Problem using Now() with DateAdd Function?

[mincefish]

This question concerns using Access 97, with linked tables from SQL Server 7.

The key fields are as follows:
AssignDate = Date\Time
ActionDateDue = Date\Time

I have a query (unfortunately not written by me...) which doesn't seem to work, in its normal form below:

SELECT Call.Call_Num, Call.Priority, Call.OpenDate, Call.AssignTo, Call.ActionEmailAddress, Call.AssignDate, Call.ActionDateDue, DateAdd("n",(DateDiff("n",[AssignDate],[ActionDateDue])*1.5),[AssignDate]) AS 150Percent, Call.ActionFlag3
FROM Call
WHERE (((DateAdd(&quot;n&quot;,(DateDiff(&quot;n&quot;,[AssignDate],[ActionDateDue])*1.5),[AssignDate]))<Now()) AND ((Call.ActionFlag3)=False) AND ((Call.IsClosed)=False));

When the query runs, I get a message saying

Data Type Mismatch in Criteria Expression

, and every row has #Name? in every field.

I started messing around with the criteria, to try to isolate the reason for the error, and I found that by removing the

Now()

criteria (leaving

WHERE ((Call.ActionFlag3)=False) AND ((Call.IsClosed)=False);

, I could make the query run properly.

This is really screwing my head, because I don't understand why

Now()

is making such a difference!

Any help would be gratefully appreciated!

 

[hermanlaksko]

The data/time entered in that database could have a different format that the one, standard om your PC
Standard in the US would be: 12312002
in Denmark it would be: 31122002 and I think that
in Sweden it would be: 20023112.
So if you format the - Now() - the Now() should work for you.

 

[mincefish]

Ok...not wishing to sound stoopid, but how would I format the Now(), if the records in the database look like this:

20/07/2002 21:36:11

I think my PC is set up to UK format, and the server that the records are being pulled from are sitting on a server also here in the UK.

Thanks for your help!

 

[hermanlaksko]

format(now(),"dd/mm/yyyy&quot