Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new with access 2003. I set up date format in table. When I query a
date range criteria between "4/15/2007" and "4/20/2007", why "4/2/2007" show
up? How can I fix this problem?

Thanks,
Cynthia
 
Because you're not using a date. You're using a string. "4/1" is
less than "4/2" alphabetically.

You need to have a date/time field.

If you need more information then post back with information about
your table structure and the SQL (View -> SQL View) in Query designer)
of the query you are trying to use, as well as any questions you may
have.

Cheers,
Jason Lepack
 
By your criteria it's look like the date field is a text field

between "4/15/2007" and "4/20/2007"

If its a date field it should be
between #4/15/2007# and #4/20/2007#

Also, if it's a text field, you need to use the criteria on the field after
you convert it to date

Where CDate([DateField]) between #4/15/2007# and #4/20/2007#
 
It's a text field formatted to LOOK like a date not an actual date/time data
type field. Change it to a Date / Time field. Then the following criteria
will work:

Between #4/15/2007# and #4/20/2007#

Double quotes ( " ) means to treat it like text. The hash ( # ) represents
date/time.

NOTE: If you are storing hours/minutes/seconds (I.E. a time component) above
criteria won't bring back any records for the 20th except those exactly at
midnight. One way to handle is is this:

Between #4/15/2007# and #4/20/2007# + .99999
 
Basically, three steps involved for my data.
1) I setup Posting Date with "Date/Time" under Date Type in Table A and
Table B (Format is Short Date, Input mask is 99/99/0000;0;_).

2) I did a union SQL to combine Table A and Table B files. I use " " for
date if there is no matched value. Here is my Query in SQL:
SELECT [table a].posting date
UNION " "
FROM...
WHERE...;

3) Query based on date range. Here is my Query in SQL:

SELECT [Query Step 2].[Posting Date]
FROM [Query Step 2]
WHERE ((([Query Step 2].[Posting Date]) Between #4/15/2007# And #4/20/2007#));

After review everyone's input, I found out the problem might occured in Step
2.

I tried to use " and # in step 3 or change " " to "01/01/9999" in Step 2,
but got the same wrong date "4/2/2007".

Should I change field properties foramt in Step 3 Query? What should I type
in? Or do anything else?


Thanks for help!
Cynthia
 
I don't see what step 2 does, really, but Instead of

SELECT [table a].posting date
UNION " "
FROM...
WHERE...;


it should have been:

SELECT CDate( [table a].posting date )
FROM ...
WHERE ...

UNION ALL

SELECT NULL
FROM...
WHERE...;




but again, in step 3, you don't care about the extra row you added in step
2, with the UNION (ALL). So, skip step 2, or at least what starts and follow
the word UNION, use CDate( ) as illustrated, just to be sure you then have
date_time, not string, and step 3 should be fine.



Vanderghast, Access MVP


Cynthia said:
Basically, three steps involved for my data.
1) I setup Posting Date with "Date/Time" under Date Type in Table A and
Table B (Format is Short Date, Input mask is 99/99/0000;0;_).

2) I did a union SQL to combine Table A and Table B files. I use " " for
date if there is no matched value. Here is my Query in SQL:
SELECT [table a].posting date
UNION " "
FROM...
WHERE...;

3) Query based on date range. Here is my Query in SQL:

SELECT [Query Step 2].[Posting Date]
FROM [Query Step 2]
WHERE ((([Query Step 2].[Posting Date]) Between #4/15/2007# And
#4/20/2007#));

After review everyone's input, I found out the problem might occured in
Step
2.

I tried to use " and # in step 3 or change " " to "01/01/9999" in Step 2,
but got the same wrong date "4/2/2007".

Should I change field properties foramt in Step 3 Query? What should I
type
in? Or do anything else?


Thanks for help!
Cynthia
Cynthia said:
I am new with access 2003. I set up date format in table. When I query
a
date range criteria between "4/15/2007" and "4/20/2007", why "4/2/2007"
show
up? How can I fix this problem?

Thanks,
Cynthia
Click to expand...
Click to expand...
 
I am new with access 2003. I set up date format in table. When I query a
date range criteria between "4/15/2007" and "4/20/2007", why "4/2/2007" show
up? How can I fix this problem?

Thanks,
Cynthia
Click to expand...

Sounds like you're treating dates as Strings. They're *NOT* strings!

Store your date in a Date/Time field and use # instead of " as the delimiter.
The *text string* "4/2/2007" is in fact between the two text strings you cite
- as is "4/2/999999999", simply because it's doing an alphnumeric text
comparison (the character / is less than the character 0).


John W. Vinson [MVP]
 
2) I did a union SQL to combine Table A and Table B files. I use " " for
date if there is no matched value.
Click to expand...

That's the source of the problem, then.

By using " " in the first SELECT of the UNION you are overriding the date/time
datatype of this field and making it into a Text field.

If possible use a SELECT which doesn't return any null values as the first in
the series; and use NULL instead of an empty string.

John W. Vinson [MVP]
 
Thanks a lots for everyone's input! I revised my program to avoid union date
and " " and it's working now.

Cynthia
 
Back
Top