Date entry into the table by using vba

[sukhjinder]

While entering the date into the table using the following code



Private Sub cmdSave_Click()
Dim sSQL As String
Dim Basefk As Long

Basefk = Nz(Me.cboAllotSlot, 0)
If Basefk > 0 Then
sSQL = "INSERT INTO tblbasecall(baseID_FK,callID_FK,calldate)"
sSQL = sSQL & "VALUES (" & Me.cboAllcall.column(1) & "," & mid & " ," & me.txtcalldate & " );"
Debug.Print sSQL
CurrentDb.Execute sSQL, dbFailOnError
End If
End Sub

i got the values like 1-5-1894,2-6-1894 all the values are from 1800s series. why the code not inserting the same value as entered into the text box of the form.

[andy49]

If The txtcalldate is date time so should be surrounded by # tags in sql

If it isn't you need to format it as a date in the sql statement

Sent from my iPhone using Tapatalk

[sukhjinder]

it works fine with #

thanks a lot

[sukhjinder]

If The txtcalldate is date time so should be surrounded by # tags in sql

If it isn't you need to format it as a date in the sql statement

Sent from my iPhone using Tapatalk

#tags are working fine but i want to draw your attention towards the date and month. In the form i have entered 7-1-2017 (dd-mm-yyyy) but it is entering 1-7-2017 (dd-mm-yyyy) from month of jan it enterd july. Where there is wrong in the coding. But for higher dates after 12th (day of the month) it works fine.

[sukhjinder]

Why it is happening so i have searched on bing and google. it shows that SQL uses the format of type mm/dd/yyyy while inserting into table. hot to convert it to dd/mm/yyyy

[ssanfu]

sSQL = sSQL & "VALUES (" & Me.cboAllcall.column(1) & "," & mid & " ," & me.txtcalldate & " );"

What is "mid"?? Be aware that "mid" is a reserved word (and a built-in function) and shouldn't be used as an object name.

When working with dates, the dates MUST be in USA format (MM\DD\YYYY)
See Allen Browne's site http://allenbrowne.com/ser-30.html
Scroll down to Dates in Strings

If you add the function SQLDate, the SQL would look something like

sSQL = sSQL & "VALUES (" & Me.cboAllcall.column(1) &  "," & mid & " ," & SQLDate(me.txtcalldate) & " );"

Note: the function "SQLDate" adds the date delimiters, so you don't need to add them.