troubleshooting Question

Access VBA Best date format for SQL insert statement

Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessVBASQL
3 Comments1 Solution17 ViewsLast Modified:
Hi
In Access when inserting a date from a text box that has a date format of dd-mm-yyyy
I use the code in the second part below to convert the dates. Is this the best method or is there a better way such as the following.
Format(Me.DateReceived, "yyyy-mm-dd")

    Dim oDateReceived As String: oDateReceived = Format(Me.DateReceived, "Medium Date")
    
    Dim oLastRegRenewal As String: oLastRegRenewal = Format(Me.DateReceived, "Medium Date")
    Dim oRegExpiryDate As String: oRegExpiryDate = Format(Me.ExpiryDate, "Medium Date")
    
    
    'Create Firearm
    ''Dim SN2 As String: SN2 = Nz(txtSN2, "")
    sSQL = "Insert into t_Arms (LicenceNumberFK,Manufacturer,Country,[Year Manuf],Model,Calibre1,Calibre2,SerialNo,SerialNo2,SerialNo3,ArmType,ActionType,"
    sSQL = sSQL & "Status,Source,SourceComment,Remarks,CreatedDTG,LastRegRenewal,RegExpiryDate,ProcessingOfficer,Image,AssaultRifle,Latitude,Longitude,Location)"
    sSQL = sSQL & " values ("
    sSQL = sSQL & Me.LicenceNumber & ","
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.Manufacturer, "")) & "',"
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.CountryOfManufacture, "")) & "',"
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.YearOfManufacture, "Unknown")) & "',"
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.Model, "")) & "',"
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.Calibre1, "")) & "',"
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.Calibre2, "")) & "',"
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.SerialNo, "")) & "',"
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.SerialNo2, "")) & "',"
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.SerialNo3, "")) & "',"
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.ArmType, "")) & "',"
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.ActionType, "")) & "',"
    sSQL = sSQL & "'Registered'," '[Status]
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.Source_Combo, "")) & "'," '[Source]
    sSQL = sSQL & "'" & SQLSafe(Me.SourceComment) & "'," '[Source Comment]
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.Remarks, "")) & "'," '[Remarks]
    sSQL = sSQL & "'" & Now() & "',"
    sSQL = sSQL & "'" & oLastRegRenewal & "',"
    sSQL = sSQL & "'" & oRegExpiryDate & "',"
    sSQL = sSQL & "'" & SQLSafe(sUserCode) & "',"
    sSQL = sSQL & "'" & SQLSafe(oGatherPictures) & "',"
    sSQL = sSQL & "'" & oAssaultRifle & "',"
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.Latitude, "0")) & "'," 'Zero length not allowed
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.Longitude, "0")) & "'," 'Zero length not allowed
    sSQL = sSQL & "'" & SQLSafe(Nz(Me.Location, "None Specified")) & "')"



Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros