Date Function Error

[Guest]

I am using Micrsoft Access 2007 and i get the error when i generate the report.
A pop up message saying "Function is not available in expressions in query
expression 'format(nz(min(TLO_Date),"7/16/2007"),"dd/mm/yyyy"' is displayed.
Proceeding further by clicking OK multiple times, report will be displayed.
But the dates are displayed wrongly in this report.

Can somebody advise on that? Many Thanks.

 

[Allen Browne]

Assuming TLO_Date is a Date/Time field (not a Text field), use # as the
delimiter around the literal date value:
Format(Nz(Min(TLO_Date), #7/16/2007#), "dd/mm/yyyy")

The results of Nz() and of Format() will be Text. If you apply criteria or
sorting, you will then get the wrong results. You would probably be better
to retain the column as a date so it sorts and filters correctly, and then
apply the formatting to the text box on the report rather than the field in
the query.

The expression would then be:
CDate(Nz(Min(TLO_Date), #7/16/2007#))
Explanation in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Oh, it is also possible that you have a problem with library references:
http://allenbrowne.com/ser-38.html