Comparing dates with Date() returns different results

[Scaniafan]

Good afternoon all,

I've got a query that returns "POD Target Date". This date is based on "Actual End Date" + a number of days. The result is formatted as short date.

Based on "POD Date", stored as short date format, I want to print a text.

The whole thing works, except for one issue that I cannot seem to solve.

If the "POD Target Date" is filled but the "POD Date" is blank, I've got two options:

- POD Pending
- POD Pending - Late

What I'm doing in an expression is:

IIf([QRY_Gross_Performance_2]![POD Target Date]<Date() And [QRY_Gross_Performance_2]![POD Date] Is Null;"POD Pending - Late";

IIf([QRY_Gross_Performance_2]![POD Target Date]=>Date() And [QRY_Gross_Performance_2]![POD Date] Is Null;"POD Pending"

))

Now I've got two examples:

POD Target Date: 15-1-2015
POD Date: empty
Result: POD Pending - Late

POD Target Date: 8-1-2015
POD Date: empty
Result: POD Pending

Though logically, both should return POD Pending - Late as today is 19-11-2015.

Can someone help me out on this? Thanks.

 

[gemma-the-husky]

POD Target Date: 15-1-2015
POD Target Date: 8-1-2015

worth checking whether it is a locale issue.

access (ie your expression) MAY be treating the second date as august 1st, not 8th Jan, (not sure if that would produce the difference you experience)

the first date is unambiguously 15th Jan.

 

[Scaniafan]

Just checked, it sees the day, month and year correctly as January for both (/ all) entries.

Even if Access would have swapped day / month, resulting in August 1st, 2015 instead of January 8th, 2015, the date would still be before today, November 19th, 2015, so the expression would need to result in "POD Pending - Late" anyways.

I just checked if the second part of my expression

IIf([QRY_Gross_Performance_2]![POD Target Date]=>Date() And [QRY_Gross_Performance_2]![POD Date] Is Null;"POD Pending"

))

would also give strange results, but this gives all "POD Pending" as it should.

 

[arnelgp]

is it true when you use the IsNull function:

IIf([QRY_Gross_Performance_2]![POD Target Date]<Date() And IsNull([QRY_Gross_Performance_2]![POD Date]);"POD Pending - Late";

IIf([QRY_Gross_Performance_2]![POD Target Date]=>Date() And IsNull([QRY_Gross_Performance_2]![POD Date]);"POD Pending"

 

[Scaniafan]

Just changed it to your suggestion, it still provides the same results, 8th January 2015 "POD Pending", 15th January 2015 "POD Pending - Late"...

 

[gemma-the-husky]

another thought, then.

you only have a "true" evaluation in your iif statement, and not a false one

are you seeing a "default value" in your result, by any chance?

 

[Scaniafan]

I thought to make it more easy, I would only post the part of the expression resulting in the issue.

Below is my complete expression:

Test Check: IIf([QRY_Gross_Performance_2]![POD Target Date]="";"";IIf([QRY_Gross_Performance_2]![POD Target Date]<Date() And IsNull([QRY_Gross_Performance_2]![POD Date]);"POD Pending - Late";IIf([QRY_Gross_Performance_2]![POD Target Date]>=Date() And IsNull([QRY_Gross_Performance_2]![POD Date]);"POD Pending";IIf([QRY_Gross_Performance_2]![POD Date]<[QRY_Gross_Performance_2]![POD Target Date];"POD On Time";IIf([QRY_Gross_Performance_2]![POD Date]>[QRY_Gross_Performance_2]![POD Target Date];"POD Late by Date";IIf([QRY_Gross_Performance_2]![POD Date]=[QRY_Gross_Performance_2]![POD Target Date] And [QRY_Gross_Performance_2]![POD Time]<=[QRY_Gross_Performance_2]![POD Target Time];"POD On Time";IIf([QRY_Gross_Performance_2]![POD Date]=[QRY_Gross_Performance_2]![POD Target Date] And [QRY_Gross_Performance_2]![POD Time]>[QRY_Gross_Performance_2]![POD Target Time];"POD Late by Time";"Check")))))))

 

[arnelgp]

is your field a Text, of course this will not work.

 

[Scaniafan]

I am using "" in earlier expressions to translate a Null, as I understuud from Mr. Google that Null is something to avoid in Access.

What I am doing in earlier expressions is Iif(IsNull(Actual End Date;"";......)

Can I keep it still as Iif(IsNull(Actual End Date;Null;...) as a safe work method?