to Query or VB for pulling criteria from an unbound field

[VJ]

Hi,

I am creating a database which will be used to look up vendor information
and when I create a search form i am using unbound fields for he user to
input whta they are looking for. The query pulls from these fields.
[form]![Vendor]![City]
My problem is with the query given so many Or statements I am having data
being pulled that I am not asking for.
Ex. I want to pull hotels in Las vegas but i get all the hotels in the
database to come up. I am pulling info based on 25 different criteria.
Should I use a big If then Else statement or what? Please help

Thanks in advance for your help.

 

[Armen Stein]

I am creating a database which will be used to look up vendor information
and when I create a search form i am using unbound fields for he user to
input whta they are looking for. The query pulls from these fields.
[form]![Vendor]![City]
My problem is with the query given so many Or statements I am having data
being pulled that I am not asking for.
Ex. I want to pull hotels in Las vegas but i get all the hotels in the
database to come up. I am pulling info based on 25 different criteria.
Should I use a big If then Else statement or what?

For a query with criteria this complex, it would be better to build
your own Where clause instead of referencing all those form fields in
your query. Then you can build (and test) exactly what you want to
do.

For an example of building a SQL Where clause in VBA code, we have
some techniques in the download Report Selection Techniques at
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp. It's
for a report rather than a form, but the technique to build up the SQL
is the same. You can also Google the words:
Access build SQL Where clause
for more ideas.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[VJ]

Thanks for the feedback.

I am creating a database which will be used to look up vendor information
and when I create a search form i am using unbound fields for he user to
input whta they are looking for. The query pulls from these fields.
[form]![Vendor]![City]
My problem is with the query given so many Or statements I am having data
being pulled that I am not asking for.
Ex. I want to pull hotels in Las vegas but i get all the hotels in the
database to come up. I am pulling info based on 25 different criteria.
Should I use a big If then Else statement or what?

For a query with criteria this complex, it would be better to build
your own Where clause instead of referencing all those form fields in
your query. Then you can build (and test) exactly what you want to
do.

For an example of building a SQL Where clause in VBA code, we have
some techniques in the download Report Selection Techniques at
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp. It's
for a report rather than a form, but the technique to build up the SQL
is the same. You can also Google the words:
Access build SQL Where clause
for more ideas.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[VJ]

The link did not work actually but i will do a search also. Thanks again.

I am creating a database which will be used to look up vendor information
and when I create a search form i am using unbound fields for he user to
input whta they are looking for. The query pulls from these fields.
[form]![Vendor]![City]
My problem is with the query given so many Or statements I am having data
being pulled that I am not asking for.
Ex. I want to pull hotels in Las vegas but i get all the hotels in the
database to come up. I am pulling info based on 25 different criteria.
Should I use a big If then Else statement or what?

For a query with criteria this complex, it would be better to build
your own Where clause instead of referencing all those form fields in
your query. Then you can build (and test) exactly what you want to
do.

For an example of building a SQL Where clause in VBA code, we have
some techniques in the download Report Selection Techniques at
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp. It's
for a report rather than a form, but the technique to build up the SQL
is the same. You can also Google the words:
Access build SQL Where clause
for more ideas.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com