Update Query

[Sanjeet Prasad]

I have a script as below :

Public Sub DoSQL()

Dim SQL123 As String
Dim TEST As String

TEST = "23/01/2003"

SQL123 = "UPDATE AR_Invoice " & _
"SET AR_Invoice.IMPORT = 'Y' " & _
"WHERE AR_Invoice.InvoiceDate = '23/01/2003'"

DoCmd.RunSQL SQL123
End Sub

this script works fine and records are updated.

Now I want to use the same with date being a variable as below.The Dated variable is a global varibale which is captured from a form:

Public Sub DoSQL()

Dim SQL123 As String
Dim TEST As String



SQL123 = "UPDATE AR_Invoice " & _
"SET AR_Invoice.IMPORT = 'Y' " & _
"WHERE AR_Invoice.InvoiceDate = " & "'" & Dated & "'"

DoCmd.RunSQL SQL123
End Sub

I have noticed that when I hard code the date this works and when I use varibale this does not work.When I execute the script it says 0 records updated, when in fact it should updated 45 records for which I am sure.

I feel it is a conversion problem from date/time to string and vice versa.I hav e not been able to recify the problem.Hlep please.Thanks.

 

[ejstefl]

Try using: Format(Dated, "DD/MM/YYYY") instead of just Dated.

 

[Sanjeet Prasad]

I just tried that it still give the same msg. 0 records updated

 

[ejstefl]

That should work. What field type is AR_Invoice.InvoiceDate?

Can you put a stop in and see what SQL123 evaluates to? That may help determine where the probelm is.

 

[Sanjeet Prasad]

Its a Date/Time field in AR_Invoice.I am not able to see the whole of the SQL123 as it seems its priety long and it folds to .....