Ability to leave parts of form blank, thereby selecting all.

G

Guest

I want to give my users a form where they will give some information and a
query will be run based on that. However, I want this to be a bit more
dynamic than just a simply query.

Let me try to explain in more detail.

One of the forms in question will run a query that pulls all training
classes an employee has had. The user would select the employee name and
search by that. However, I also want to give the user several other options
that they either can choose to use or not. For example, I would want to give
them a place to also enter the instructor name. So, they could choose the
employee Peter Venkman and the instructor Bruce Wayne. This would pull up any
course Dr. Venkman took that was taught by Bruce Wayne. On the other hand,
the user could choose the employee Peter Venkman and leave the instructor
name blank, and this would in turn pull up ALL of Dr. Venkman's records no
matter who was the instructor. Is there any way to do this? I think I may
have asked a similar question, but I haven't gotten an answer yet on whether
or not this is possible, and if it is how to do it. Thanks.

--
Have a nice day!

~Paul

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''''''''
ptaylor
 
D

Douglas J. Steele

I'm assuming that they're selecting the names from combo boxes?

Rather than having

[Forms]![NameOfForm]![NameOfCombo] as the criteria, use

[Forms]![NameOfForm]![NameOfCombo] OR [Forms]![NameOfForm]![NameOfCombo] IS
NULL
 
G

Guest

Awesome! Thank you! That worked.

A quick follow-up question: The same doesn't seem to be working for the date
range. I want to give my users the ability to enter a date range. I give them
a start date and an end date, then the criteria in the date field of the
query searches for all records within that date range. In other words, the
criteria reads as follows:

Between [Forms]![ByInstructFrm]![StartDate] And
[Forms]![ByInstructFrm]![EndDate]

How could I also allow for the user to choose to leave the start and end
date blank, thereby getting all records, and not just a specific range. If I
try just leaving the dates blank, it gives me nothing instead of all records.
Your suggestion above doesn't seem to be working in the case of the date.

--
Have a nice day!

~Paul

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''''''''
ptaylor



Douglas J. Steele said:
I'm assuming that they're selecting the names from combo boxes?

Rather than having

[Forms]![NameOfForm]![NameOfCombo] as the criteria, use

[Forms]![NameOfForm]![NameOfCombo] OR [Forms]![NameOfForm]![NameOfCombo] IS
NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paul (TRX) said:
I want to give my users a form where they will give some information and a
query will be run based on that. However, I want this to be a bit more
dynamic than just a simply query.

Let me try to explain in more detail.

One of the forms in question will run a query that pulls all training
classes an employee has had. The user would select the employee name and
search by that. However, I also want to give the user several other
options
that they either can choose to use or not. For example, I would want to
give
them a place to also enter the instructor name. So, they could choose the
employee Peter Venkman and the instructor Bruce Wayne. This would pull up
any
course Dr. Venkman took that was taught by Bruce Wayne. On the other hand,
the user could choose the employee Peter Venkman and leave the instructor
name blank, and this would in turn pull up ALL of Dr. Venkman's records no
matter who was the instructor. Is there any way to do this? I think I may
have asked a similar question, but I haven't gotten an answer yet on
whether
or not this is possible, and if it is how to do it. Thanks.

--
Have a nice day!

~Paul

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''''''''
ptaylor
 
D

Douglas J. Steele

Simplest way would be

