re: Criteria in Database Results

Tech-Archive recommends: Speed Up your PC by fixing your registry

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

Date: Thu, 22 Jul 2004 08:49:41 -0700

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?
>>>.
>>>
>>.
>>
>.
>

Quantcast