Multiple controls to a Query on a form

  • Thread starter Thread starter Rob S.
  • Start date Start date
R

Rob S.

I am a college student working on a semester project and I have a form that
is supposed to allow a user to input multiple critera to a query, like
LastName, Date, etc. I've tried it out multiple ways, but I cannot make it
work consistently. Here are my findings:

The query, when blank, runs fine. All records show.
If one form-based criteria is added to the query, (like
[Forms]![SwitchboardStaffStats]![Last_name_control]) and appropriate records
are typed into the FORM, it works properly. If the search box on form is
cleared, and then replaced with another name, it works properly.
If one more form-based criteria is added
([Forms]![SwitchboardStaffStats]![department_control]) And filled in, it is
run. The Query returns nothing.
If the Query is cleared of all criteria (the query and the form are
cleared), and then all three are copied back into the query criteria, the
query runs fine. Until you try to run a second one, with a different number
of criteria. For instance, if you search by date and name it works, but if
you then run a search for name, it does NOT work.
The form-to-query seems to work only ONCE. And then the code to connect
back to the form has to be copied back in for it to work again.

Am I missing something in my macro (which I created through Access)? I am
not familiar with VB, but could anyone provide some insight on how to make
this work?
 
If you are ANDing, ie using the same line for your criteria, you should use
something like:

[Forms]![SwitchboardStaffStats]![Last_name_control] & "*"

which will give you everything, if blank, and anything matching if full or
partially filled.
 
I am "ANDing", when I tried to input those new criteria (with the wildcard)
and run the query from there, I am prompted for Parameters. I left the form
blank and ran it, and again the parameters come up.

I then cleared the query of all criteria. And then just added the Form
reference for last name with the wildcard. It still prompted me for
parameters. Then when i filled something in the form, the query returned
nothing. Could I send you the database to see better? My teacher is even
struggling to solve this one.

Arvin Meyer said:
If you are ANDing, ie using the same line for your criteria, you should use
something like:

[Forms]![SwitchboardStaffStats]![Last_name_control] & "*"

which will give you everything, if blank, and anything matching if full or
partially filled.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Rob S. said:
I am a college student working on a semester project and I have a form that
is supposed to allow a user to input multiple critera to a query, like
LastName, Date, etc. I've tried it out multiple ways, but I cannot make
it
work consistently. Here are my findings:

The query, when blank, runs fine. All records show.
If one form-based criteria is added to the query, (like
[Forms]![SwitchboardStaffStats]![Last_name_control]) and appropriate
records
are typed into the FORM, it works properly. If the search box on form is
cleared, and then replaced with another name, it works properly.
If one more form-based criteria is added
([Forms]![SwitchboardStaffStats]![department_control]) And filled in, it
is
run. The Query returns nothing.
If the Query is cleared of all criteria (the query and the form are
cleared), and then all three are copied back into the query criteria, the
query runs fine. Until you try to run a second one, with a different
number
of criteria. For instance, if you search by date and name it works, but
if
you then run a search for name, it does NOT work.
The form-to-query seems to work only ONCE. And then the code to connect
back to the form has to be copied back in for it to work again.

Am I missing something in my macro (which I created through Access)? I am
not familiar with VB, but could anyone provide some insight on how to make
this work?
Click to expand...
Click to expand...
 
Could I send you the database to see better? My teacher is even
struggling to solve this one.
Click to expand...

Make sure that you compact and zip it first. My email address is at:

http://www.datastrat.com

add your phone number to the email so I can verify that it is the correct
file and not a prank or virus.
 
Using 'Like' worked!!! Thank you very much...but one more question: one of
my criteria is Date (the date of the transaction). I want the user to use a
range. So the criteria in the query looks like:
Between [Forms]![SwitchboardStaffStats]![start_prompt] & “*†And
[Forms]![SwitchboardStaffStats]![enddate_prompt] & "*"

When I add this to the ANDs, it displays this error and is not working. I
don't think you can include Like in this?

Arvin Meyer said:
Actually, that should be:

Like [Forms]![SwitchboardStaffStats]![Last_name_control] & "*"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



If you are ANDing, ie using the same line for your criteria, you should use
something like:

[Forms]![SwitchboardStaffStats]![Last_name_control] & "*"

