Don't know how to make an update query

[lawdy]

I am having trouble learning queries. The sub following runs, but it don't update the table. Please help.



#Sub modQue_addNewLocation() ' update query Dim db As DAO.Database
Set db = CurrentDb
Dim dateDate As Date
Dim strLineNo As String
Dim strSQL As String

dateDate = DateValue("9/27/2025")
strLineNo = "104"
strSQL = "UPDATE tblCalendar SET fLngProposalNo = 20250870 " _
& "WHERE fDate = #" & dateDate & "# AND fStrLineNo = ' & strLineNo & ';"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub#

The fields in the table are fDate , fStrLineNo . and fLngProposalNo .
I am trying to put the proposal number (20250870 ) in the table. Thank you.

[davegri]

& "WHERE fDate = #" & dateDate & "# AND fStrLineNo = '" & strLineNo & "'"

[Gustav]

You must format the date value to a string expression to be safe:

strSQL = "UPDATE tblCalendar SET fLngProposalNo = 20250870 " & _
"WHERE fDate = #" & Format(dateDate, "yyyy\/mm\/dd") & "# AND fStrLineNo = '" & strLineNo & "';"

[Welshgasman]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything
Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.
Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.
Example:
tt="Eg'g"
? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")

Same applies to building query sql.

As I kept forgetting how to protect a character I used something I found on the net.

'Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
Public Const strcJetDate = "\#yyyy-mm-dd\#"  'Needed for dates in queries as Access expects USA but will accept ISO format.

Then use that in the Format() function.

[CJ_London]

Your sub is called addNewLocation but using an update query. Update queries don’t add a new record, they update an existing one.

perhapd that is what you intend, but the comments are at odds with the name.

to add a new location, you use an insert query

[Ken Sheridan]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

A literal double quote character is represented by a contiguous pair of double quote characters, which can be anywhere within a literal string expression delimited by double quote characters in the usual way, so they are not necessarily triple double quotes in the sense that there is a triplet of contiguous double quote characters. People often seem to have difficulties with this, though I'm not sure why. All that's necessary is to put two contiguous double quote characters "" wherever you would normally put a single quote character ' in the string. e.g.

MyString = "My name in its unanglicized form is ""Cináed O'Siridean"""

which would evaluate to:

My name in its unanglicized form is "Cináed O'Siridean"

[lawdy]

That solved my problem. Thanks to everyone.

[lawdy]

I marked this solved. However I am still having trouble with it. I had to add more criteria and I also using variables. These queries give me a hard time. The following is what I have:
#
Sub modQue_updateNewLocation() ' update tblCalendar query
Dim db As DAO.Database
Set db = CurrentDb
Dim strSQL As String
Dim strSvc As String
Dim dateDate As Date
Dim strLineNo As String
Dim lngProposalNo As Long

dateDate = DateValue("9/30/2025") ' Forms![frmCalendar].Form![hTbToDate]
strLineNo = "103" ' Right(Forms![frmCalendar].Form![hTbToName], 3)
strSvc = "stripe" ' Forms![frmCalendar].Form![hTbService]
lngProposalNo = 20250537 ' Forms![frmCalendar].Form![hTbProposalNo]

strSQL = "UPDATE tblCalendar SET fLngProposalNo = " & lngProposalNo & " AND " _
& "fTxtService = '" & strSvc & "'" _
& "WHERE fDate = #" & dateDate & "# AND fStrLineNo = '" & strLineNo & "'"


DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

End Sub
#
I also changed the query name. It is an update query. It runs but it don't update the table. Please help.

[Welshgasman]

Debug.print strSQL until you get it correct
Only then attempt to use it.

You have already been advised to use format() on the date.

I tend to use Select first to make sure I have the correct records, then change to required query type.

[lawdy]

I changed the line to:
& "WHERE fDate = #" & Format(dateDate, "mm/dd/yyyy") & "# AND fStrLineNo = '" & strLineNo & "'"

The query ran without error but did not update the table. Any ideas.

[CJ_London]

Have you checked that a record exists in the table that has the date and line number required. And that the date in the field does not have a time element, perhaps hidden with formatting

[Ken Sheridan]

I changed the line to:
& "WHERE fDate = #" & Format(dateDate, "mm/dd/yyyy") & "# AND fStrLineNo = '" & strLineNo & "'"

The query ran without error but did not update the table. Any ideas.

Debug.Print the SQL statement to the immediate window. Then copy and paste it into the query designer, and open the query as a datasheet. Does it return a row? If not, it points to the row you are trying to update not existing in the table.

[lawdy]

There is for sure a record where the fDate is 9/30/2025 and fStrLineNo is 103. I have check over & over. I printed the sql with the same non results. I don't know what to do.

[CJ_London]

Would help if you provided the full code since, based on the code you provided in post #8, you need a space before WHERE.

And comment out setwarnings until you know the code is working as required

Use debug.print strSQL to check you have the correct values

[Welshgasman]

Well use a select and get to see the record first. As mentioned if there is a time element in there (and you should know that), then it will not match. In that case use DateValue()