Parameter Query and Date calculations....

J

Julie Wardlow

Help!

I am calculating a future date using the DateAdd function in a query (the
calculation also involves an IIf statement), and have managed to get this
formula to produce the required result.

I then want to search through the records and select those with dates (as
caluclated above) within a user defined range, and so I am using a parameter
query. However, this query returns dates outside of the range and appears to
have particular problems in differentiating between years (2005 cf 2006).
But when the dates are not calculated but worked out and entered manually
the correct records are selected using the same criteria.

Since both my criteria and the equation work correctly independantly I am
assuming the problem is somehow related to the way Access stores dates. Does
anyone know anything about this or how I could resolve the problem?

Any suggestions would be greatly appreciated!

Julie
 
J

John Spencer (MVP)

It would help if you posted your query. At a minimum, I would suggest you post
the calculation (including the IIF).

It is possible that your calculated column is not returning a datetime but a
string that looks like a datetime. For instance,

IIF(FieldA=1,"",Date()) returns a string.

Access can only return one type of data and since it was told to return an empty
string or a date, Access returns the more universal of the two - a string. You
can use
IIF(FieldA=1,Null,Date())
In this case, you will get back a datetime value or a null value since they are
"compatible". Not a good technical explanation, but perhaps you can follow
what I am saying.
 
J

Julie Wardlow

Thank you, I did follow that and I have now managed to put the IIF as a
criteria in the query so that the date is only calculated if another field
has set values. The date is now calculated using the following:
Next Visit: DateAdd("m",[Servicing Visit Regularity],[Booked For])

So if I have understood correctly that should take out the issue with
formats. However, the wrong dates were still being returned.

I have managed to go round the problem now though by using an update query
so the calculated dates are added to a table with the format of the field
set to date/time and this seems to have done the trick.

Thanks for the advice, I wouldn't have thought of the formats without it!

Julie
 
Top