Choose the Latest Date from 2 Date Fields in a Table 1

[Stangleboy]

I have been gone for a very long time, and thank you in advance for your help. I have a table with 2 date fields I want the latest date to populated in a new field. I wrote a very basic line in the query section but Access does not like my comma that starts my second statement:

LatestDate: IIf(Nz([DispoDate])>=Nz([PacketDate]),([DispoDate])),IIf(Nz([PacketDate])>=Nz([DispoDate]),([PacketDate]))

Each statement runs fine individually but placed together I get:

"The expression you entered contains invalid syntax, or you need to enclose your text data in quotes. It seems that Access is looking for another option like "AND" or "OR" or "<>" but that data comes out wrong.

Any suggestions how to fix this code or another way to write would be great, thank you.

 

[PHV]

Why not simply this ?
LatestDate: IIf(Nz([DispoDate])>=Nz([PacketDate]),[DispoDate],[PacketDate])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Stangleboy]

I guess I wanted to make things more complicated then they are, thank you PHV.

 

[lameid]

NZ takes two parameters, Help says the second is optional UNLESS used in a query.

The Variable to test and return something if it is null, and the something that will be returned. By default it returns a zero length string if there is not a second value: ""


Since the fields say date in them, I would assume they are date fields and compare them to a low date if null. Also you are only doing 1 test to see if one number is bigger so you only need 1 test; IIF. IIF takes three parameters. The test or rather the boolean it returns, what to return if it is true, what to return if it is false.

LatestDate: IIF( NZ([DispoDate],#4/1/1900#) >= NZ([PacketDate],#4/1/1900#), [DispoDate], [PacketDate])