Re: Criteria in Database Results

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Jim Buyens (news_at_interlacken.com)
Date: 07/22/04 

Date: 22 Jul 2004 15:04:10 -0700

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

Quantcast