Solved Syntax error (1 Viewer)

PatAccess · Mar 17, 2021

Hello Guys,

My code block is meant to add comments to the current comments. Any idea why the "else" block would be red (Syntax error)?

Code:

Private Sub cmdUpdateComments_Click()
    Dim strInput As String
    Dim strComments As String
    Dim sqlUpdate As String
    Dim d As Date
    
    strComments = Me.Comments.Value
    strInput = InputBox("Please enter new comment", "Update Comments")
    d = Date
    
    If IsNull(strComments) Then
        sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]= '# & d & # & " - " & " & strInput & " '"
    Else
        sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]=' " & strComments & " & " " & # & d & # & " - " & " & strInput & " ' "
    End If
    
End Sub

Thank you

theDBguy · Mar 17, 2021

I think your quotes may be in the wrong places.

Gasman · Mar 17, 2021

Try Debug.Print sqlUpdate and see what is produced.?

PatAccess · Mar 17, 2021

Gasman said:
Try Debug.Print sqlUpdate and see what is produced.?

It says:
compile error:
Syntax error

It is not printing the Debug.print. I also tried to remove the Else block and place it under an "If NOT IsNull(variable) Then" but still the same error

theDBguy · Mar 17, 2021

PatAccess said:
It says:
compile error:
Syntax error

It is not printing the Debug.print. I also tried to remove the Else block and place it under an "If NOT IsNull(variable) Then" but still the same error

Hi. Although your Else block may be getting colored in red, your If block is not correct either. Double check your placement of the double quotes in your code.

Sent from phone...

plog · Mar 17, 2021

Code:

sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]=' " & strComments & " & " " & # & d & # & " - " & " & strInput & " ' "

You are building a string correct? You can do that with literals (things inside quote marks) and variables (things that contain data other than what they say on their face). When you compose a string made of both of them you must seperate them by an ampersand (&). So if something isn't surrounded by quotes its interperted as a variable.

In the code I posted above, do you really have a variable named #?

NauticalGent · Mar 17, 2021

I "think" PatAccess is under the impression it is needed when adding a date to a text field...

In which case:

SQL:

sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]= """ & d & " - " & strInput & """"

PatAccess · Mar 17, 2021

plog said:
Code:

sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]=' " & strComments & " & " " & # & d & # & " - " & " & strInput & " ' "

You are building a string correct? You can do that with literals (things inside quote marks) and variables (things that contain data other than what they say on their face). When you compose a string made of both of them you must seperate them by an ampersand (&). So if something isn't surrounded by quotes its interperted as a variable.

In the code I posted above, do you really have a variable named #?

I do not. d is a Date variable so I thought I have to surround it by #

PatAccess · Mar 17, 2021

NauticalGent said:
I "think" PatAccess is under the impression it is needed when adding a date to a text field...

In which case:

SQL:

sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]= """ & d & " - " & strInput & """"

Yes, that's what I thought. but what would I surround the variables with? I thought it was double quotes because shouldn't the query be:

Code:

sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]= '(String)'"

NauticalGent · Mar 17, 2021

PatAccess said:
I do not. d is a Date variable so I thought I have to surround it by #

Only when it actually part of the query/SQL statement...

NauticalGent · Mar 17, 2021

PatAccess said:
Yes, that's what I thought. but what would I surround the variables with? I thought it was double quotes because shouldn't the query be:

Code:

sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]= '(String)'"

As plog said, variables inside of quotes become literal strings: d = Date becomes "d" instead of 3/17/2021

Gasman · Mar 17, 2021

NauticalGent said:
As plog said, variables inside of quotes become literal strings: d = Date becomes "d" instead of 3/17/2021

If anything I would have thought the Format() option would be needed to convert the date to the format required AND make it a string for the comment concatenation?
I would have put the new comment into a separate variable, so I could debug.print it and then just set the Comment field to that variable in the SQL.?

Also wouldn't you need a WHERE statement in there as to which record are you updating?

NauticalGent · Mar 17, 2021

Gasman said:
Also wouldn't you need a WHERE statement in there as to which record are you updating?

Yes you would, but my feeble, non-multitasking brain can only handle one thing at a time...

PatAccess · Mar 17, 2021

Gasman said:
If anything I would have thought the Format() option would be needed to convert the date to the format required AND make it a string for the comment concatenation?
I would have put the new comment into a separate variable, so I could debug.print it and then just set the Comment field to that variable in the SQL.?

Also wouldn't you need a WHERE statement in there as to which record are you updating