Between Nz([Forms]![ByInstructFrm]![StartDate], #1/1/100#) And
Nz([Forms]![ByInstructFrm]![EndDate], #12/31/9999#)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Paul (TRX) said:
Awesome! Thank you! That worked.

A quick follow-up question: The same doesn't seem to be working for the
date
range. I want to give my users the ability to enter a date range. I give
them
a start date and an end date, then the criteria in the date field of the
query searches for all records within that date range. In other words, the
criteria reads as follows:

Between [Forms]![ByInstructFrm]![StartDate] And
[Forms]![ByInstructFrm]![EndDate]

How could I also allow for the user to choose to leave the start and end
date blank, thereby getting all records, and not just a specific range. If
I
try just leaving the dates blank, it gives me nothing instead of all
records.
Your suggestion above doesn't seem to be working in the case of the date.

--
Have a nice day!

~Paul

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''''''''
ptaylor



Douglas J. Steele said:
I'm assuming that they're selecting the names from combo boxes?

Rather than having

[Forms]![NameOfForm]![NameOfCombo] as the criteria, use

[Forms]![NameOfForm]![NameOfCombo] OR [Forms]![NameOfForm]![NameOfCombo]
IS
NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paul (TRX) said:
I want to give my users a form where they will give some information and
a
query will be run based on that. However, I want this to be a bit more
dynamic than just a simply query.

Let me try to explain in more detail.

One of the forms in question will run a query that pulls all training
classes an employee has had. The user would select the employee name
and
search by that. However, I also want to give the user several other
options
that they either can choose to use or not. For example, I would want to
give
them a place to also enter the instructor name. So, they could choose
the
employee Peter Venkman and the instructor Bruce Wayne. This would pull
up
any
course Dr. Venkman took that was taught by Bruce Wayne. On the other
hand,
the user could choose the employee Peter Venkman and leave the
instructor
name blank, and this would in turn pull up ALL of Dr. Venkman's records
no
matter who was the instructor. Is there any way to do this? I think I
may
have asked a similar question, but I haven't gotten an answer yet on
whether
or not this is possible, and if it is how to do it. Thanks.

--
Have a nice day!

~Paul

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''''''''
ptaylor
 
G

Guest

Awesome! Thank you very much! That worked!

--
Have a nice day!

~Paul

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''''''''
ptaylor



Douglas J. Steele said:
Simplest way would be

Between Nz([Forms]![ByInstructFrm]![StartDate], #1/1/100#) And
Nz([Forms]![ByInstructFrm]![EndDate], #12/31/9999#)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Paul (TRX) said:
Awesome! Thank you! That worked.

A quick follow-up question: The same doesn't seem to be working for the
date
range. I want to give my users the ability to enter a date range. I give
them
a start date and an end date, then the criteria in the date field of the
query searches for all records within that date range. In other words, the
criteria reads as follows:

Between [Forms]![ByInstructFrm]![StartDate] And
[Forms]![ByInstructFrm]![EndDate]

How could I also allow for the user to choose to leave the start and end
date blank, thereby getting all records, and not just a specific range. If
I
try just leaving the dates blank, it gives me nothing instead of all
records.
Your suggestion above doesn't seem to be working in the case of the date.

--
Have a nice day!

~Paul

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''''''''
ptaylor



Douglas J. Steele said:
I'm assuming that they're selecting the names from combo boxes?

Rather than having

[Forms]![NameOfForm]![NameOfCombo] as the criteria, use

[Forms]![NameOfForm]![NameOfCombo] OR [Forms]![NameOfForm]![NameOfCombo]
IS
NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I want to give my users a form where they will give some information and
a
query will be run based on that. However, I want this to be a bit more
dynamic than just a simply query.

Let me try to explain in more detail.

One of the forms in question will run a query that pulls all training
classes an employee has had. The user would select the employee name
and
search by that. However, I also want to give the user several other
options
that they either can choose to use or not. For example, I would want to
give
them a place to also enter the instructor name. So, they could choose
the
employee Peter Venkman and the instructor Bruce Wayne. This would pull
up
any
course Dr. Venkman took that was taught by Bruce Wayne. On the other
hand,
the user could choose the employee Peter Venkman and leave the
instructor
name blank, and this would in turn pull up ALL of Dr. Venkman's records
no
matter who was the instructor. Is there any way to do this? I think I
may
have asked a similar question, but I haven't gotten an answer yet on
whether
or not this is possible, and if it is how to do it. Thanks.

--
Have a nice day!

~Paul

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''''''''
ptaylor
 

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

Top