Access VBA Best date format for SQL insert statement
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.
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")) & "')"
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Curious, is there a reason why you are using unbound forms, instead of simply binding to the table and validating the entry in the forms Before Update event?
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Open in new window
to convert text into date field on your insert query.