which will give you everything, if blank, and anything matching if full or
partially filled.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Rob S. said:
I am a college student working on a semester project and I have a form that
is supposed to allow a user to input multiple critera to a query, like
LastName, Date, etc. I've tried it out multiple ways, but I cannot make
it
work consistently. Here are my findings:

The query, when blank, runs fine. All records show.
If one form-based criteria is added to the query, (like
[Forms]![SwitchboardStaffStats]![Last_name_control]) and appropriate
records
are typed into the FORM, it works properly. If the search box on form is
cleared, and then replaced with another name, it works properly.
If one more form-based criteria is added
([Forms]![SwitchboardStaffStats]![department_control]) And filled in, it
is
run. The Query returns nothing.
If the Query is cleared of all criteria (the query and the form are
cleared), and then all three are copied back into the query criteria, the
query runs fine. Until you try to run a second one, with a different
number
of criteria. For instance, if you search by date and name it works, but
if
you then run a search for name, it does NOT work.
The form-to-query seems to work only ONCE. And then the code to connect
back to the form has to be copied back in for it to work again.

Am I missing something in my macro (which I created through Access)? I am
not familiar with VB, but could anyone provide some insight on how to make
this work?
Click to expand...
Click to expand...
 
I probably don't need to send it to you now, the basics is working--but I
still can't figure out the Between date criteria. (see my post from like 20
minutes ago :p)
 
Wildcards such as * only work with text fields, not with dates.

Try

Between [Forms]![SwitchboardStaffStats]![start_prompt] And
[Forms]![SwitchboardStaffStats]![enddate_prompt]


[start_prompt] and [enddate_prompt] must contain complete dates (in either
mm/dd/yyyy format, or an non-ambiguous format such as yyyy-mm-dd or dd mmm
yyyy).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rob S. said:
Using 'Like' worked!!! Thank you very much...but one more question: one
of
my criteria is Date (the date of the transaction). I want the user to use
a
range. So the criteria in the query looks like:
Between [Forms]![SwitchboardStaffStats]![start_prompt] & "*" And
[Forms]![SwitchboardStaffStats]![enddate_prompt] & "*"

When I add this to the ANDs, it displays this error and is not working. I
don't think you can include Like in this?

Arvin Meyer said:
Actually, that should be:

Like [Forms]![SwitchboardStaffStats]![Last_name_control] & "*"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



If you are ANDing, ie using the same line for your criteria, you should
use
something like:

[Forms]![SwitchboardStaffStats]![Last_name_control] & "*"

which will give you everything, if blank, and anything matching if full
or
partially filled.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Rob S. said:
I am a college student working on a semester project and I have a form
that
is supposed to allow a user to input multiple critera to a query, like
LastName, Date, etc. I've tried it out multiple ways, but I cannot
make
it
work consistently. Here are my findings:

The query, when blank, runs fine. All records show.
If one form-based criteria is added to the query, (like
[Forms]![SwitchboardStaffStats]![Last_name_control]) and appropriate
records
are typed into the FORM, it works properly. If the search box on form
is
cleared, and then replaced with another name, it works properly.
If one more form-based criteria is added
([Forms]![SwitchboardStaffStats]![department_control]) And filled in,
it
is
run. The Query returns nothing.
If the Query is cleared of all criteria (the query and the form are
cleared), and then all three are copied back into the query criteria,
the
query runs fine. Until you try to run a second one, with a different
number
of criteria. For instance, if you search by date and name it works,
but
if
you then run a search for name, it does NOT work.
The form-to-query seems to work only ONCE. And then the code to
connect
back to the form has to be copied back in for it to work again.

Am I missing something in my macro (which I created through Access)? I
am
not familiar with VB, but could anyone provide some insight on how to
make
this work?
Click to expand...
Click to expand...
Click to expand...
 
That makes sense. However, if a user only wants to input name and
department, the query returns blank because the date fields are left blank.
Should I default value from like 1/1/1950 to 1/1//2050 to encompass all the
data and then if they want to specify a time spot they would enter something
different. Or, I could leave it blank and make it a required field.

I just don't want the users to have the hassle of filling in the control for
date when it is not a part of their criteria.

Douglas J. Steele said:
Wildcards such as * only work with text fields, not with dates.

Try

Between [Forms]![SwitchboardStaffStats]![start_prompt] And
[Forms]![SwitchboardStaffStats]![enddate_prompt]


