Date Format Issues

[DB]

I have a query that stores dates inputted into the long date format
automatically (time stamp). I have created a table with short date
format matching particular days with "A", "B", or "C". Obviously they
will not link together because the query dates have the time included
with the date (Which I do not have the ability to change). Is there
any way that I could get access to just read the date part of the long
date format so it can link with the table that I have created?

 

[Jeff Boyce]

Unless your tables are storing the dates/times as text, rather than
date/time data, the "format" you've used has nothing to do with the values
actually stored.

If you need the date-only portion of a date/time field, take a look at the
DateValue() function in Access HELP. Similarly, try the TimeValue()
function for time-only...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rick Brandt]

I have a query that stores dates inputted into the long date format
automatically (time stamp). I have created a table with short date
format matching particular days with "A", "B", or "C". Obviously they
will not link together because the query dates have the time included
with the date (Which I do not have the ability to change). Is there
any way that I could get access to just read the date part of the long
date format so it can link with the table that I have created?

First, forget about this being a format issue (it's not). Formatting is a
display only setting and has nothing to do with what is stored.

What you actually have are two tables with date fields. In one of the
tables the times vary and in the other table the times are all midnight.
Access normally just suppresses the display of midnight times.

It won't be very efficient but you can join them using a non-standard join
like...

SELECT foo, bar, etc..
FROM Table1 JOIN Table2
ON DateValue(Table1.DateField) = Table2.DateField

In the above example the date field in Table1 is the one that has varying
time values. The DateValue() function strips that off so you get the same
date at midnight.

You will not be able to build this query using the graphical QBE grid. Use
that to create a standard join on the fields and then swithc to SQL view to
add the DateValue() expression to the join.

 

[DB]

Thank you both for the quick response! I will play with the DateValue
and see what I can get!

Thanks Again!

 

[DB]

The DateValue worked like a charm!!

Thank you again to both of you!!