re: Criteria in Database Results

From: Dave (anonymous_at_discussions.microsoft.com)
Date: 07/22/04 

Date: Thu, 22 Jul 2004 06:58:20 -0700

I am encountering an error while trying this custom query.
The code I have is this:

SELECT * FROM shoppers WHERE If(('::appcity::'="), True,
appcity = '::appcity::') AND If ('::appstate::'="), True,
appstate = ::'appstate::')

shoppers is the table name
appcity is the field name on the web page and the table's
field, same for appstate

this code produces the following error:

Server error: Unable to retrieve schema information from
the query:

SELECT * FROM shoppers WHERE If(('1[="), True, appcity
= '2') AND If ('3'="), True, appstate = 4')

against a database using the connection string

DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=URL=fpdb/MSU.mdb.

The following error message comes from the database driver
software; it may appear in a different language depending
on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query expression 'If(('1[="), True,
appcity = '2') AND If ('3'="), True, appstate = 4')'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)

Any ideas?

>-----Original Message-----
>You have to use a custom query with a WHERE clause that
>looks like this:
>
>WHERE IIf(('::city::'=''),True, city='::city::')
> AND IIf(('::state::'=''),True, state='::state::')
>
>assuming that your database fields and your form fields
>are named city and state.
>
>Jim Buyens
>Microsoft FrontPage MVP
>http://www.interlacken.com
>Author of:
>*----------------------------------------------------
>|\---------------------------------------------------
>|| Microsoft Office FrontPage 2003 Inside Out
>||---------------------------------------------------
>|| Web Database Development Step by Step .NET Edition
>|| Microsoft FrontPage Version 2002 Inside Out
>|| Faster Smarter Beginning Programming
>|| (All from Microsoft Press)
>|/---------------------------------------------------
>*----------------------------------------------------
>
>
>
>>-----Original Message-----
>>I have a form that searches 2 text boxes and displays
>the
>>information in data*** view below it once you hit
>>submit. It works great for the criteria "or",
>and "and".
>>However, I would like to have it set up as "and"
>>and "null". Basically, I want this examples to happen:
>>
>>Text Box Name 1=city
>>Text Box Name 2=state
>>
>>Search 1:
>>1=Miami
>>2=Florida
>>
>>Prodoces 3 records
>>
>>Search 2:
>>1=Miami
>>2=(null)
>>
>>Produces 3 records
>>
>>Search 3:
>>1=(null)
>>2=Florida
>>
>>Produces 15 records
>>
>>Basically, I want the "and" to work as it should and
>does;
>>however, when one of the fields is left blank, I don't
>>want it to search for, say, Miami and blank state...I
>want
>>it to search for all of Miami while passing the null
>>value. Any ideas?
>>.
>>
>.
>