Don't know how to make an update query

[davegri]

You can't use "AND" for naming multiple table field names, the separator is a comma.

Sub modque_addnewlocation() ' update query Dim db As DAO.Database
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim dateDate As Date, strLineNo As String, strSQL As String
    Dim strSvc As String, lngProposalNo As Long
    dateDate = DateValue("9/27/2025")
    strLineNo = "104"
    strSvc = "stripe"
    lngProposalNo = 20250537
    strSQL = "UPDATE tblCalendar SET fLngProposalNo = " & lngProposalNo & ", " _
    & "fTxtService = '" & strSvc & "'" _
    & " WHERE DateValue(fDate) = #" & dateDate & "# AND fStrLineNo = '" & strLineNo & "'"
    Debug.Print strSQL
    db.Execute strSQL, dbFailOnError
End Sub

Immediate Pane: shows debug.print
call modque_addnewlocation
UPDATE tblCalendar SET fLngProposalNo = 20250537, fTxtService = 'stripe' WHERE DateValue(fDate) = #9/27/2025# AND fStrLineNo = '104'



Result:

[lawdy]

I just got it. I have an AND in the set statement as follows:
strSQL = "UPDATE tblCalendar SET fLngProposalNo = " & lngProposalNo & " AND " _

It should have a comma instead.
strSQL = "UPDATE tblCalendar SET fLngProposalNo = " & lngProposalNo & ", " _

So the query now works. I appreciate everyone's help.

[lawdy]

Thank you davegri

[Gustav]

You miss a space before WHERE and to use the correct syntax:

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

[Welshgasman]

FWIW I always put the space at the start of the next line. Easier to see than having to scroll to the right all the time.

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

[Ken Sheridan]

You can correct any rows which contain a non-zero tome of day element in the fDate column by executing the following update query:

UPDATE tblCalendar
SET fDate = INT(fDate)
WHERE fDate IS NOT NULL;

Having done that set the ValidationRule property of the fDate column in table design view to:

Int([fDate]) Or Is Null

or, if the Required property of the column is True (Yes), to:

Int([fDate])

If you are still experiencing the same behaviour can you attach a copy of your file, with any personal or otherwise sensitive data disguised? All we really need is the tblCalendar table with the row in question, so you can copy and paste that into a new .accdb file if you do not want to expose your other data. The copy must be exhibiting the errant behaviour of course.