Re: Criteria in Database Results

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

Date: Fri, 23 Jul 2004 06:37:25 -0700

I copied and pasted the exact code into my custom query on
database results and I get the following error when trying
to Verify Query:

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

SELECT * FROM shoppers WHERE IIf(('1'=''), True, appcity
= '2') AND IIf('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
(comma) in query expression 'IIf(('1'=''), True, appcity
= '2') AND IIf('3'=''), True, appstate = '4')'.

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

Any ideas?
>-----Original Message-----
>I think you need:
>
>SELECT * FROM shoppers
>WHERE IIf(('::appcity::'=''), True, appcity
= '::appcity::')
>AND IIf('::appstate::'=''), True, appstate
= '::appstate::')
>
>The IIf fucntion takes three arguments:
>
>o A comparison
>o A value to return if the comparison is true
>o A value to return if the comparison is false.
>
>In IIf(('::appcity::'=''), True, appcity = '::appcity::'),
>the comparison is ('::appcity::'=''), which tests whether
the appcity
>field that the visitor specified is empty.
>
>If the appcity field from the visitor *is* empty, the
second argument
>makes the IIf always return true.
>
>If the appcity field from the visitor contains data, the
third
>argument makes the IIf return true or false, depending on
whether the
>visitor's value matches the value in the current database
record.
>
>Note that in ('::appcity::'=''), '' is a double
apostrophe (''), and
>not a quote (").
>
>Sorry, but Microsoft Access doesn't support
If...Then...Else...End If.
>It only supports IIf.
>
>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)
>|/---------------------------------------------------
>*----------------------------------------------------
>
>"Dave" <anonymous@discussions.microsoft.com> wrote in
message news:<216501c47003$806f3100$a401280a@phx.gbl>...
>> I'm thinking I need an if, then, else statement...but
am
>> unsure of the syntax. It would be something like this:
>>
>> SELECT * FROM shoppers WHERE
>>
>> If (appcity='') THEN
>>
>> ELSE (appcity=::appcity::)
>>
>> However, I am unsure on how to do nothing when
appcity=''.
>> >-----Original Message-----
>> >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?
>> >>>.
>> >>>
>> >>.
>> >>
>> >.
>> >
>.
>