Passthrough Query Criteria Syntax (1 Viewer)

[llkhoutx]

I'm developing an Access FE SQL Server BE database and have the following passthougfh query which generates and error:

UPDATE top (100) x
SET CARRIER_NAME = LTRIM(RTRIM(CARRIER_NAME)) 
WHERE report_state = 'il' AND report_year=2012 AND report_month=1 and
schedule_code = forms!switchboard!Y

and get the following error:

ODBC--call failed.
[Microsoft][ODBC SQL Server driver][SQL Server]Inbcorrect syntax near "!". (#102)

The pasthrough query runs correctly when the " and schedule_code = forms!switchboard!Y" is omitted.

I'm attempting to use a value on a form which is IN("A","B","v") and changes from time to time. I don't want multiple queries with the various permuytations of the criteria.

Suggestions?

Is there some way to do this.

 

[jdraw]

I am not an sql server user. But passthrus expect the syntax of the "linked to" system so to speak. So I think you need to get the value from your
forms!switchboard!Y and insert it into your sql string.

Just my $.02

 

[llkhoutx]

Thanks JDraw, you're correct, the executable syntax is

UPDATE top (100) x 
SET CARRIER_NAME = LTRIM(RTRIM(CARRIER_NAME))  
WHERE report_state = 'il' AND report_year=2012 AND report_month=1 and 
schedule_code ='03300'

I have 80 quiries that I have to dynamically modify the WHERE clause for each new period. I now just whiz through them.

 

[Galaxiom]

Probably too late now but you could run a loop and do a Replace in the SQL property of Each QueryDef.