Solved Update Date query not working in AccessVBA

[Sodslaw]

Hi i cant get a really simple peice VBA code to work

Have tried, but with no success..

strSQL = "UPDATE TblOrderCustLink " & _
        "SET QuotePrintdate = Date() " & _
        "WHERE OrderCustLinkID = " & Me.QuoteOrderCustLink & ";"

works well in a Query, SQL statement...

UPDATE TblOrderCustLink SET TblOrderCustLink.QuotePrintdate = Date()
WHERE (((TblOrderCustLink.OrderCustLinkID)=[forms]![FRM_Order]![QuoteOrderCustLink]));

any ideas? I know its something stupid.

I have tried several variations but no joy, its driving me nuts any help would be appreciated.

thanks.

 

[isladogs]

In VBA, use
=#" & Date() & "#" & _

 

[MajP]

"UPDATE TblOrderCustLink SET QuotePrintdate = " & format(Date(),"MM/DD/YYYY") " &" WHERE OrderCustLinkID = " & ME.QuoteOrderCustLink

if quoteOrderCustLink is text then

"UPDATE TblOrderCustLink SET QuotePrintdate = " & format(Date(),"MM/DD/YYYY") " &" WHERE OrderCustLinkID = '" & ME.QuoteOrderCustLink & "'"

 

[MajP]

You do not have a space berfore the where so it would resolve to

"SET QuotePrintdate = Date() " & "WHERE OrderCustLinkID =
to
= Date()WHERE OrderCustLinkID ="

SQL is pretty code about not needed spaces, but I think it will see "Date()WHERE" as one thing

 

[CJ_London]

would help if you clarified what 'Have tried, but with no success..' means - you get an error?, wrong result? something else?

try

strSQL = "UPDATE TblOrderCustLink" & _
        " SET QuotePrintdate = #" & format(Date(),"yyyy-mm-dd") & "#" & _
        " WHERE OrderCustLinkID = " & Me.QuoteOrderCustLink

and what are you using to execute the sql?

 

[Sodslaw]

Thanks for these replies so far i have tried...

Code runs but the date feild didnt update ( think i implemented it correctly)

strSQL = "UPDATE TblOrderCustLink " & _
        "SET QuotePrintdate =#" & Date & "#" & "WHERE OrderCustLinkID = " & _
        Me.QuoteOrderCustLink & ";"

and

strSQL = "UPDATE TblOrderCustLink " & _
        "SET QuotePrintdate =#" & Date & "# " & "WHERE OrderCustLinkID = " & _
        Me.QuoteOrderCustLink & ";"

I get a Compile error (quoteOrderCustLink is a number)

strSQL = "UPDATE TblOrderCustLink SET QuotePrintdate = " & format(Date(),"MM/DD/YYYY") " &" WHERE OrderCustLinkID = " & ME.QuoteOrderCustLink

