Join Dates from 2 tables with different formats in a query

[Qaspec]

Table A was created with Dates and Time in each record and even when I change
the table format to Short Date to match table B (which has date but no time
data) the query will still not reurn any values because the date values are
not being considred even. How can I get the Date\Time in table a to be Date
only?

 

[vanderghast]

You apply DateValue to the dateTime field:

....
FROM table1 INNER JOIN table2
ON table1.DateOnlyField = DateValue(table2.DateAndTimeValue)
.....



Applying a format is only a cosmetic, a makeup. The real thing is left
unchanged by a format unless you capture the result of the format itself,
which is then a string, so you would have to convert it back to a date:

CDate(Format(now, "mm/dd/yyyy"))


but that is much more expensive than using DateValue.



Vanderghast, Access MVP

 

[MGFoster]

Table A was created with Dates and Time in each record and even when I change
the table format to Short Date to match table B (which has date but no time
data) the query will still not reurn any values because the date values are
not being considred even. How can I get the Date\Time in table a to be Date
only?

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

Format only changes the display of the data, not the actual data.
You'll have to run an UPDATE on the table, like this (BACKUP YOUR DATA
BEFORE DOING THIS):

UPDATE tableA
SET date_column = DatePart(date_column)

This will remove the time part of the date.

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/AwUBSo2tu4echKqOuFEgEQI87ACglBlbQKg3yaiY/ZfaO7LvBwitZ4gAoOlc
Fs+tPUna5CF31un094rxLzA6
=ZQ0g
-----END PGP SIGNATURE-----