[start_prompt] and [enddate_prompt] must contain complete dates (in either
mm/dd/yyyy format, or an non-ambiguous format such as yyyy-mm-dd or dd mmm
yyyy).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rob S. said:
Using 'Like' worked!!! Thank you very much...but one more question: one
of
my criteria is Date (the date of the transaction). I want the user to use
a
range. So the criteria in the query looks like:
Between [Forms]![SwitchboardStaffStats]![start_prompt] & "*" And
[Forms]![SwitchboardStaffStats]![enddate_prompt] & "*"

When I add this to the ANDs, it displays this error and is not working. I
don't think you can include Like in this?

Arvin Meyer said:
Actually, that should be:

Like [Forms]![SwitchboardStaffStats]![Last_name_control] & "*"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



If you are ANDing, ie using the same line for your criteria, you should
use
something like:

[Forms]![SwitchboardStaffStats]![Last_name_control] & "*"

which will give you everything, if blank, and anything matching if full
or
partially filled.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I am a college student working on a semester project and I have a form
that
is supposed to allow a user to input multiple critera to a query, like
LastName, Date, etc. I've tried it out multiple ways, but I cannot
make
it
work consistently. Here are my findings:

The query, when blank, runs fine. All records show.
If one form-based criteria is added to the query, (like
[Forms]![SwitchboardStaffStats]![Last_name_control]) and appropriate
records
are typed into the FORM, it works properly. If the search box on form
is
cleared, and then replaced with another name, it works properly.
If one more form-based criteria is added
([Forms]![SwitchboardStaffStats]![department_control]) And filled in,
it
is
run. The Query returns nothing.
If the Query is cleared of all criteria (the query and the form are
cleared), and then all three are copied back into the query criteria,
the
query runs fine. Until you try to run a second one, with a different
number
of criteria. For instance, if you search by date and name it works,
but
if
you then run a search for name, it does NOT work.
The form-to-query seems to work only ONCE. And then the code to
connect
back to the form has to be copied back in for it to work again.

Am I missing something in my macro (which I created through Access)? I
am
not familiar with VB, but could anyone provide some insight on how to
make
this work?
Click to expand...
Click to expand...
Click to expand...
 
