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

 

[Armen Stein]

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

I just tried it again and it worked for me. You can also find the
downloads by going to www.JStreetTech.com, then clicking Custom
Programming, then Downloads.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[VJ]

Ok, I did that but I am still having problems with my database. My Where
statement is quite long and I keep getting errors in VBA with it. I set up a
SQL to VBA converter which I found online so that was helpful but I am still
trying to figure this out. Any advice would be great. My brain hurts from
this which is why i am having trouble now.

 

[Armen Stein]

My Where
statement is quite long and I keep getting errors in VBA with it. I set up a
SQL to VBA converter which I found online so that was helpful but I am still
trying to figure this out. Any advice would be great. My brain hurts from
this which is why i am having trouble now.

Building a long Where clause in code can be tricky. A few hints:

- build up the statement one piece at a time, by commenting out
portions of your code. Get each piece working before adding more.

- use a breakpoint to stop your code after the Where clause has been
built and dump it into the immediate window. Then copy & paste it
into the SQL view of an Access query. This will help you find syntax
errors.

- pay close attention to all those quotation marks, including the
double quotes that collapse. If you have string values, then you need
to wrap the variable text in quotes:

strWhereClause = 'WHERE MyField = ''' & MyFormControl & ''''

- pay close attention to spaces - make sure that as your statement is
built up, that you have spaces between the words. It's okay to have
more than one, Access doesn't care.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[VJ]

thanks i will get back to you once i try it.

Building a long Where clause in code can be tricky. A few hints:

- build up the statement one piece at a time, by commenting out
portions of your code. Get each piece working before adding more.

- use a breakpoint to stop your code after the Where clause has been
built and dump it into the immediate window. Then copy & paste it
into the SQL view of an Access query. This will help you find syntax
errors.

- pay close attention to all those quotation marks, including the
double quotes that collapse. If you have string values, then you need
to wrap the variable text in quotes:

strWhereClause = 'WHERE MyField = ''' & MyFormControl & ''''

- pay close attention to spaces - make sure that as your statement is
built up, that you have spaces between the words. It's okay to have
more than one, Access doesn't care.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com