Using a numeric variable in an SQL String

[Freddie6]

I have a report in Access XP that is based on an SQL statement. The variable is declared as a public variable and assigned with a record ID number when the object is double-clicked. The report is then opened and the SQL statement is executed except it prompts me to input the record number rather than reading the public variable with the value assigned to it. The public variable "CLM" is declared as Long. Because SQL is read as string I have attempted to convert this to a CStr(CLM). I have feeling it has something to do with the quotes...but everything I find on the web is set as follows: "SELECT * FROM tblEmployees WHERE RecID = " & CStr(CLM"). I have copied this directly from the web pages into my SQL statement and it does not work! It looks to me like there are not enough quotes since there are not pairs. Any ideas?

 

[Darrylles]

Hi,

As with any argument passed as a parameter, if the parameter requires brackets, the parameter requires bracketS not just the left bracket.

cstr expects (CLM) not (CLM
(as your code is passing.)

try :

"SELECT * FROM tblEmployees WHERE RecID = " & CStr(CLM) & ";"

Maybe the terminating ; will also help (although I've never been sure about this with MS Access SQL).

If not, then try:

"SELECT * FROM tblEmployees WHERE RecID = " & CStr(CLM)

Make sure that CLM variable is 'dim'med as the same data type as the record ID type (I assume Long, but it may be anything that you have defined).

ATB

Darrylle








Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[NorthNone]

It may be easier than you are making it out to be.
Try this:
"SELECT * FROM tblEmployees WHERE RecID = " & CLM & ""
HTH
JSouth


---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.