Compile error in query expression

[Mike Painter]

This worked the last time I opened the database.

reportDate: IIf(Len([last call time])<7,Date(),DateValue(Left([last call
time],InStr([last call time]," "))))
It gives "compile error in query expression" now

I have run all parts of it in an immediate window and they work.
I can find no missing references.
Visual basic for applications
Microsoft access 11.0 object library
Microsoft DAO 3.6...
Ole Automation
Microsoft visual basic for applications extensibility 5.3
Microsoft calendar control 11.0
Microsoft office 11.0 object library
Microsoft Word 11.0 object library
are all checked.

I have not rebooted at this time.

 

[Allen Browne]

If I understand you correctly:
- [Last Call Time] is a Date/Time field.
- If it is blank, you want to use today's date.
- Otherwise you want to strip the time component, and just show the date.

Since this calculated field will not be editable, you may be able to solve
the problem with a text box bound to the [last call time] field, and set its
Format property to "Short Date".

Alternatively, try something like this:
IIf([last call time] Is Null, Date(), DateValue([last call time])

For anyone who wants to know why I didn't suggest NZ(), see:
http://allenbrowne.com/QueryPerfIssue.html#Nz