CODEWITHSUNDEEP
datems-accessinsertvb6
David
David

Reputation: 2324

Why would this INSERT query insert a time value, rather than a date?

I have a field in an MS-Access database that is shown below.

enter image description here

When I run this query on the database (from a VB6 application or directly as a query)

INSERT INTO [stockQuantities] ([stockCode], [description], [productGroup], [qtyFrom], " & _
"[price], [dateQuoted]) VALUES (" SOS ", "SOS SAND FOR COSTING ONLY", "COS", 10, " & _
"6.34, 01/12/2018);

The final value, for dateQuoted is instead inserting as 00:00:04. When I change the format to Short Date, it inserts as 30/12/1899.

Why is my date not inserting in the correct format? Is it the query or the field that's the issue?

Upvotes: 1

Views: 56

Answers (1)

Erik A
Erik A

Reputation: 32632

Dates in Access SQL need to be delimited by octothorpes, and in either mm/dd/yyyy or yyyy-mm-dd format.

Your query should look like this (assuming you want to insert the first of December):

INSERT INTO [stockQuantities] ([stockCode], [description], [productGroup], [qtyFrom], " & _
"[price], [dateQuoted]) VALUES (" SOS ", "SOS SAND FOR COSTING ONLY", "COS", 10, " & _
"6.34, #12/01/2018#);

However, you're also allowed to insert a numerical value, which is the value in days offset from 1899-12-30. Your insert query just inserted 1 divided by 12 divided by 2018, which is a value of 4.388E-05 , and that corresponds to 4 seconds from Access's reference date.

If the date for a date-time column is equal to the reference date, Access only displays the time portion.

Upvotes: 5

Related Questions