How do I create a user friendly query in Access?

[Guest]

I want to create a user friendly searchable database.

1) I know how to create simple queries, but how do I create queries in which
the user can choose from a variety of criteria, and they can all be AND or OR?

2) I assume I have to use forms as the user interface. How do I connect the
query to the form?

Thanks!

 

[John Vinson]

I want to create a user friendly searchable database.

1) I know how to create simple queries, but how do I create queries in which
the user can choose from a variety of criteria, and they can all be AND or OR?

Parameter queries are a good starting point. Rather than using a
literal value on the Criteria line use a forms reference such as

=[Forms]![frmCrit]![txtSearchFor]

You should have an unbound Form named frmCrit with a textbox named
txtSearchFor (this can also be a Combo Box or other type of control).
There can of course be several such criteria. On the Form you would
have a command button to open another Form (for onscreen display) or
Report (for printing) based on the query.

2) I assume I have to use forms as the user interface. How do I connect the
query to the form?

Use the Query as the Form's Record Source property. If you're building
the form from scratch, choose the desired Query rather than any table
name.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks, that works.

Could you elaborate on how I do multiple search criteria? In particular, in
the user interface forms, I will have multiple seach criteria, and the user
can choose to specify all or only some of them. If the user does not specify
one of the criteria, no entry will match the query. How do I solve this?

I want to create a user friendly searchable database.

1) I know how to create simple queries, but how do I create queries in which
the user can choose from a variety of criteria, and they can all be AND or OR?

Parameter queries are a good starting point. Rather than using a
literal value on the Criteria line use a forms reference such as

=[Forms]![frmCrit]![txtSearchFor]

You should have an unbound Form named frmCrit with a textbox named
txtSearchFor (this can also be a Combo Box or other type of control).
There can of course be several such criteria. On the Form you would
have a command button to open another Form (for onscreen display) or
Report (for printing) based on the query.

2) I assume I have to use forms as the user interface. How do I connect the
query to the form?

Use the Query as the Form's Record Source property. If you're building
the form from scratch, choose the desired Query rather than any table
name.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks, that works.

Could you elaborate on how I do multiple search criteria? In particular, in
the user interface forms, I will have multiple seach criteria, and the user
can choose to specify all or only some of them. If the user does not specify
one of the criteria, no entry will match the query. How do I solve this?

I want to create a user friendly searchable database.

1) I know how to create simple queries, but how do I create queries in which
the user can choose from a variety of criteria, and they can all be AND or OR?

Parameter queries are a good starting point. Rather than using a
literal value on the Criteria line use a forms reference such as

=[Forms]![frmCrit]![txtSearchFor]

You should have an unbound Form named frmCrit with a textbox named
txtSearchFor (this can also be a Combo Box or other type of control).
There can of course be several such criteria. On the Form you would
have a command button to open another Form (for onscreen display) or
Report (for printing) based on the query.

2) I assume I have to use forms as the user interface. How do I connect the
query to the form?

Use the Query as the Form's Record Source property. If you're building
the form from scratch, choose the desired Query rather than any table
name.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Rob Oldfield]

You can use the Nz function to turn the Null value returned from the form
into a "*" wildcard. But note that if you add a * wildcard onto a query it
gets automatically corrected to Like "*"

This is necessary for the wildcard to work, but if you're setting it up
dynamically then it won't get done automatically for you. So you would need
to use something like....

Like nz([forms]![FormName]![ControlName],"*") as your criterion.

Thanks, that works.

Could you elaborate on how I do multiple search criteria? In particular, in
the user interface forms, I will have multiple seach criteria, and the user
can choose to specify all or only some of them. If the user does not specify
one of the criteria, no entry will match the query. How do I solve this?

I want to create a user friendly searchable database.

1) I know how to create simple queries, but how do I create queries in which
the user can choose from a variety of criteria, and they can all be AND

or OR?

Parameter queries are a good starting point. Rather than using a
literal value on the Criteria line use a forms reference such as

=[Forms]![frmCrit]![txtSearchFor]

You should have an unbound Form named frmCrit with a textbox named
txtSearchFor (this can also be a Combo Box or other type of control).
There can of course be several such criteria. On the Form you would
have a command button to open another Form (for onscreen display) or
Report (for printing) based on the query.

2) I assume I have to use forms as the user interface. How do I connect the
query to the form?

Use the Query as the Form's Record Source property. If you're building
the form from scratch, choose the desired Query rather than any table
name.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Thanks, that works.

Could you elaborate on how I do multiple search criteria? In particular, in
the user interface forms, I will have multiple seach criteria, and the user
can choose to specify all or only some of them. If the user does not specify
one of the criteria, no entry will match the query. How do I solve this?

If it's just a few criteria - I'd say four or less, otherwise the SQL
gets too complicated - you can use a criterion on each field of

=[Forms]![frmCrit]![controlA] OR [Forms]![frmCrit]![controlA] IS NULL

When you go from design view to datasheet and back, you'll see that
JET has totally scrambled the query grid, but it usually still works.

The alternative is to put a command button on frmCrit which loops
through the controls on the form and builds up a SQL string, adding a
criterion for each non-NULL control.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

How about if I want to turn something other than Null into the "*" wildcard?
Say the entry <N/A> from a Combo Box?

Thanks!

You can use the Nz function to turn the Null value returned from the form
into a "*" wildcard. But note that if you add a * wildcard onto a query it
gets automatically corrected to Like "*"

This is necessary for the wildcard to work, but if you're setting it up
dynamically then it won't get done automatically for you. So you would need
to use something like....

Like nz([forms]![FormName]![ControlName],"*") as your criterion.

Thanks, that works.

Could you elaborate on how I do multiple search criteria? In particular, in
the user interface forms, I will have multiple seach criteria, and the user
can choose to specify all or only some of them. If the user does not specify
one of the criteria, no entry will match the query. How do I solve this?

