Loop through a datasheet and change cell value based on two other cells

[scubafrog79]

I have a form that is shown as a datasheet view when it's loaded. During the loading of the form, I would like for it to do a "calculation" that if the "Refill Date" is greater than the "PA Date", it changes the "PA Required" to TRUE. But I need it to check all the rows as the form loads. I should add that I don't want to save the change to PA Required in the table that stores the data, just want it to show me when the form loads. How can I do this with VBA code?

Thank you in advance

 

[Andrzejek]

How about simple:
[tt]
Select "Refill Date",[blue]
IIF("Refill Date" > "PA Date", TRUE, FALSE) As "PA Required",[/blue]
"PA Date"
From MyTable
[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Hi Andy,
This is MS Access so they syntax would be:

Select [Refill Date],
IIF({Refill Date} > [PA Date], TRUE, FALSE) As [PA Required],
[PA Date]
From MyTable

At least you didn't use CASE WHEN in place of IIf.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[sxschech]

Shouldn't [pre]{Refill Date}[/pre] be [pre][Refill Date][/pre] ?

 

[dhookom]

Yes the {} should be [].

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016