Criteria in Database Results

D

Dave

I have a form that searches 2 text boxes and displays the
information in datasheet 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?
 
J

Jim Buyens

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)
|/---------------------------------------------------
*----------------------------------------------------
 
D

Dave

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

Jim Buyens

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 said:
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 datasheet 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?
.

.
Click to expand...
.
Click to expand...
Click to expand...
 
D

Dave

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" <[email protected]> wrote in
message news: said:
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
Click to expand...
displays
the
information in datasheet 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
Click to expand...
state...I
want
it to search for all of Miami while passing the null
value. Any ideas?
.

.

.
Click to expand...
Click to expand...
.
Click to expand...
 
J

Jim Buyens

Sorry, I had an missing parentheses. The expression should be:

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

BTW, it's perfectly all right for you to point this out and I
don't mind correcting it. But if you ever plan to change
this page or create others like it, you should learn a bit
more about SQL.

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 said:
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" <[email protected]> wrote in
message news: said:
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
Click to expand...
displays
the
information in datasheet 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
Click to expand...
state...I
want
it to search for all of Miami while passing the null
value. Any ideas?
.

.

.
Click to expand...
.
Click to expand...
Click to expand...
 
D

Dave

Ugh, I totally missed that also...good news is it works
perfectly..thanks :)
-----Original Message-----
Sorry, I had an missing parentheses. The expression should be:

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

BTW, it's perfectly all right for you to point this out and I
don't mind correcting it. But if you ever plan to change
this page or create others like it, you should learn a bit
more about SQL.

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" <[email protected]> wrote in
message news: said:
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
Click to expand...
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,
Click to expand...
the
third
argument makes the IIf return true or false, depending
Click to expand...
on
whether the
visitor's value matches the value in the current
Click to expand...
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" <[email protected]> wrote in
Click to expand...
message news:<216501c47003$806f3100 [email protected]>...
I'm thinking I need an if, then, else
Click to expand...
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 datasheet 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
Click to expand...
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?
.

.

.

.
Click to expand...
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Link a text box based on drop down box with four options MS Word 365 0
Index Problem 2
WORD VBA Search and switch to advanced ... 1
Index Question 0
Prompt For Replace in Selection And More... 0
Possible to Combine reports to's to a single box? 0
Creating list from values that meet multiple criteria from other sheet 1
Need help modifying code 0

Top