I've placed the WHERE clause in it but something is still wrong. It is saying missing parameters for my strComments, strd, and strInput variables. I honestly don't know what I am doing wrong here
Here is what I have now

Code:

Private Sub cmdUpdateComments_Click()
    Dim strInput As String
    Dim strComments As String
    Dim sqlUpdate As String
    Dim strd As String
    Dim d As Date
    
    strComments = Me.Comments.Value
    strInput = InputBox("Please enter new comment", "Update Comments")
    d = Date
    strd = Format(d, "Short date")
    
    If IsNull(strComments) Then
        sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]= strd & " - " & strInput WHERE 'StateCorpLicID=" & Me.StateCorpLicID & "'"
    Else
        sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]= strComments & ' ' & strd & ' - ' & strInput WHERE 'StateCorpLicID=" & Me.StateCorpLicID & "'"
    End If
    Debug.Print sqlUpdate
    DoCmd.RunSQL sqlUpdate
End Sub

And here is what the Debug print is showing

Code:

UPDATE TblStateCorpLic SET [Comments]= strComments & ' ' & strd & ' - ' & strInput WHERE 'StateCorpLicID=54'

When I hover over strComments (in my immediate window), it is giving me the correct information. But when I hover over strd and strInput, they are both empty

I step through the code and
strComments holds the correct information
strd holds the correct information
strInput holds the correct information

However, the problem seems to happen within the SQL statement because sqlUpdate is empty?

Gasman · Mar 17, 2021

NauticalGent said:
Yes you would, but my feeble, non-multitasking brain can only handle one thing at a time...

Sorry John,
That was not directed at you, but the O/P

theDBguy · Mar 17, 2021

PatAccess said:
And here is what the Debug print is showing

And that looks like an invalid SQL statement. If you copy and paste it into the Query Designer, it might give you an error.

theDBguy · Mar 17, 2021

PatAccess said:
If IsNull(strComments) Then

Also, your code will probably never reach the True block of your If statement, because if Me.Comments.Value is truly Null, then this line would produce an error.

PatAccess said:
strComments = Me.Comments.Value

Gasman · Mar 17, 2021

PatAccess said:
I've placed the WHERE clause in it but something is still wrong. It is saying missing parameters for my strComments, strd, and strInput variables. I honestly don't know what I am doing wrong here
Here is what I have now

Code:

Private Sub cmdUpdateComments_Click() Dim strInput As String Dim strComments As String Dim sqlUpdate As String Dim strd As String Dim d As Date strComments = Me.Comments.Value strInput = InputBox("Please enter new comment", "Update Comments") d = Date strd = Format(d, "Short date") If IsNull(strComments) Then sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]= strd & " - " & strInput WHERE 'StateCorpLicID=" & Me.StateCorpLicID & "'" Else sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]= strComments & ' ' & strd & ' - ' & strInput WHERE 'StateCorpLicID=" & Me.StateCorpLicID & "'" End If Debug.Print sqlUpdate DoCmd.RunSQL sqlUpdate End Sub

And here is what the Debug print is showing

Code:

UPDATE TblStateCorpLic SET [Comments]= strComments & ' ' & strd & ' - ' & strInput WHERE 'StateCorpLicID=54'

When I hover over strComments (in my immediate window), it is giving me the correct information. But when I hover over strd and strInput, they are both empty

I step through the code and
strComments holds the correct information
strd holds the correct information
strInput holds the correct information

However, the problem seems to happen within the SQL statement because sqlUpdate is empty?

You concatenation is off. Try

Code:

        sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]= '" & strd & "' - '" & strInput & "' WHERE StateCorpLicID=" & Me.StateCorpLicID

PatAccess · Mar 17, 2021

Gasman said:
You concatenation is off. Try

Code:

sqlUpdate = "UPDATE TblStateCorpLic SET [Comments]= '" & strd & "' - '" & strInput & "' WHERE StateCorpLicID=" & Me.StateCorpLicID

Thank you. My concatenation was indeed off. It works.

PatAccess · Mar 17, 2021

Thank you so much guys.

Solved Syntax error (1 Viewer)

Registered User.

I’m here to help

Enthusiastic Amateur

Registered User.

I’m here to help

Banishment Pending

Ignore List Poster Boy

Registered User.

Registered User.

Ignore List Poster Boy

Ignore List Poster Boy

Enthusiastic Amateur

Ignore List Poster Boy

Registered User.

Enthusiastic Amateur

I’m here to help

I’m here to help

Enthusiastic Amateur

Registered User.

Registered User.

Similar threads

Users who are viewing this thread