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

 

[Mike Painter]

Thanks, but the magic worked again. I usually work on a problem a long time
before asking for help.
99% of the time the solution come to me right after posting.

I recompiled and everything works again.
I don't think the error was in what I posted but just a "referred pain"
Maybe the brownies want extra milk.

Thanks again.


I download a report two to six times a day and the [last call time] is a
string containing the date and time in an odd format which I have no control
over and Access does not like.
Under a rare situation it may not even be a date but something else. That's
why the IIF statement is needed.

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

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