Where clause containing variable

[vtj]

I am trying to create a program that cycles through a number of agencies that
have one or more items in a table. I need to pull each agency separately and
am trying to do that by setting a variable (var5) equal to the agency number
that I want to process on this pass through the program. It does not like
the syntax of the ‘= var5;’ part of the string. Is it possible to correct
the syntax or does this process need to be done a different way, maybe
rebuilding the string rst30 for each pass? I am using Access 2000.

rst30 = "SELECT [LoadTable Payroll Clear].[FISCAL_YR], [LoadTable Payroll
Clear].[AGENCY_NBR] FROM [LoadTable Payroll Clear] Where LoadTable Payroll
Clear.[Agency_nbr] = var5;"

 

[John Spencer]

If var5 is a number then

rst30 = "SELECT [LoadTable Payroll Clear].[FISCAL_YR], [LoadTable Payroll
Clear].[AGENCY_NBR] FROM [LoadTable Payroll Clear] Where LoadTable Payroll
Clear.[Agency_nbr] =" & var5 & ";"

If var5 is a string then
rst30 = "SELECT [LoadTable Payroll Clear].[FISCAL_YR], [LoadTable Payroll
Clear].[AGENCY_NBR] FROM [LoadTable Payroll Clear] Where LoadTable Payroll
Clear.[Agency_nbr] =" & chr(34) & var5 & Chr(34) & ";"

Since this is only a snippet of your code, I'm not sure what else to tell
you.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..