DATE/TIME Mismatch Error Join table in Access

[moorecurls]

I have a table that has a Date (CiDate) as DATE/TIME originally formatted as long date. I need to join it to another table that has Date (EffDte) as DATE/TIME originally formatted as short date. Both tables are created from an SQL Teradata query that pulls large amounts of data that I need to join on the CiDate and EfDte. I keep getting a data mismatch error. None of the dates are blank! I've tried the tables formatted a DATE/TIME with General Format as Short Date. Then I've tried queries to force the date into the short date format of DateValue([CiDate]) and DateValue([EffDte]) to append to the tables already created. I've tried queries to force the date into short date format of Format([CiDate], "mm/dd/yyyy") and Format([EffDte], "mm/dd/yyyy") appending to the tables with DATE/TIME General Format as Short Date.

I've tried changes the tables to Short Text fields with the General format as "mm/dd/yyyy". Then running queries to force the Date to Short Date like Format([CiDate], "mm/dd/yyyy") and Format([EffDte], "mm/dd/yyyy").

HELP! There has to be away to run a query to join on dates. Any ideas on how I can make this happen?

[June7]

Data is linked or local tables?

I have no problem joining on date values with Access tables and linked SQLServer table.

[NoellaG]

In SQL you have several date types: Date (accurate to 1 day; 3 bytes) , SmallDateTime (accurate to 1 minute; 4 bytes), DateTime( accurate to 0.003 secs; 8 bytes), DateTime(2) (accurate to 0.0000001 secs;10 bytes) and DateTimeOffset (datetime(2) but with timezone offset; 10 bytes). To be able to read the largeer date types access has included the Date/Time extended field type.
Perhaps first check what the SQL field types are, and add a step to the import process to convert the data to the correct type in a temp table before appending them.

[Minty]

Bear in mind that the Format() function returns a string - NOT a date.

CDate() would be better in most circumstances, but as NoellaG has stated it really depends on what the SQL original data is.
If it's DateTime2 and you are on an older version of the ODBC driver or old version of Access, it won't see it as a date and treat it as text.

[moorecurls]

To answer the questions: both are linked tables as I'm dealing with very large amounts of data. I would agree this issue is being caused by the source data coming from the Teradata SQL original query. The original tables has the DATE/TIME as TIMESTAMP(0), Not Null for the EffDte the other table has DATE/TIME as TIMESTAMP(6), Not Null for CiDate. I’m currently using temp tables in Teradata because of the large data. I was exporting the data to access and then converting the Date field. Any suggestions on the query I need to write in Teradata to convert the Date during the import process?
I have Teradata 16.20.53.24 db version, provider version Net 15.11.0.0 and Access 2007-2016 version.

I was using the Format() function to try to change the Date to text in both tables to see if that would allow a match on the data. I’ll try the Cdate() function to see if that works. Thanks for the Dlookup link. I’ll check it out to see if there is a query that will help convert the dates to the same format.
Any further assistance with this issue would be much appreciated.

[Gicu]

What do you see for data type for the two fields if you open the linked tables in Access in design view? Are they both date time? Never used Teradata but I see they have an ODBC driver for Windows, have you tried to link the tables using that?
https://downloads.teradata.com/downl...driver/windows

Cheers,