'Strange' results from expression (1 Viewer)

[Scaniafan]

Hello all,

I've got below expression in a query...

Gross Document Performance: IIf([QRY_Gross_Performance_Document_2]![Document Upload Date] Is Null And [QRY_Gross_Performance_Document_2]![Target Date]>=Date();"Pending";IIf([QRY_Gross_Performance_Document_2]![Document Upload Date] Is Null And [QRY_Gross_Performance_Document_2]![Target Date]<Date();"Pending - Late";IIf([QRY_Gross_Performance_Document_2]![Document Upload Date]<=[QRY_Gross_Performance_Document_2]![Target Date];"On Time";IIf([QRY_Gross_Performance_Document_2]![Document Upload Date]>[QRY_Gross_Performance_Document_2]![Target Date];"Late"))))

The results for the first part (Pending & Pending - Late) work fine, the rest results in a #Error.

All fields are formatted as Short Date in QRY_Gross_Performance_2. If I put Cdate() around the fields, the expression works fine.

In both instances however I then get an data type mismatch if I use <>"Pending" as criteria. This is strange to me as the expression result is text...

Two questions...

- What goes wrong in the second part of the expression?
- Why can't I use text criteria on the results of the expression?

 

[Minty]

If you format a date it changes it to a string, hence when you use CDate it starts to work again.

 

[Minty]

Sorry the second part of your question - use this query as a basis to build another query then apply your criteria from their. The underlying data is a date, hence your type mismatch.

 

[Scaniafan]

Ok, that part I get, by using CDate I'm making a date value from the field. But for some reason the first part works without using CDate, and the second part doesn't. Although nothing big changes between the two parts.

However, the result of my expression with using CDate is text. Either "Pending", "Pending - Late", "On Time" or "Late".

The only other value is a #Error if one of the dates

[QRY_Gross_Performance_Document_2]![Document Upload Date]
[QRY_Gross_Performance_Document_2]![Target Date]

is Null.

For some reason I get a type mismatch if I want to use a criteria like <>"Pending" or Not "Pending" on this field


------

Edit

------

Ok, I've got some more intel on this... It turns out that the target date causes this behaviour. This date is calculated in the 2nd query with below expression, and populates with "No actual end date" if there is no date in the end date field. If I change this to Null the expression works as I want it to.:banghead:

 

[Minty]

The calculated field is not calculated before your criteria are applied : Put your date field in and the in it's criteria put in your original criteria;
[QRY_Gross_Performance_Document_2]![Document Upload Date] Is Null And [QRY_Gross_Performance_Document_2]![Target Date]>=Date()