VBA query Inserting Time instead of Date

Question

I need some help with my query I am trying to insert to a table see above code:

CurrentDb.Execute ("INSERT INTO tblWarehouseItem ( whiwrhID, whiItemName, whivatName," whiVatRate, whiimtID, whiQty, whiPrice, whisupID, whiDateIn, whiExpiryDate,whiwhiID ) " & _
                                   " Values (" & rs!WID & "," & Chr(34) & rs!N & Chr(34) & "," & Chr(34) & rs!VN & Chr(34) & "," & rs!VR & "," & rs!IID & "," & intQtyForm & "," & rs!PR & "," & rs!SID & "," & CDate(rs!DIN) & "," & CDate(rs!EXD) & "," & rs!ID & ")")

In my table the CDate(rs!EXD) and CDate(rs!DIN) are stored as time.

When I compile my query in the immediate window I get Dates.

INSERT INTO tblWarehouseItem ( whiwrhID, whiItemName, whivatName, whiVatRate, whiimtID, whiQty, whiPrice, whisupID, whiDateIn, whiExpiryDate,whiwhiID )  Values (2,"ITEM10","A",19,14,4,20,10,21/07/14,26/05/14,60)

How can I make my query to insert the date in the table?

thanks in advance

is there a particular reason you are using a recordset to populate the query instead of doing a INSERT INTO ... SELECT which would sidestep this entire problem? Or, you can Format(rs!DIN,'dd/mm/yyyy HH:MM') or, TimeValue() — serakfalcon
– serakfalcon, Commented Jul 23, 2014 at 10:58

Alex K. · Accepted Answer · 2014-07-23 11:09:35Z

2

If you run that 21/07/14 is passed to Access and interpreted as an integer resulting from 12 divide by 7 divide by 14 which when converted to a date is just a time.

Delimit dates with #:

.. ",#" & CDate(rs!DIN) & "#," ..

edited Jul 23, 2014 at 11:09

answered Jul 23, 2014 at 11:04

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA query Inserting Time instead of Date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related