Parameter Query and Date calculations....

[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

[Allen Browne]

Access is not good at determining the data types of calculated query fields,
parameters, and unbound controls. You will need to help it understand the
data type you intend, and it will then return the correct results.

Wrap the calculated date field in CVDate()
Declare any parameter in your query.
If the query refers to an unbound control on a form, set the Format property
of the control to Short Date or similar.

More details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

The way Access stored the date/time data is not the problem. If you use
non-US dates, you might like to read this also:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Julie Wardlow" <jl*******@supanet.com> wrote in message
news:r6****************@newsfe7-gui.ntli.net...

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?

[Julie Wardlow]

Thanks,

It appears my problem was to do with the specification of formats although
the CVDATE() didn't make any difference to the records that were returned.
However, I have sorted the it by taking out the IIF part of the formula and
using criteria to determine when to run the formula and the then using an
append query so the calculated date is added to a field in a table with the
specifies format as date/time. This seems to have got round the problem even
if it hasn't solved it directly!

Thanks for your help, I shall certainly be more careful with formats in the
future,

Julie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43*********************@per-qv1-newsreader-01.iinet.net.au...

Access is not good at determining the data types of calculated query
fields, parameters, and unbound controls. You will need to help it
understand the data type you intend, and it will then return the correct
results.

Wrap the calculated date field in CVDate()
Declare any parameter in your query.
If the query refers to an unbound control on a form, set the Format
property of the control to Short Date or similar.

More details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

The way Access stored the date/time data is not the problem. If you use
non-US dates, you might like to read this also:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Julie Wardlow" <jl*******@supanet.com> wrote in message
news:r6****************@newsfe7-gui.ntli.net...

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?