why does Update statement in VBA work incorrectly?

[dai_lo]

Hi,

I wrote an UPDATE statement in vba which would take and update input date into a table.

here is the code:

DoCmd.RunSQL "UPDATE DOP_new SET [Shipped] = -1, [Date of Shipment] = " & Itm_Date_of_Shipment & " Where ([Date of Shipment] is null) And [BarCode #] = " & Itm_BarCode & ";"

it incorrectly updates the column with 12/30/1899

if I put a fix text or Now() in the above, such as

DoCmd.RunSQL "UPDATE DOP_new SET [Shipped] = -1, [Date of Shipment] = format(Now(), 'mm/dd/yyyy') Where ([Date of Shipment] is null) And [BarCode #] = " & Itm_BarCode & ";"

it update the column correctly with today's date

please advise.

Alice

 

[DJkarl]

Hi,

I wrote an UPDATE statement in vba which would take and update input date into a table.

here is the code:

DoCmd.RunSQL "UPDATE DOP_new SET [Shipped] = -1, [Date of Shipment] = " & Itm_Date_of_Shipment & " Where ([Date of Shipment] is null) And [BarCode #] = " & Itm_BarCode & ";"

it incorrectly updates the column with 12/30/1899

if I put a fix text or Now() in the above, such as

DoCmd.RunSQL "UPDATE DOP_new SET [Shipped] = -1, [Date of Shipment] = format(Now(), 'mm/dd/yyyy') Where ([Date of Shipment] is null) And [BarCode #] = " & Itm_BarCode & ";"

it update the column correctly with today's date

please advise.

Alice

In Access dates need the # signs around them try adding them to your first example.

DoCmd.RunSQL "UPDATE DOP_new SET [Shipped] = -1, [COLOR="Red"][Date of Shipment] = #" & Itm_Date_of_Shipment &[/COLOR] "# Where ([Date of Shipment] is null) And [BarCode #] = " & Itm_BarCode & ";"

 

[dai_lo]

thanks.. it works