Between Nz([Forms]![SwitchboardStaffStats]![start_prompt], #1/1/1950#) And
Nz([Forms]![SwitchboardStaffStats]![enddate_prompt], #1/1/2050#)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rob S. said:
That makes sense. However, if a user only wants to input name and
department, the query returns blank because the date fields are left
blank.
Should I default value from like 1/1/1950 to 1/1//2050 to encompass all
the
data and then if they want to specify a time spot they would enter
something
different. Or, I could leave it blank and make it a required field.

I just don't want the users to have the hassle of filling in the control
for
date when it is not a part of their criteria.

Douglas J. Steele said:
Wildcards such as * only work with text fields, not with dates.

Try

Between [Forms]![SwitchboardStaffStats]![start_prompt] And
[Forms]![SwitchboardStaffStats]![enddate_prompt]


[start_prompt] and [enddate_prompt] must contain complete dates (in
either
mm/dd/yyyy format, or an non-ambiguous format such as yyyy-mm-dd or dd
mmm
yyyy).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rob S. said:
Using 'Like' worked!!! Thank you very much...but one more question:
one
of
my criteria is Date (the date of the transaction). I want the user to
use
a
range. So the criteria in the query looks like:
Between [Forms]![SwitchboardStaffStats]![start_prompt] & "*" And
[Forms]![SwitchboardStaffStats]![enddate_prompt] & "*"

When I add this to the ANDs, it displays this error and is not working.
I
don't think you can include Like in this?

:

Actually, that should be:

Like [Forms]![SwitchboardStaffStats]![Last_name_control] & "*"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



If you are ANDing, ie using the same line for your criteria, you
should
use
something like:

[Forms]![SwitchboardStaffStats]![Last_name_control] & "*"

which will give you everything, if blank, and anything matching if
full
or
partially filled.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I am a college student working on a semester project and I have a
form
that
is supposed to allow a user to input multiple critera to a query,
like
LastName, Date, etc. I've tried it out multiple ways, but I cannot
make
it
work consistently. Here are my findings:

The query, when blank, runs fine. All records show.
If one form-based criteria is added to the query, (like
[Forms]![SwitchboardStaffStats]![Last_name_control]) and appropriate
records
are typed into the FORM, it works properly. If the search box on
form
is
cleared, and then replaced with another name, it works properly.
If one more form-based criteria is added
([Forms]![SwitchboardStaffStats]![department_control]) And filled
in,
it
is
run. The Query returns nothing.
If the Query is cleared of all criteria (the query and the form are
cleared), and then all three are copied back into the query
criteria,
the
query runs fine. Until you try to run a second one, with a
different
number
of criteria. For instance, if you search by date and name it works,
but
if
you then run a search for name, it does NOT work.
The form-to-query seems to work only ONCE. And then the code to
connect
back to the form has to be copied back in for it to work again.

Am I missing something in my macro (which I created through Access)?
I
am
not familiar with VB, but could anyone provide some insight on how
to
make
this work?
Click to expand...
Click to expand...
Click to expand...
 
Awesome--that one works, always great to learn a new function.

Looking back to my original question in the thread, Arvin suggested to use:

Like [Forms]![SwitchboardStaffStats]![phonenumber_control] & "*"

as my criteria. This appeared to work at first, until I tried it out and
looked a bid closer. This returns what is typed into the control box on the
form to the query. If the control is left blank, it should return all (258)
records. However, it returns 188, ignoring those with blank (or null) phone
number fields. I've tried to add something like &"Is Null" but, I can't seem
to make it work. If my data was perfect and filled out, this wouldn't be an
issue.



Douglas J. Steele said:
Between Nz([Forms]![SwitchboardStaffStats]![start_prompt], #1/1/1950#) And
Nz([Forms]![SwitchboardStaffStats]![enddate_prompt], #1/1/2050#)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rob S. said:
That makes sense. However, if a user only wants to input name and
department, the query returns blank because the date fields are left
blank.
Should I default value from like 1/1/1950 to 1/1//2050 to encompass all
the
data and then if they want to specify a time spot they would enter
something
different. Or, I could leave it blank and make it a required field.

I just don't want the users to have the hassle of filling in the control
for
date when it is not a part of their criteria.

Douglas J. Steele said:
Wildcards such as * only work with text fields, not with dates.

Try

Between [Forms]![SwitchboardStaffStats]![start_prompt] And
[Forms]![SwitchboardStaffStats]![enddate_prompt]


[start_prompt] and [enddate_prompt] must contain complete dates (in
either
mm/dd/yyyy format, or an non-ambiguous format such as yyyy-mm-dd or dd
mmm
yyyy).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Using 'Like' worked!!! Thank you very much...but one more question:
one
of
my criteria is Date (the date of the transaction). I want the user to
use
a
range. So the criteria in the query looks like:
Between [Forms]![SwitchboardStaffStats]![start_prompt] & "*" And
[Forms]![SwitchboardStaffStats]![enddate_prompt] & "*"

When I add this to the ANDs, it displays this error and is not working.
I
don't think you can include Like in this?

:

Actually, that should be:

Like [Forms]![SwitchboardStaffStats]![Last_name_control] & "*"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



If you are ANDing, ie using the same line for your criteria, you
should
use
something like:

[Forms]![SwitchboardStaffStats]![Last_name_control] & "*"

which will give you everything, if blank, and anything matching if
full
or
partially filled.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I am a college student working on a semester project and I have a
form
that
is supposed to allow a user to input multiple critera to a query,
like
LastName, Date, etc. I've tried it out multiple ways, but I cannot
make
it
work consistently. Here are my findings:

The query, when blank, runs fine. All records show.
If one form-based criteria is added to the query, (like
[Forms]![SwitchboardStaffStats]![Last_name_control]) and appropriate
records
are typed into the FORM, it works properly. If the search box on
form
is
cleared, and then replaced with another name, it works properly.
If one more form-based criteria is added
([Forms]![SwitchboardStaffStats]![department_control]) And filled
in,
it
is
run. The Query returns nothing.
If the Query is cleared of all criteria (the query and the form are
cleared), and then all three are copied back into the query
criteria,
the
query runs fine. Until you try to run a second one, with a
different
number
of criteria. For instance, if you search by date and name it works,
but
if
you then run a search for name, it does NOT work.
The form-to-query seems to work only ONCE. And then the code to
connect
back to the form has to be copied back in for it to work again.

Am I missing something in my macro (which I created through Access)?
I
am
not familiar with VB, but could anyone provide some insight on how
to
make
this work?
Click to expand...
Click to expand...
Click to expand...
 
If they're only putting part of the phone number into the text box, use

Like [Forms]![SwitchboardStaffStats]![phonenumber_control] & "*"
OR [Forms]![SwitchboardStaffStats]![phonenumber_control] IS NULL

If they're putting the entire number into the text box, use

=[Forms]![SwitchboardStaffStats]![phonenumber_control]
OR [Forms]![SwitchboardStaffStats]![phonenumber_control] IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rob S. said:
Awesome--that one works, always great to learn a new function.

Looking back to my original question in the thread, Arvin suggested to
use:

Like [Forms]![SwitchboardStaffStats]![phonenumber_control] & "*"

as my criteria. This appeared to work at first, until I tried it out and
looked a bid closer. This returns what is typed into the control box on
the
form to the query. If the control is left blank, it should return all
(258)
records. However, it returns 188, ignoring those with blank (or null)
phone
number fields. I've tried to add something like &"Is Null" but, I can't
seem
to make it work. If my data was perfect and filled out, this wouldn't be
an
issue.



Douglas J. Steele said:
Between Nz([Forms]![SwitchboardStaffStats]![start_prompt], #1/1/1950#)
And
Nz([Forms]![SwitchboardStaffStats]![enddate_prompt], #1/1/2050#)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rob S. said:
That makes sense. However, if a user only wants to input name and
department, the query returns blank because the date fields are left
blank.
Should I default value from like 1/1/1950 to 1/1//2050 to encompass all
the
data and then if they want to specify a time spot they would enter
something
different. Or, I could leave it blank and make it a required field.

I just don't want the users to have the hassle of filling in the
control
for
date when it is not a part of their criteria.

:

Wildcards such as * only work with text fields, not with dates.

Try

Between [Forms]![SwitchboardStaffStats]![start_prompt] And
[Forms]![SwitchboardStaffStats]![enddate_prompt]


[start_prompt] and [enddate_prompt] must contain complete dates (in
either
mm/dd/yyyy format, or an non-ambiguous format such as yyyy-mm-dd or dd
mmm
yyyy).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Using 'Like' worked!!! Thank you very much...but one more question:
one
of
my criteria is Date (the date of the transaction). I want the user
to
use
a
range. So the criteria in the query looks like:
Between [Forms]![SwitchboardStaffStats]![start_prompt] & "*" And
[Forms]![SwitchboardStaffStats]![enddate_prompt] & "*"

When I add this to the ANDs, it displays this error and is not
working.
I
don't think you can include Like in this?

:

Actually, that should be:

Like [Forms]![SwitchboardStaffStats]![Last_name_control] & "*"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



If you are ANDing, ie using the same line for your criteria, you
should
use
something like:

[Forms]![SwitchboardStaffStats]![Last_name_control] & "*"

which will give you everything, if blank, and anything matching if
full
or
partially filled.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I am a college student working on a semester project and I have a
form
that
is supposed to allow a user to input multiple critera to a query,
like
LastName, Date, etc. I've tried it out multiple ways, but I
cannot
make
it
work consistently. Here are my findings:

The query, when blank, runs fine. All records show.
If one form-based criteria is added to the query, (like
[Forms]![SwitchboardStaffStats]![Last_name_control]) and
appropriate
records
are typed into the FORM, it works properly. If the search box on
form
is
cleared, and then replaced with another name, it works properly.
If one more form-based criteria is added
([Forms]![SwitchboardStaffStats]![department_control]) And
filled
in,
it
is
run. The Query returns nothing.
If the Query is cleared of all criteria (the query and the form
are
cleared), and then all three are copied back into the query
criteria,
the
query runs fine. Until you try to run a second one, with a
different
number
of criteria. For instance, if you search by date and name it
works,
but
if
you then run a search for name, it does NOT work.
The form-to-query seems to work only ONCE. And then the code to
connect
back to the form has to be copied back in for it to work again.

Am I missing something in my macro (which I created through
Access)?
I
am
not familiar with VB, but could anyone provide some insight on
how
to
make
this work?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top