Pass Through Query Nightmare (Access 2K --> SQL Server 2000)

[ILCSP]

Hello again, I'm still in the process of changing ms access queries to
Pass through queries. We're using a SQL Server 2000 database with an
Access 2000 DB as our front end.

I trying to convert this ms access query to a pass through and I'm
having quite a hard time doing it, so I hope you guys can help me. I
have a clients form and in this form there are 2 combo boxes where a
user can select a client's Type and Status. One for each. However, if
they don't need to choose anything, that field should be Null. Here's
the access query:

SELECT tblClients.Number, tblClients.Type
FROM tblClients
WHERE (((tblClients.Type)=[Forms]![frmClients]![CLType])
AND
((IIf(IsNull([Forms]![frmClients]![CLStatus]),((([tblClients].[Status])
Is
Null)),((([tblClients].[Status])=[Forms]![frmClients]![CLstatus]))))<>False))

OR
(((IIf(IsNull([Forms]![frmClients]![CLStatus]),((([tblClients].[Status])
Is
Null)),((([tblClients].[Status])=[Forms]![frmClients]![CLstatus]))))<>False)
AND (([Forms]![frmClients]![CLtype]) Is Null))

The IIF part in the criteria AND and OR is the same. Since I have to
check the value of the boxes first and the IS Null part cannot be equal
to False, I'm stuck. I know I can't use the boxes values all the way
through because SQL 2000 will not understand them, so I guess I have to
come up with something first before activating the pass through query.
Any help would be extremely appreciated.

Thanks.

JR.

[Lyle Fairfield]

I think this has been explained to you previously by someone more
familiar with passthrough queries than I, but here's the bottom line
(assuming I'm right; someone will tell us if not);
1. A PassThrough query must be written in the dialect of the database
server to which it is being passed, in this case in the Transact-SQL
[T-SQL] dialect of MS-SQL;
2. The PTQ must be a pure string; T-SQL will know absolutely nothing
about your Forms or Access; you may get away with something like "WHERE
Sex = " & txtSex.Value & "blah blah" but you will not get away with
"WHERE Sex = txtSex.Value".
3. If I had to do this thing I would put in the form's module a sub or
function that created that string, accounting for whatever values might
appear in boxes, (including no values at all, or nulls) and I would
debug.print the string. Then I would test it in the MS-SQL DB. Then I
would write code to test it before I sent it; When I got it right, I'd
use it.

[ILCSP]

Hello Lyle. I just found the solution and in a way it's what you're
saying. I'm first checking the value of the boxes and according to
them, I build the SQL string. I'm using a Select Case statement where
it checks whether the Status is Null, If it is Null then I check
whether the Type is Null, or else and then I do the same when the
Status is Else.

After I checked both values the appropiate SQL string is sent to the
SQL server and I get what I need.

Thanks for your input. I greatly appreciate it.

JR.
Lyle Fairfield wrote:

I think this has been explained to you previously by someone more
familiar with passthrough queries than I, but here's the bottom line
(assuming I'm right; someone will tell us if not);
1. A PassThrough query must be written in the dialect of the database
server to which it is being passed, in this case in the Transact-SQL
[T-SQL] dialect of MS-SQL;
2. The PTQ must be a pure string; T-SQL will know absolutely nothing
about your Forms or Access; you may get away with something like "WHERE
Sex = " & txtSex.Value & "blah blah" but you will not get away with
"WHERE Sex = txtSex.Value".
3. If I had to do this thing I would put in the form's module a sub or
function that created that string, accounting for whatever values might
appear in boxes, (including no values at all, or nulls) and I would
debug.print the string. Then I would test it in the MS-SQL DB. Then I
would write code to test it before I sent it; When I got it right, I'd
use it.