I have a sub inside an unbound form that appends a record to a table in SQL server using a pass-through query. The sub creates the necessary SQL then runs a Querydef. One of the fields is a date field. The function runs without error and inserts a record, but the date field is blank. However, if I cut and paste the SQL directly into SSMS it also inerts a record but with a date. I am confused. the code, & SQL is below.
The SQL that results from variable strSQL is below:
NB the function sqlDate takes a date argument and returns the date as a string in format YYYYMMDD.
I know I could have a get around by setting the date field in SQL with a default date of now, but I will need to use the same principle with dates other than today. So I would like to understand what is going wrong.
Code:
Dim strID As String: strID = GetNextIndex("IR", "tblItemRequest", "IR_ID")
Dim db As Database
Dim qdf As dao.QueryDef
Dim strSQL As String
strSQL = "INSERT INTO tblItemRequest ( [IR_ID], [I_PtNo], [StaffNumber], [IR_DateRequested], [IR_QtyRequested], [IR_Notes]) "
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & strID & "', '" & Me.txtPtNo & "', '" & Gbl_StaffNo & "', '" & sqlDate(Date) & "', " & txtQtyRequested & ", '" & Me.txtReason & "')"
Debug.Print strSQL
Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
qdf.Connect = conConnectionStores
qdf.SQL = strSQL
qdf.ReturnsRecords = False
qdf.Execute
db.Close
Set qdf = Nothing
Set db = Nothing
MsgBox "Added"
The SQL that results from variable strSQL is below:
Code:
INSERT INTO tblItemRequest ( [IR_ID], [I_PtNo], [StaffNumber], [IR_DateRequested], [IR_QtyRequested], [IR_Notes]) VALUES ('IR00000007', 'I_000012', '59899', '20191212', 1, 'test entry')
NB the function sqlDate takes a date argument and returns the date as a string in format YYYYMMDD.
I know I could have a get around by setting the date field in SQL with a default date of now, but I will need to use the same principle with dates other than today. So I would like to understand what is going wrong.