Automatically repeat the last record under certain conditions in a table/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(Calendar 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!

[FONT=&quot]Ben[/FONT]

 

[Uncle Gizmo]

are the dates always ascending with no gaps?

 

[IexGIfate]

Yes, this will be good to know first in case there is no gaps.

 

[vbaInet]

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

That's a lot of criteria you're working with there.

First of all, start with the one I've highlighted in blue.

1. Create a query that does a LEFT join from your original query to the Holidays Table
2. Put criteria under the Calendar Date field for dates > Date()
3. Put Is Null criteria under the Holiday date field.

Once you get that working you can go to the next stage.

 

[IexGIfate]

Thanks a lot for your answer,

Ok, I've done it and now I see only the interesting future dates.

How to make the Value corresponding to these dates = to the last not = 0 record?

Ben

 

[vbaInet]

Ok, I've done it and now I see only the interesting future dates.

You're not done yet Ben. Now you need to incorporate the Weekday() part into the criteria. So it will be something like:

> Date() And Weekday([Calendar date]) <> 1 AND Weekday([Calendar date]) <> 7

 

[IexGIfate]

I put Between 2 and 6 and everything's ok.

I see the good dates now thanks

 

[vbaInet]

Next step:

1. Create another query pull your original query (or table) from your first post and do a LEFT JOIN to the new query
2. Use the IIF() to determine which of the date values from the "good dates" query Is Null:

IIF(IsNull([COLOR="Blue"]qryGoodDates[/COLOR].[[COLOR="blue"]DateField[/COLOR]])=True, [[COLOR="blue"]MyValue[/COLOR]], "[COLOR="blue"]We'll do something here later[/COLOR]")

 

[IexGIfate]

"We'll do something later" on every line.

-I guess it doesn't know how to evaluate what's qryGoodDates.[DateField].
-Why don't I add a field [MyValue] on the qryGoodDates? So it will put only the values corresponding to the dates?

 

[vbaInet]

Did you do a LEFT JOIN?

 

[IexGIfate]

This was my error, well done! Yes I have it now thanks

 

[vbaInet]

Now I'm sure you know we're going to create another query

1. Create a query based on your original table (or query) with just the date and myvalue fields
2. Put criteria under the myvalue field to > 0
3. You can either:
i. Sort by the date field in ASC order and use Last on the myvalue field to get the last value
ii Sort by the date field in any order and use the Min (for DESC) or Max (for ASC) to get the last value
iii. Sort the date field in DESC order and use the TOP 1 predicate to get the last non zero value.

 

[IexGIfate]

I'm getting used to it

3)
i) "Cannot have aggregate function in WHERE clause (Last([MyValue])>0)
ii) Does not do anything (I used Totals => Min on the dates)
iii) Invalid syntax. Should I write it in SQL?

 

[vbaInet]

i. Last is an aggregate function just like Min, Max, Count etc. You need to right click and hit Totals then select it from there
ii. Did you include only the date and myvalue field?
iii. Yes that's right, SELECT TOP 1 DateField, MyValue... etc

 

[IexGIfate]

i. Ok, I tried but I see all the dates with all the values.
ii. Yes, 2 fields only. Dates ascending, MyValue criteria > 0 total min (or max, I tried both)
iii. Syntax error. My SQL code:

"SELECT [Calendar_All Dates].[Calendar date] ,[Calendar_All Dates].[MyValue] (SELECT TOP 1 [Calendar_All Dates].[MyValue])
FROM [Calendar_All Dates]
WHERE ((([Calendar_All Dates].[MyValue])>0))
ORDER BY [Calendar_All Dates].[Calendar date];"

I tried without parenthesis, with ","

 

[vbaInet]

Let's keep it simple:

SELECT TOP 1 [MyValue]
FROM [Calendar_All Dates]
WHERE [MyValue] > 0
ORDER BY [Calendar date] DESC;

 

[IexGIfate]

Ok I got the single value

 

[vbaInet]

Do you realise we're going to writing another SQL statement?

Going back to post #8, you now need to incorporate the new query in here:

IIF(IsNull(qryGoodDates.[DateField])=True, [MyValue], [COLOR="Red"]([/COLOR]SELECT [COLOR="blue"]TheValue [/COLOR]FROM [COLOR="Blue"]NewQuery[/COLOR][COLOR="red"])[/COLOR])

I hope you notice the parentheses in red enclosing the SQL statement - very important.

That's it, you're done!

 

[IexGIfate]

Yes Yes and Yes!
I needed to add a If [Calendar_All Date].[Calendar Date]>Date()
But yes it's perfect! Thanks a lot

 

[vbaInet]

You're welcome!

I'm glad that you were able to follow instructions with ease. Some people struggle!