Simple Select Query using dates

S

shadow

I have one table where the underlying date is a long date, and it is
displayed as a short date. I have a second table where the underlying date
is a short date, and is displayed as a short date. I've linked the two
tables on date in my query but I get no matches.

Table 1 underlying date Table 2 underlying date
1/15/2004 11:16:00 AM 1/15/2004

Table 1 displayed date Table 2 displayed date
1/15/2004 1/15/2004

How do I modify my query so that just the short date is considered when I
query for matching dates?
 
R

Rick Brandt

shadow said:
I have one table where the underlying date is a long date, and it is
displayed as a short date. I have a second table where the
underlying date is a short date, and is displayed as a short date.
I've linked the two tables on date in my query but I get no matches.

Table 1 underlying date Table 2 underlying date
1/15/2004 11:16:00 AM 1/15/2004

Table 1 displayed date Table 2 displayed date
1/15/2004 1/15/2004

How do I modify my query so that just the short date is considered
when I query for matching dates?
Click to expand...

What you are talking about "short date" and "long date" are merely display
options. They do not affect what is stored. An Access Date field always
includes time. When you don't see one then it is midnight. So you are
really attempting to join...

1/15/2004 11:16:00 AM to 1/15/2004 00:00:00 AM

A solution (though not a real efficient one) is to use...

Select Table1.*, Table2.*
FROM Table1 JOIN Table2
ON DateValue(Table1.DateField) = Table2.DateField

You will not be able to create such a join in the graphical designer. Just
make the standard join you have now and then switch to SQL view to add the
DateValue() function.
 
Top