Automatically repeat the last record under certain conditions in a table/query.

[IexGIfate]

It was pretty clear.

One question, I'll be working with a lot of data, which one of these 2 solutions is faster?

IIf([Calendar_All Dates].[Calendar date]<Date(),
[MyValue],
IIf(IsNull([UK_Working days].[Calendar date])=True,0,(SELECT [MyValue] FROM [Query222])))

or

IIf([Calendar_All Dates].[Calendar date]<Date() or IsNull([UK_Working days].[Calendar date])=True,
[MyValue],
(SELECT [MyValue] FROM [Query222]))

Because MyValue = 0 when IsNull([UK_Working days].[Calendar date])=True

So [2 If ()] faster than [1 If () or ()] ?

 

[vbaInet]

They result in two different outcomes.

The first one will return [MyValue] if 1 condition is met whilst the second will return the same if 2 conditions are met.

 

[IexGIfate]

Well, I think that they return the same thing.

They both return [MyValue] if [Calendar_All Dates].[Calendar date]<Date() OR IsNull([UK_Working days].[Calendar date])=True

And return (SELECT [MyValue] FROM [Query222])) otherwise.

Is there one faster?

 

[vbaInet]

Look carefully, you will find that they don't do the same thing. They may return the same result but they simply don't do the same thing.

If they're returning the same result then it means one or more of the criteria has already been catered for in the steps I gave you earlier. Or your data just doesn't require one of them.

 

[IexGIfate]

Yes you are right, they don't do the same thing (I just said that they returned the same result).

But as MyValue = 0 when IsNull([UK_Working days].[Calendar date])=True

I think that:
The first solution fills what's before Date() then fills the 0 when IsNull.. then fills the values
The second solution fills what's before Date() and what's after with some 0 (=Myvalue when CalDate>Date()) when IsNull, then fills the values not = 0 after Date()

Am I right?

 

[vbaInet]

This is how I would explain it.

IIf([Calendar_All Dates].[Calendar date]<Date(),
[MyValue],
IIf(IsNull([UK_Working days].[Calendar date])=True,0,(SELECT [MyValue] FROM [Query222])))

MyValue will show if the Calendar Date is before today. Otherwise, if there's no calendar date it will show 0, and as a last resort, if everything else fails it will show the last value.

IIf([Calendar_All Dates].[Calendar date]<Date() or IsNull([UK_Working days].[Calendar date])=True,
[MyValue],
(SELECT [MyValue] FROM [Query222]))

MyValue will ONLY show if there is no calendar date or the calendar date is before today. Otherwise, if none of those conditions are met, show the highest value.

IIF() is like a real life scenario. If I do this what will happen? On the other hand if I don't do it, what will happen? I do = True, I don't do = False.

So you need to choose wisely.