On Sat, 27 Nov 2004 18:23:01 -0800, Joanna

I want to create a user friendly searchable database.

1) I know how to create simple queries, but how do I create queries in which
the user can choose from a variety of criteria, and they can all be AND or OR?

Parameter queries are a good starting point. Rather than using a
literal value on the Criteria line use a forms reference such as

=[Forms]![frmCrit]![txtSearchFor]

You should have an unbound Form named frmCrit with a textbox named
txtSearchFor (this can also be a Combo Box or other type of control).
There can of course be several such criteria. On the Form you would
have a command button to open another Form (for onscreen display) or
Report (for printing) based on the query.

2) I assume I have to use forms as the user interface. How do I connect the
query to the form?

Use the Query as the Form's Record Source property. If you're building
the form from scratch, choose the desired Query rather than any table
name.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

I will have more than four search criteria. And in each of them the user
should be able to specify more than one keyworkd...

Do you recommed any great book where I can learn how to do *fast*?

Thanks!

Thanks, that works.

Could you elaborate on how I do multiple search criteria? In particular, in
the user interface forms, I will have multiple seach criteria, and the user
can choose to specify all or only some of them. If the user does not specify
one of the criteria, no entry will match the query. How do I solve this?

If it's just a few criteria - I'd say four or less, otherwise the SQL
gets too complicated - you can use a criterion on each field of

=[Forms]![frmCrit]![controlA] OR [Forms]![frmCrit]![controlA] IS NULL

When you go from design view to datasheet and back, you'll see that
JET has totally scrambled the query grid, but it usually still works.

The alternative is to put a command button on frmCrit which loops
through the controls on the form and builds up a SQL string, adding a
criterion for each non-NULL control.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

I will have more than four search criteria. And in each of them the user
should be able to specify more than one keyworkd...

Do you recommed any great book where I can learn how to do *fast*?

Ummm... not really. There's rather a steep learning curve and you're
asking to do some pretty advanced stuff. If you'll send me your EMail
address *offline* (don't post it here or you'll get buried in SPAM,
unless you spamproof it) to me at jvinson <at> wysard of info <dot>
com (remove the blanks and make the obvious punctuation changes) I'll
send you some sample code for a search with a dozen fields; you should
be able to adapt it to your needs.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Rob Oldfield]

Easiest way would be to extend John's idea and add a....

Or Field="N/A"

.....in there as well.

(We're basically both suggesting the same thing to your original
question.... mine is just the way I've always done it.... but I suspect that
his way might be better.)

How about if I want to turn something other than Null into the "*" wildcard?
Say the entry <N/A> from a Combo Box?

Thanks!

You can use the Nz function to turn the Null value returned from the form
into a "*" wildcard. But note that if you add a * wildcard onto a query it
gets automatically corrected to Like "*"

This is necessary for the wildcard to work, but if you're setting it up
dynamically then it won't get done automatically for you. So you would need
to use something like....

Like nz([forms]![FormName]![ControlName],"*") as your criterion.

Thanks, that works.

Could you elaborate on how I do multiple search criteria? In

particular,
in

the user interface forms, I will have multiple seach criteria, and the user
can choose to specify all or only some of them. If the user does not specify
one of the criteria, no entry will match the query. How do I solve this?


:

On Sat, 27 Nov 2004 18:23:01 -0800, Joanna

I want to create a user friendly searchable database.

1) I know how to create simple queries, but how do I create queries

in
which

the user can choose from a variety of criteria, and they can all be

AND
or OR?

Parameter queries are a good starting point. Rather than using a
literal value on the Criteria line use a forms reference such as

=[Forms]![frmCrit]![txtSearchFor]

You should have an unbound Form named frmCrit with a textbox named
txtSearchFor (this can also be a Combo Box or other type of control).
There can of course be several such criteria. On the Form you would
have a command button to open another Form (for onscreen display) or
Report (for printing) based on the query.

2) I assume I have to use forms as the user interface. How do I connect the
query to the form?

Use the Query as the Form's Record Source property. If you're building
the form from scratch, choose the desired Query rather than any table
name.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Rob Oldfield]

Though for completeness, I should also add that you can use the IIF function
to turn an N/A into a *

Easiest way would be to extend John's idea and add a....

Or Field="N/A"

....in there as well.

(We're basically both suggesting the same thing to your original
question.... mine is just the way I've always done it.... but I suspect that
his way might be better.)

How about if I want to turn something other than Null into the "*" wildcard?
Say the entry <N/A> from a Combo Box?

Thanks!

query

it

would

need

to use something like....

Like nz([forms]![FormName]![ControlName],"*") as your criterion.

Thanks, that works.

Could you elaborate on how I do multiple search criteria? In particular,
in
the user interface forms, I will have multiple seach criteria, and the
user
can choose to specify all or only some of them. If the user does not
specify
one of the criteria, no entry will match the query. How do I solve this?


:

On Sat, 27 Nov 2004 18:23:01 -0800, Joanna

I want to create a user friendly searchable database.

1) I know how to create simple queries, but how do I create

queries

in

be

AND

or OR?

Parameter queries are a good starting point. Rather than using a
literal value on the Criteria line use a forms reference such as

=[Forms]![frmCrit]![txtSearchFor]

You should have an unbound Form named frmCrit with a textbox named
txtSearchFor (this can also be a Combo Box or other type of control).
There can of course be several such criteria. On the Form you would
have a command button to open another Form (for onscreen display) or
Report (for printing) based on the query.

2) I assume I have to use forms as the user interface. How do I
connect the
query to the form?

Use the Query as the Form's Record Source property. If you're building
the form from scratch, choose the desired Query rather than any table
name.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps