Repeat the last record under certain conditions in a table or a query

[IexGIfate]

Hello,

I’d like to repeat the last not = 0 record under certain conditions, in a table or in a query.

What I have:
Calendar_All Dates
Calendar date MyValue
7/6/2014 0.00
7/7/2014 108.94
7/8/2014 107.71
7/9/2014 107.07
7/10/2014 105.89
7/11/2014 105.30
7/12/2014 0.00
7/13/2014 0.00
7/14/2014 104.69
7/15/2014 0.00
7/16/2014 0.00
7/17/2014 0.00
7/18/2014 0.00
7/19/2014 0.00
7/20/2014 0.00
7/21/2014 0.00
7/22/2014 0.00
7/23/2014 0.00

What I want:
Calendar_All Dates
Calendar date MyValue
7/6/2014 0.00
7/7/2014 108.94
7/8/2014 107.71
7/9/2014 107.07
7/10/2014 105.89
7/11/2014 105.30
7/12/2014 0.00
7/13/2014 0.00
7/14/2014 104.69
7/15/2014 104.69
7/16/2014 104.69
7/17/2014 104.69
7/18/2014 104.69
7/19/2014 0.00
7/20/2014 0.00
7/21/2014 104.69
7/22/2014 104.69
7/23/2014 104.69

Basically, if Calendar date > Date(), if Calendar Date not Saturday or Sunday, weekday(Calenda r date<>1 and <>7), AND Calendar Date not in (Holidays table)
Then repeat the last not = 0 value of MyValue


The idea is clear but I can’t find the right way to write it.. I thought of changing the default value but the value is already 0, while default is null + I need to set the default value under certain conditions.
Thanks a lot for your help!

Ben

[twinnyfo]

Ben,

I htink you have much of the basics to do what you want. How fluent are you in VBA and using recordsets? This would allow you to examine multiple aspects of each record.

There may be a way to do this with just a SQL SELECT Statement, but it would probably make my head hurt more than I care to guess.

[IexGIfate]

Hi,

I'm starting to understand how to use them. I think I would be better to do this using VBA as well.
What are you thinking about?

Ben

[twinnyfo]

Without writing your code for you, I would begin by opening a recordset based upon your table, sorted by date. Have a variable that slooks at the value of MyValue for the first record. Then cycle through records 2 - x. If the MyValue for that record is not 0, then you update the variable to reflect the new value. Then, if the date of the current record meets all your criteria, and is also 0, then update that record to reflect the value in your variable.

Relatively straightforward , and we will be glad to help you through any hitches you come across. We'd like to see what you come up with first.

[IexGIfate]

I'd do something like:

Public Sub GetInterestingValue()

Dim x As Double
Dim recordSetDate As DAO.Recordset
Set recordSetDate = CurrentDb.OpenRecordset("Calendar_All Dates")

'I need to like "scan" all the records, which I don't know how to do, for recordSetDate in [Calendar_All Dates], while maybe?

If recordSetDate("MyValue").Value <> 0 

'{This will be for later: AND Weekday(recordSetDate("Calendar Date").Value) Between 2 and 6 AND recordSetDate("Calendar Date") is in [UK_Holidays]}

then 
x=recordSetDate("MyValue").Value

'Go scan Next one

End Sub


So it should return the last value with the goo syntax right?

After I see something like 

Public Sub FillTheTable()

Dim recordSetDate As DAO.Recordset
Set recordSetDate = CurrentDb.OpenRecordset("Calendar_All Dates")

'I need again to like "scan" all the records from the last corresponding to the x I got earlier to the last of the table, which I don't know how to do, for recordSetDate in [Calendar_All Dates], while maybe?

For recordSetDate("Calendar Date") > {Calendar Date corresponding to the x calculated earlier} 

If Weekday(recordSetDate("Calendar Date").Value) Between 2 and 6 AND recordSetDate("Calendar Date") is not in [UK_Holidays]

then 
recordSetDate("MyValue").Value=x

'Go scan Next one

End Sub

[IexGIfate]

x is not global I guess, I should call GetInterestingV alue() and store it as x at the beginning of the Second sub

[twinnyfo]

Good first trial for a rookie(?). Let's use what you have so far and work on it:

P.S. Please use Code Tags when posting code.

Public Sub GetInterestingValue()
    Dim dblCurrentValue As Double
    Dim strSQL As String
    Dim recordSetDate As DAO.Recordset

    'We want to make sure we sort by the date:
    strSQL = "SELECT * FROM [Calendar_All Dates] " & _
        "ORDER BY [Calendar Date];"
    'You may notice the challenge of having spaces in your
    'field names here--try to avoid in the future

    Set recordSetDate = CurrentDb.OpenRecordset(strSQL)

    'This should return all records in the Table
    'But if something goes wrong, we want to make sure
    'there are records before we start:
    If Not recordSetDate.RecordCount = 0 Then
        'First find out what the MyValue is
        recordSetDate.MoveFirst
        dblCurrentValue = recordSetDate!MyValue
        recordSetDate.MoveNext
        Do While Not recordSetDate.EOF
            'Perform your data validations here
            'for weekdays and holidays


            'Update your Current Value here (when needed):
            dblCurrentValue = recordSetDate!MyValue

            'If you need to assign the value of the MyValue
            'This will probably be nested in an If...Then
            With recordSetDate
                .Edit
                !MyValue = dblCurrentValue
                .Update
            End With
        Loop
    End If
End Sub

You were not far off, but I htink it's a lack of experience. We will be glad to help you through the rest of the process as you trudge along--we've all been there at some point in our lives.

[IexGIfate]

This is just beautiful, thanks a lot for your help.
I will try to write in properly tomorrow and I will tell you about.

Many thanks again

[Rabbit]

There is a SQL only solution but it's convoluted to say the least.

Please note this solution was created and tested in SQL Server, some minor syntax changes would be needed to make it work in Access:

SELECT
	y.dt,
	z.amt

FROM 
	(
		SELECT 
			t1.dt,
			MAX(COALESCE(t2.dt, t1.dt)) AS mdt
			
		FROM 
			#t AS t1
			
			LEFT JOIN #t AS t2
			ON	t1.dt > t2.dt AND
				DATEPART(WEEKDAY, t1.dt) BETWEEN 2 AND 6 AND
				DATEPART(WEEKDAY, t2.dt) BETWEEN 2 AND 6 AND
				t1.amt = 0 AND
				t2.amt <> 0

		GROUP BY
			t1.dt
	) x
	
	INNER JOIN #t AS y
	ON	x.dt = y.dt
	
	INNER JOIN #t AS z
	ON	x.mdt = z.dt
	
ORDER BY
	y.dt

Basically you have to reference the table 4 times to make it work.