Tags: access, calculating, calculations, database, date, dateadd, function, future, helpi, iif, involves, managed, microsoft, mysql, oracle, parameter, query, sql, statement, thecalculation

Parameter Query and Date calculations....

On Database » Microsoft Access

6,522 words with 2 Comments; publish: Mon, 31 Dec 2007 11:52:00 GMT; (250140.01, « »)

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

All Comments

Leave a comment...

  • 2 Comments
    • 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" <jlwardlow.ms-access.todaysummary.com.supanet.com> wrote in message

      news:r65Se.5200$Ys5.637.ms-access.todaysummary.com.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?

      #1; Mon, 31 Dec 2007 11:54:00 GMT
    • 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" <AllenBrowne.ms-access.todaysummary.com.SeeSig.Invalid> wrote in message

      news:4319074c$0$502$5a62ac22.ms-access.todaysummary.com.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" <jlwardlow.ms-access.todaysummary.com.supanet.com> wrote in message

      > news:r65Se.5200$Ys5.637.ms-access.todaysummary.com.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?

      #2; Mon, 31 Dec 2007 11:55:00 GMT