help me with this SQL code (1 Viewer)

[azhar2006]

Hello my friends
Can i get your help with this code. I want to add (NumberUpgradeAfter) and (DateUpgradeAfter) to (strSQL) clause

While rs.EOF = False
    
        strStatFig = rs![StatFig]
        strRtba = rs![Rtba]
        strNumberUpgradeAfter = rs![NumberUpgradeAfter]
        strDateUpgradeAfter = rs![DateUpgradeAfter]
        
        
        strSQL = "UPDATE tblmastr SET tblmastr.Rtba = '" & strRtba & "' WHERE ((tblmastr.StatFig)='" & strStatFig & "')"
        
        db.Execute strSQL, dbFailOnError
        
        rs.MoveNext
    
    Wend

 

[Gasman]

I always start at the source?

UPDATE statement (Microsoft Access SQL)

Office developer client VBA reference documentation

learn.microsoft.com

Debug.Print your strSQL to see what you have before trying to use it.

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 for someone else to spot it.

 

[azhar2006]

Thank You So Much Gasman , Dear
All fields referred to in the table are text.

 strSQL = "UPDATE tblmastr SET tblmastr.Rtba = '" & strRtba & "'.....tblmastr.NumberUpgradeAfter and DateUpgradeAfter

 

[The_Doc_Man]

Gasman's example link didn't show the syntax for multiple-field updates.

You make the final query like this: UPDATE table SET fieldA = valueA, fieldB = 'valueB', fieldC = #date-string-for-C# ;
(i.e. comma-separated clauses of "field=value" with appropriate punctuation. In this example, fieldA is numeric so you need no special punctuation if valueA is literal. FieldB is short or long text so you need a quoted string. The quoting method you showed us in your first example was correct for a concatenated string inclusion. In my example, FieldC is a date field so you need to put in a constant date value which, if literal, must be delimited by the octothorpe (#). You suggested that all values were strings, but what matters is the data type of the target fields.

You appear to be inviting us to build your SQL for you but we don't know the correct values to enter. You will have to supply that. But I will save you some typing. You showed us an UPDATE example for a single table. IF this is a single-table update you do not have to qualify the fields because the only place they CAN be is in that single table. Therefore, your SQL statement could resemble, in part

strSQL = "UPDATE tblmastr SET Rtba = '" & strRtba & "', ...

Note also that if this were a JOIN update (not often recommended), you WOULD need to qualify where the fields were located since multiple tables would be involved in that case.

 

[azhar2006]

Gasman's example link didn't show the syntax for multiple-field updates.

You make the final query like this: UPDATE table SET fieldA = valueA, fieldB = 'valueB', fieldC = #date-string-for-C# ;
(i.e. comma-separated clauses of "field=value" with appropriate punctuation. In this example, fieldA is numeric so you need no special punctuation if valueA is literal. FieldB is short or long text so you need a quoted string. The quoting method you showed us in your first example was correct for a concatenated string inclusion. In my example, FieldC is a date field so you need to put in a constant date value which, if literal, must be delimited by the octothorpe (#). You suggested that all values were strings, but what matters is the data type of the target fields.

You appear to be inviting us to build your SQL for you but we don't know the correct values to enter. You will have to supply that. But I will save you some typing. You showed us an UPDATE example for a single table. IF this is a single-table update you do not have to qualify the fields because the only place they CAN be is in that single table. Therefore, your SQL statement could resemble, in part

strSQL = "UPDATE tblmastr SET Rtba = '" & strRtba & "', ...

Note also that if this were a JOIN update (not often recommended), you WOULD need to qualify where the fields were located since multiple tables would be involved in that case.

Private Sub cmdExecute_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strStatFig As String
Dim strRtba As String
Dim strNumberUpgradeAfter As String
Dim strDateUpgradeAfter As String

Dim strSQL As String

Set db = CurrentDb
Set rs = db.OpenRecordset("qryNamesWithNew", dbOpenSnapshot)

'Make sure we have records and then
'make sure we are at the first record
    If rs.RecordCount < 1 Then
        MsgBox "No records require an update"
        Set rs = Nothing
        Set db = Nothing
    Exit Sub
    End If

rs.MoveFirst

'We need to loop through all of the records
'that our query object found
    While rs.EOF = False
    
        strStatFig = rs![StatFig]
        strRtba = rs![Rtba]
        strNumberUpgradeAfter = rs![NumberUpgradeAfter]
        strDateUpgradeAfter = rs![DateUpgradeAfter]
        
        
        strSQL = "UPDATE tblmastr SET tblmastr.Rtba = '" & strRtba & "' WHERE ((tblmastr.StatFig)='" & strStatFig & "')"
        
        db.Execute strSQL, dbFailOnError
        
        rs.MoveNext
    
    Wend

rs.Close
Set rs = Nothing
Set db = Nothing

MsgBox "Complete"

End Sub

 

[Gasman]

Sorry @The_Doc_Man, but it did?

UPDATE Orders SET OrderAmount = OrderAmount * 1.1, Freight = Freight * 1.03  WHERE ShipCountry = 'UK';

 

[The_Doc_Man]

@Gasman - misread it. Must have been tired at the time. It happens. And if anything, I was knocking the article, not YOUR contribution.

 

[ebs17]

Numbers do not need anything

Caution. With decimal numbers, you must observe the regional setting for the decimal separator. If this is different from the dot (in German the comma is used), the number must be treated.

Str(DoubleVariable)