Solved Update Date query not working in AccessVBA

S

Sodslaw

Registered User.
Local time
Yesterday, 19:41
Joined
Jun 7, 2017
Messages
85
Hi i cant get a really simple peice VBA code to work

Have tried, but with no success..
Code: 
strSQL = "UPDATE TblOrderCustLink " & _
        "SET QuotePrintdate = Date() " & _
        "WHERE OrderCustLinkID = " & Me.QuoteOrderCustLink & ";"

works well in a Query, SQL statement...

Code: 
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.
 
"UPDATE TblOrderCustLink SET QuotePrintdate = " & format(Date(),"MM/DD/YYYY") " &" WHERE OrderCustLinkID = " & ME.QuoteOrderCustLink
Click to expand...

if quoteOrderCustLink is text then

Code: 
"UPDATE TblOrderCustLink SET QuotePrintdate = " & format(Date(),"MM/DD/YYYY") " &" WHERE OrderCustLinkID = '" & ME.QuoteOrderCustLink & "'"
 
You do not have a space berfore the where so it would resolve to
Code: 
"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
 
would help if you clarified what 'Have tried, but with no success..' means - you get an error?, wrong result? something else?

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

and what are you using to execute the sql?
 
Thanks for these replies so far i have tried...

Code runs but the date feild didnt update ( think i implemented it correctly)
Code: 
strSQL = "UPDATE TblOrderCustLink " & _
        "SET QuotePrintdate =#" & Date & "#" & "WHERE OrderCustLinkID = " & _
        Me.QuoteOrderCustLink & ";"
and
Code: 
strSQL = "UPDATE TblOrderCustLink " & _
        "SET QuotePrintdate =#" & Date & "# " & "WHERE OrderCustLinkID = " & _
        Me.QuoteOrderCustLink & ";"

I get a Compile error (quoteOrderCustLink is a number)
Code: 
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() " & _
Code: 
strSQL = "UPDATE TblOrderCustLink " & _
        "SET QuotePrintdate = Date() " & _
        " WHERE OrderCustLinkID = " & Me.QuoteOrderCustLink & ";"

Last one was i tried, but did not update the table.
Code: 
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. :)
 
Last edited:
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
 
Gasman said:
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
Click to expand...
table im trying to update is TblOrderCustLink...
QuotePrintDate to todays date where OrderCustLinkID = 1 ( as you can see its not updating to todays date.)
1644590121664.png


Debug print =
Code: 
UPDATE TblOrderCustLink SET QuotePrintdate =#11/02/2022#WHERE OrderCustLinkID = 1;

in full the code is...

Code: 
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
 
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? :(
 
Sodslaw said:
debug print now shows...
Code: 
UPDATE TblOrderCustLink SET QuotePrintdate =#11/02/2022# WHERE OrderCustLinkID = 1;
But still not updating the table :(
Click to expand...
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?
 
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..
Code: 
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.
 
Last edited:
Gasman said:
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? :(
Click to expand...

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

1644593078457.png


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
 
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
Click to expand...
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
 
CJ_London said:
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
Click to expand...
added "DBEngine(0)(0).Execute strSQL, dbFailOnError" beleive i must have deleted when trying varous versions of the codes above but still no banaba
 
Last edited:
in the sql query builder i use
Code: 
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.
 
Last edited:
UPDATE TblOrderCustLink SET QuotePrintdate =#11/02/2022# WHERE OrderCustLinkID = 1;
Click to expand...
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
 
CJ_London said:
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
Click to expand...
Great Advise
i added a new button to the form just to update the date and it seemd to do the trick.

really dont know why but noticed the debug print from this..
Code: 
UPDATE TblOrderCustLink SET QuotePrintdate =#11/02/2022# WHERE OrderCustLinkID = 1;
to this...
Code: 
UPDATE TblOrderCustLink SET QuotePrintdate = #2022-02-11# WHERE OrderCustLinkID = 1
and now working. I beleive, when trying those codes i deleted the sql exectution line at some point and the code i tried from then on didnt work. i can finally have a beer and start my weekend.

Thanks to everyone for your help and have a great weekend.
 
You must log in or register to reply here.

Similar threads

H
Solved IsNull not working in query
2
Replies
21
Views
1,563
hrdpgajjar
H
B
Update query doesn't like value formatted for a different region.
Replies
19
Views
1,865
Josef P.
J
B
DoCmd.RunCommand acCmdPaste stopped working, think office 365 update
Replies
3
Views
708
bignose2
B
amorosik
Program1 works fine, program2 doesn't, due to a date problem, but code inside are the same
Replies
17
Views
1,244
gemma-the-husky
gemma-the-husky
H
Solved Textbox "format" dropdown not working in properties in form design view
Replies
11
Views
909
GPGeorge
GPGeorge

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom