CODEWITHSUNDEEP
ms-accessvba
user3147504
user3147504

Reputation: 21

Date format in Access insert into sql statement

Code below, but the date in the table is not showing correctly, my computer system date is set to yyyy,mm,dd and the Access table field is selected to short date. As seen in code below the date is showing fine when debugging and stepping through the program but at the end the date in the table is shown as 1905-12-30 (Which should be 2013-12-30) any suggestions please?

InsDate = Date
**InsDate** = Format(InsDate, "yyyy,mm,dd")

AppeQry = "INSERT INTO TStockMaster ( SupplierID, PartID, UnitsID, ConditionID, " & _
          "QTY, WarehouseID, BinID, RowID, ColumnID, InsDate ) VALUES ( " & SupID & "," & PrtID & "," & UntID & "," & _
          CondID & "," & Qt & "," & WarehID & "," & BnID & "," & RwID & "," & ColID & "," & **InsDate** & ");"

Upvotes: 1

Views: 1387

Answers (1)

HansUp
HansUp

Reputation: 97131

Use a parameter query instead of concatenating values as text into an INSERT statement.

Then, when you execute the statement, and supply the parameter value, you can give it the actual Date/Time value and not be bothered with text format and date type delimiters.

Here is a simplified example. Save this query as qryTStockMasterAppend.

INSERT INTO TStockMaster (InsDate) VALUES (pInsDate);

Then your VBA code can use that saved query, supply the parameter value and execute it.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTStockMasterAppend")
qdf.Parameters("pInsDate") = Date()
qdf.Execute dbFailOnError

Upvotes: 2

Related Questions