VBA SQL and CDate

Question

I have a query in VBA that looks like so...

locSQL_Complete_Details = ""

locSQL_Complete_Details = locSQL_Complete_Details & "SELECT A.[Supplier]," & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "       A.[Sales Person]," & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "       A.[Sales Code]," & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "       A.[State]," & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "       A.[Customer Name]," & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "       A.[Account Number]," & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "       CDate(A.[Usage Start Date])," & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "       CDate(A.[Usage End Date])," & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "       B.[KY_PROFDTL_SEQ]," & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "       B.[QY_ELEM_VALUE]," & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "       B.[TX_ELEM_VALUE]" & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "FROM   Table_A                                      A" & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "INNER JOIN Table_B                                  B" & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "       ON  ([B].[Supplier]        =       [A].Supplier)" & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "       AND ([B].[ER Number]     =       [A].[ER Number])" & vbCrLf

locSQL_Complete_Details = locSQL_Complete_Details & "AND ([B].[Usage Start Date] = CDate([A].[Usage Start Date]))"

locSQL_Complete_Details = locSQL_Complete_Details & "AND ([B].[Usage End Date] = CDate([A].[Usage End Date]))"

(I apologize about the double-spacing....)

Table_A[Usage Start Date] and Table_A[Usage End Date] are BOTH defined as Text! And Table_B[Usage Start Date] and Table_B.[Usage End Date] are defined as "Date/Time". Thus the use of "CDate" on the Table_A columns.

When I attempt running this, I get "Data type mismatch in criteria expression"

Can anyone help me out here as to what might be wrong???

I appreciate your review and am hopeful for a reply.

PSULionRP

Answer 1

Do you need to convert the [B].[Usage Start Date] to a CDate when you compare them?

Can you look at the data from each table to see if they are the same types?

Answer 2

See my other answer to the same question earlier today. Use an Alias or the ordinal number of the field.

Henry