Retrieve data after a certain date

[Ifigenia]

I have a silly litle problem. I have a table with transactions and it has a
field called "date" (Date/Time). I made a query to retrieve certain records
and one of the contitions is to get records after a certain date.
I have tried several solutions but the problem seems to be that i can't get
the computer to recognise my constant date as Date/Time. If i use quotation
marks it thinks it's text and i get a mismach, and if i use nothing the #
symbol appears and I don't know what this does and i get nothing. What
should i put in the "criteria" field under "Date" in order to get the
transactions that where made after 18/5/2009 (constant).

ifigenia

 

[George]

Geia sou Ifigeneia,

Assuming that the date field is of Dat/Time datatype use the criteria

18/5/2009 in your query - this should be change automatically to
#15/5/2009#. Of course is better to rename the field name from date to

TransactionDate or something relevant.

If you want to use the current date use the criteria >date()

Hope this helps,

GeorgeCY

Ο χÏήστης "Ifigenia" έγγÏαψε:

 

[MGFoster]

I have a silly litle problem. I have a table with transactions and it has a
field called "date" (Date/Time). I made a query to retrieve certain records
and one of the contitions is to get records after a certain date.
I have tried several solutions but the problem seems to be that i can't get
the computer to recognise my constant date as Date/Time. If i use quotation
marks it thinks it's text and i get a mismach, and if i use nothing the #
symbol appears and I don't know what this does and i get nothing. What
should i put in the "criteria" field under "Date" in order to get the
transactions that where made after 18/5/2009 (constant).

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use this:

Criteria: > #5/18/2009#

The ">" symbol means greater-than, which translates to all dates after
May 18, 2009.

When using the # delimiter the date format has to be
USA date format: month/day/year.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBShE0m4echKqOuFEgEQLJHwCg9IpFjBwW12gKRuHWpiS4c1NA0IMAn0Pf
9nK0IgUHKaYSCYqFrLwPzAbx
=3UBe
-----END PGP SIGNATURE-----

 

[Douglas J. Steele]

Use this:

Criteria: > #5/18/2009#

The ">" symbol means greater-than, which translates to all dates after
May 18, 2009.

When using the # delimiter the date format has to be
USA date format: month/day/year.

<picky>

It's not mandatory to use mm/dd/yyyy just because you're using # delimiters.
yyyy-mm-dd works just as well, as will any unambiguous format such as dd mmm
yyyy.

The issue is that Jet will always assume dates in nn/nn/nnnn format are
mm/dd/yyyy (regardless of the Short Date format set through Regional
Settings) unless the first two digits are greater than 12. In other words,
12/01/2009 will ALWAYS be interpretted as 01 Dec, 2009, whereas 13/01/2009
will be correctly interpretted as 13 Jan, 2009.

</picky>

 

[John Spencer]

Or input the date as yyyy-mm-dd format

#2009-05-18#

This format is always correctly interpreted. The # signs are needed to
tell Access that this is a date and not a math formula ( for instance,
18 divided by 5 divided by 2009). The result of the math is a very small
number (near zero) and that would give you every record in your database
where the date was after December 31, 1899. Why? Because dates are
stored as the number of days from that zero date.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Ifigenia]

thanks! i did not think of that ... still i can't change all my dates to mach
the american format ... people would get comfused. I will probably break the
date up. thanks anyway

 

[Douglas J. Steele]

No change is required to your data. A Date/Time value is actually stored as
an eight-byte floating point number where the integer portion represents the
date as the number of days relative to 30 Dec, 1899 and the decimal portion
represents the time as a fraction of a day.

See Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I had in my September 2003 Access
Answers column for Pinnacle Publication's "Smart Access" newsletter. (The
column and accompanying database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)