Added a space before the where (but i think it had a space after the Date() on the second line ...Date() " & _

strSQL = "UPDATE TblOrderCustLink " & _
        "SET QuotePrintdate = Date() " & _
        " WHERE OrderCustLinkID = " & Me.QuoteOrderCustLink & ";"

Last one was i tried, but did not update the table.

strSQL = "UPDATE TblOrderCustLink" & _
        " SET QuotePrintdate = #" & Format(Date, "yyyy-mm-dd") & "#" & " WHERE OrderCustLinkID = " & _
        Me.QuoteOrderCustLink

As im in the UK the format im using is DDMMYYY so i have retried the above with dd-mm-yyyy formatting, but i got the same results
Maybe its time to smoke something stronger than a cigarette.

 

[Gasman]

Start using debug.print strSQL
That is the point of putting it into a variable, rather than composing on the fly.

FWIW, the last attempt looks fine to me, but I would still look at a debug.print

 

[Sodslaw]

Start using debug.print strSQL
That is the point of putting it into a variable, rather than composing on the fly.

FWIW, the last attempt looks fine to me, but I would still look at a debug.print

table im trying to update is TblOrderCustLink...
QuotePrintDate to todays date where OrderCustLinkID = 1 ( as you can see its not updating to todays date.)

Debug print =

UPDATE TblOrderCustLink SET QuotePrintdate =#11/02/2022#WHERE OrderCustLinkID = 1;

in full the code is...

Private Sub PrevQuote_Click()
Dim Answer As Variant
Dim strSQL As String
'Dim TDate As String
'LDate = Date

 
Me.Refresh

If Me.OrderType.Value = "Quotation" Then

    strSQL = "UPDATE TblOrderCustLink " & _
            "SET QuotePrintdate =#" & Date & "#" & _
            "WHERE OrderCustLinkID = " & Me.QuoteOrderCustLink & ";"
    MsgBox "QuoteOrderCustLink=" & Me.QuoteOrderCustLink
    Debug.Print strSQL
        DoCmd.OpenReport "SalesQuotation", acViewPreview, , "OrderID = " & Me.OrderID
End If

If Me.OrderType.Value = "Specification" And Not IsNull(Me.SpecNo.Value) Then
    'Me.LockQuote = True
    DoCmd.OpenReport "SalesSpecification", acViewPreview, , "OrderID = " & Me.OrderID
End If

End Sub

 

[GPGeorge]

Check post #4

 

[Sodslaw]

Check post #4

debug print now shows...

UPDATE TblOrderCustLink SET QuotePrintdate =#11/02/2022# WHERE OrderCustLinkID = 1;

But still not updating the table

 

[Gasman]

Put all the output of the debug.print into a query window and run it, and see what it complains about.

Have you tried a refresh/requery, as you are not saying how you know it has not been updated?

 

[GPGeorge]

debug print now shows...

UPDATE TblOrderCustLink SET QuotePrintdate =#11/02/2022# WHERE OrderCustLinkID = 1;

But still not updating the table

Sometimes we get reports of outcomes -- "not updating" -- instead of descriptions of behavior. That makes it harder to guess at the problem.

What DOES happen instead of updating the table? An error? No Change? Are there even records in the table which match that OrderCustLinkID that could be updated?

 

[Sodslaw]

As you can see from the screen shot #8 of the table im expecting the "QuotePrintDate" to update from 01/02/2022 to todays date . In a similar way when i run the query manually, which in sql view it looks like this..

UPDATE TblOrderCustLink SET TblOrderCustLink.QuotePrintdate = Date()
WHERE (((TblOrderCustLink.OrderCustLinkID)=[forms]![FRM_Order]![QuoteOrderCustLink]));

but when running the VBA code there are no errors the code just proceded to DoCmd.OpenReport.....

after the DoCmd.OpenReport is done and closing the preview, i am opening the Table from the navplane to see if the date is its original 01/02/2022 or the current Date so far all the VBA codes tried will not update the date.

 

[Sodslaw]

Put all the output of the debug.print into a query window and run it, and see what it complains about.

Have you tried a refresh/requery, as you are not saying how you know it has not been updated?

i have put the debug.print code in a query window and ran it, the results are...

after clicking on Yes i closed and saved the query and opened the table. it did not update the date as expected (same as VBA code) hope this helps

 

[CJ_London]

getting confused - I don't see any code which actually executes the sql. I would expect something like

currentdb.execute strSql

And I don't see any code which generates that message

As im in the UK the format im using is DDMMYYY

sql always uses the US format of mm/dd/yyyy or the sql standard of yyyy-mm-dd

if you have a UK date of 3/2/2022, (3rd Feb) that will be interpreted by sql as 2nd March. Only exception is if the day >12 since there are only 12 months in the year.

You are creating a sql string so to let sql know that the string is to be interpreted as a date, you surround it with the # character - and format the date as either mm/dd/yyyy or yyyy-mm-dd. If you are in the US or a country that uses the US date format, then you don't need to format it

dates are stored as decimal numbers - the value before the dp is the number of days since 31/12/1899, the bit after the number of seconds divided by 86400, the number of seconds in a day. What you see as a date is just a formatted view based on your windows settings

 

[Pat Hartman]

When you use Date(), you do not need to format it. Date() is a function and the function is sent to the server NOT a string. ONLY STRING dates require formatting.

Are you sure the where clause is correct? What, exactly does "does not work" mean?

 

[Sodslaw]

getting confused - I don't see any code which actually executes the sql. I would expect something like

currentdb.execute strSql

And I don't see any code which generates that message


sql always uses the US format of mm/dd/yyyy or the sql standard of yyyy-mm-dd

if you have a UK date of 3/2/2022, (3rd Feb) that will be interpreted by sql as 2nd March. Only exception is if the day >12 since there are only 12 months in the year.

You are creating a sql string so to let sql know that the string is to be interpreted as a date, you surround it with the # character - and format the date as either mm/dd/yyyy or yyyy-mm-dd. If you are in the US or a country that uses the US date format, then you don't need to format it

dates are stored as decimal numbers - the value before the dp is the number of days since 31/12/1899, the bit after the number of seconds divided by 86400, the number of seconds in a day. What you see as a date is just a formatted view based on your windows settings

added "DBEngine(0)(0).Execute strSQL, dbFailOnError" beleive i must have deleted when trying varous versions of the codes above but still no banaba

 

[Pat Hartman]

Are you getting an error message? Is the ID in the table? Is the table updateable? Doesn't work doesn't tell us anything.

 

[Sodslaw]

in the sql query builder i use

UPDATE TblOrderCustLink SET TblOrderCustLink.QuotePrintdate = Date()
WHERE (((TblOrderCustLink.OrderCustLinkID)=[forms]![FRM_Order]![QuoteOrderCustLink]));

it works everytime. ie after running the query and openening the table TblOrderCustLink the QuotePrintdate in TblOrderCustLink = 11/02/2022 (todays date)

im just trying to replicate it in VBA.

the VBA codes i have tried i cant seem to get it to work. What, exactly does "does not work" mean? = Its not updating the QuotePrintdate to todays date in the TblOrderCustLink.
How do i know this because after running the code i open the navplane and open the TblOrderCustLink table and see that the date does not update.
Is the where clause correct? i beleive so, the vba code has a message box the reads me.QuoteOrderCustLink as "1" so i beleive it to be true
as TblOrderCustLink.OrderCustLinkID =1 as per the screen shot of the table in #8

im not seeing any errors but the QuotePrintDate date <>Date() the same as when putting the debug print into an sql statement.

 

[CJ_London]

UPDATE TblOrderCustLink SET QuotePrintdate =#11/02/2022# WHERE OrderCustLinkID = 1;

if it was working, it would update it to 2nd November.

You are jumping all over the place, stop, take a breath . Provide the full vba code you are now actually using. And whilst you are at it, provide the recordsource to your form (or subform if that where your code is running). It may be you have the record locked