Access 2003 - VBA - Joining on two types (string = date)

Question

Background: Im busy with a research, in which I receive data stored in a oracle database. I myself use an Access database to store the data from the oracle database (and as well data from other sources, not relevant for this question)

The data from the oracle database is given to me in access format. Then I run the following query to retrieve the "oracle data" and insert (or update if the row already excist) into my own access database.

The oracle database export saved it's dates in text format, I have date formats in my own database, so I use a VBA function to transform the texts in dates.

My question: Can I join on a string and a date if the string is transformed to a date? How should I proceed?

UPDATE [TABLE1] INNER JOIN [URI_to_oracle_export.mdb].[TABLE2] 
ON ([TABLE1].PT=[TABLE2].PT) 
AND  ([TABLE1].DCMDATE=toDateFunction([TABLE2].DCMDATE)) 
AND ([TABLE1].CPEVENT=[TABLE2].CPEVENT) SET ...the rest of the SQL..

My toDateFunction:

Function toDateFunction(input As String)

toDateFunction= CDate(Right(input , 2) & "/" & Mid(input , 5, 2) & "/" & Left(input , 4))

End Function

Matt Donnan Matt Donnan · Accepted Answer · 2013-06-13T14:31:40

So based on your conversion function it looks as though the Oracle text format you are receiving is YYYYMMDD, for using type conversions for a join in this case I would prefer to work the other way round and transform my access value to text e.g:

WHERE OracleTable.Date = Format(AccessTable.Date, "YYYYMMDD")

Or

INNER JOIN OracleTable ON OracleTable.Date = Format(AccessTable.Date, "YYYYMMDD")

You could still do it in reverse and convert your Oracle value to a date if you wanted which would be more like:

WHERE DateSerial(Left(OT.Date,4),Mid(OT.Date,5,2),Right(OT.Date,2)) = AT.Date

When building your date value literally I prefer using DateSerial to CDate

Access 2003 - VBA - Joining on two types (string = date)

1 Answers