Access VBA: SQL String with string tablefield converted as date

Normally I figure out my DateTime issues in VBA and SQL by trial and error, but in this case I have hit a wall.

I am not even sure I can do what I want.

I have a temporary table with fields F1, F2, etc.

Field F7 contains a date as a string n the format dd/MM/yyyy.

Now, I want to copy the data from the temporary table, to a table where the data in field F7 should be stored in a Date/Time field. The format of this field is Short Date (dd-MM-yyyy).

I have tried using CDate(), Format() and Trim() alone and in several combinations, along with padding my date data with ## and adding brackets [] to my table fieldname, but I simply cannot find the correct combination.

This is my code. The importedDate works fine because I am using a variable. But for the field F7, I am mixing the SQL query with the VBA date conversion, and it is not working for me:

Sub MoveImportedData()
Dim SQLStr As String


SQLStr = "INSERT INTO TblDebitorSaldoListe " _
& "(CompanyCode,CompanyName, CustomerNumber, CustomerName, OneTimeCustomerName, TermsOfPayment, NetDueDate, Reference, " _
& "DunningBlock,Comment, ReminderOne, ReminderTwo, DebtCollection, TotalAmountDKK, TotalNotYetDueDKK, TotalOverdueDKK, " _
& "ReminderOneFile, ReminderTwoFile, NoReminder, ImportedDate) " _
& "SELECT F1, F2, F3, F4, F5, F6, #" & CDate("F7") & "#, F8, F9, '', '', '', '', F10, F11, F12, '', '', 0, '" & CDate(Date) & "' " _
& "FROM TEMP WHERE Len(F1)=4" _

MsgBox SQLStr

DoCmd.RunSQL SQLStr

End Sub

Any help would be much appreciated. I am using Access 2013.

Thanks.

Answer

It’s really quite simple:

& "SELECT F1, F2, F3, F4, F5, F6, DateValue(F7), F8, F9, '', '', '', '', F10, F11, F12, '', '', 0, Date() " _

or, if F7 is dotted (corrected to have single-quotes with Replace):

& "SELECT F1, F2, F3, F4, F5, F6, DateValue(Replace(F7, '.', '-')), F8, F9, '', '', '', '', F10, F11, F12, '', '', 0, Date() " _

Advertisement

Answer