SQL Update Query Issue

[BrettM]

I have an SQL update query that is performing badly and I was wondering if anyone can give me a pointer please? Is there any issues with using a public variable within an SQL statement?

The following code works perfectly if I remove the reference to PublicVar. Setting a break point I can see that PublicVar does contain the general text string that I desire so I don't believe it is a date format issue etc.

Public Sub UpdateExpired()
    Dim SQL As String
    PublicVar = Format(Date, "dd/mm/yy") & " Voucher marked as expired today."
 
    SQL = "UPDATE Vouchers" & _
    "SET Vouchers.Expired = -1, Vouchers.Notes = Vouchers.Notes + Chr$(13) + Chr$(10) & PublicVar" & _
    "WHERE (((Vouchers.Expired)=0) AND ((Date())>[Vouchers]![ExpDate]));"
 
    CurrentDb.Execute SQL
    PublicVar = ""
 
End Sub

I have split the "SQL=" code for readibility purposes here - it is all one line in my system. The error I am getting is Run-time error 3061 - Too few parameters. Expected 1.

Nothing I have searched on gives me any clue as to why it is happening here. Your assistance would be greatly appreciated.

Regards Brett

 

[MSAccessRookie]

I think you might be missing a couple of spaces. I am not positive, but I think the SQL statement will be parsed as follows:

UPDATE VouchersSET Vouchers.Expired = -1, Vouchers.Notes = Vouchers.Notes + Chr$(13) + Chr$(10) & PublicVarWHERE (((Vouchers.Expired)=0) AND ((Date())>[Vouchers]![ExpDate]));

Try changing it to:

SQL = "UPDATE Vouchers " & _
"SET Vouchers.Expired = -1, Vouchers.Notes = Vouchers.Notes + Chr$(13) + Chr$(10) & PublicVar " & _
"WHERE (((Vouchers.Expired)=0) AND ((Date())>[Vouchers]![ExpDate]));"

I have an SQL update query that is performing badly and I was wondering if anyone can give me a pointer please? Is there any issues with using a public variable within an SQL statement?

The following code works perfectly if I remove the reference to PublicVar. Setting a break point I can see that PublicVar does contain the general text string that I desire so I don't believe it is a date format issue etc.

Public Sub UpdateExpired()
    Dim SQL As String
    PublicVar = Format(Date, "dd/mm/yy") & " Voucher marked as expired today."
 
    SQL = "UPDATE Vouchers" & _
    "SET Vouchers.Expired = -1, Vouchers.Notes = Vouchers.Notes + Chr$(13) + Chr$(10) & PublicVar" & _
    "WHERE (((Vouchers.Expired)=0) AND ((Date())>[Vouchers]![ExpDate]));"
 
    CurrentDb.Execute SQL
    PublicVar = ""
 
End Sub

I have split the "SQL=" code for readibility purposes here - it is all one line in my system. The error I am getting is Run-time error 3061 - Too few parameters. Expected 1.

Nothing I have searched on gives me any clue as to why it is happening here. Your assistance would be greatly appreciated.

Regards Brett

 

[pbaldy]

You also need to get PublicVar outside the quotes so it can be evaluated.

 

[MSAccessRookie]

You also need to get PublicVar outside the quotes so it can be evaluated.

So that would be something like this then?

SQL = "UPDATE Vouchers " & _
"SET Vouchers.Expired = -1, Vouchers.Notes = Vouchers.Notes + Chr$(13) + Chr$(10)" & PublicVar & { Possible need for a <SPACE> between PublicVar and WHERE }_
"WHERE (((Vouchers.Expired)=0) AND ((Date())>[Vouchers]![ExpDate]));"

 

[pbaldy]

Yes, but you were also correct about the space. It would have caused a problem.

 

[BrettM]

Thanks for the input guys.

The spaces were not actually missing from my statement however as I was using only one line I thought it would make it easier to read if I quickly edited it for display. Kinda forgot that you would probably cut & paste it to check the syntax. Whoops.

The answer went just a touch deeper than first envisioned. As the suggested code also didn't work - but gave a different error - I thought of the full syntax and realised that strings need to be surrounded by quote marks. The easiest way is to inject them into PublicVar so here is my solution...

Public Sub UpdateExpired()
    Dim SQL As String
    PublicVar = Chr(39) + Chr$(13) + Chr$(10) + Format(Date, "dd/mm/yy") & " Voucher marked as expired today." + Chr(39)
 
    SQL = "UPDATE Vouchers " & _
    "SET Vouchers.Expired = -1, Vouchers.Notes = Vouchers.Notes & " & PublicVar & _
    " WHERE (((Vouchers.Expired)=0) AND ((Date())>[Vouchers]![ExpDate]));"
    CurrentDb.Execute SQL
    PublicVar = ""
 
End Sub

Note that the spacing syntax is correct now .

Chr(39) before and after the string contents works like a charm.

Thanks again for your assistance MSAccessRookie and pbaldy. Without your lead I would still be following the wrong thread.

Regards Brett