Normally I figure out my
DateTime issues in
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
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.
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() " _