Incorrect where clause 2

[Russ1005]

I'm getting a syntax error on the following select. Could someone tell me what is wrong?

Thanks,
-Russ

SELECT customer_claims_header.*
FROM customer_claims_header
WHERE ([forms]![casedatabaseform]![csr] <> 'SMITH') OR
(([forms]![casedatabaseform]![csr] = 'SMITH') AND (case_status <> N'Open') AND (csr_status <> N'Pending'))

 

[PHV]

The errors are here N'Open' and here N'Pending'

What is the meaning of this N ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Russ1005]

Funny, I have the same question about the N. The N was present in a query I copied from that is working.

I eliminated the N's and I'm still getting a syntax error.

 

[PHV]

What is the data type of case_status in customer_claims_header ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Russ1005]

nvarchar 20

 

[PHV]

And this ?
PARAMETERS [forms]![casedatabaseform]![csr] TEXT(255);
SELECT customer_claims_header.*
FROM customer_claims_header
WHERE ([forms]![casedatabaseform]![csr] <> 'SMITH') OR
(([forms]![casedatabaseform]![csr] = 'SMITH') AND (case_status <> 'Open') AND (csr_status <> 'Pending'))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Russ1005]

Sorry, I'm lost. Do you want me to include the parameters statements? what is the meaning of TEXT(255)? BTW, this select statement is going in the record source in case that makes a difference.

 

[PHV]

this select statement is going in the record source
???
Can you please elaborate the context ?
You are building a SQL string you want to execute with either DAO or ADO ?
Then try this:
strSQL = "SELECT * FROM customer_claims_header"
If Forms!casedatabaseform!csr = 'SMITH' Then
strSQL = strSQL & " WHERE case_status<>'Open' AND csr_status<>'Pending'"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Russ1005]

I added the parameters statement and still get a syntax error.

 

[Russ1005]

The select statement is going in the the record source property of the casedatabaseform form (Access 2003).

I can't use the "strSQL =" in that case, can I? Am I making any sense?

 

[PHV]

Forms!casedatabaseform.RowSource = strSQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLSister]

Is this by chance a passthrough query to SQL Server?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Russ1005]

The database is SQL Server. I'm not sure what passthrough means. The former record source property was set up visually up by hitting the elipses on the record source line on the data tab of the casedatabaseform property sheet.

 

[SQLSister]

A paasstrough query is one which bypasses the Access jet engine and sends the query directly to SQL Server. AS such it must match SQL server's syntax which your query does not becasue you can't send the values from a form that way to SQL Server, but you can if you are using an Access query against a SQL Server linked table. So I was wondering if that was one of the reasons it was not working.

And just for everyone's edification, the N's in the original query are part of SQL Server syntax when querying nvarchar fields.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Russ1005]

Thanks for the definition. I inherited this pre-existing project and am an Access newbie. Do the N's give a clue that it is passthrough? If not, how do I determine if the project is using linked tables or passthrough? Can you switch back and forth from passthrough to Jet or do you have to choose which method and stick with it throughout the app?

What I'm trying to do is modify the where clause depending on the user name. Or possibly prompt the user to select a "mode" to use in the app then add a where clause depending on the answer to the "mode" selected.

 

[Russ1005]

The original query was:

SELECT [customer_claims_header].*
FROM [customer_claims_header]

I added the form stuff later.

 

[SQLSister]

If you look at the query list (in detail mode) there is a column that tells you the type of query. A passthrough query will be listed as such.

Sure you can mix linked tabled and passthrough queries. I've used passthrough queries when I want to improve performance by doing all the work on the back end. But I'm really a SQL Server programmer anyway. WE just havea couple of legacy apps I maintain that use Access front ends.

To give you an example of how a pass-through can improcve performance, I have one which took several minutes to run in Access and we converted it to a stored procedure and run it through a passthrough query and it takes seconds. And it is 18 pages of SQL code that is all calculations dependent on other calculations. A thoroughly nasty thing that I cringe whenever I have to change it (like I have to do Monday, darn!).





Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Russ1005]

Thanks for the info. Sounds like Monday is going to be fun.

None of the saved queries were of type passthrough.