|
|
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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
"Rob S." <RobS[ at ]discussions.microsoft.com> wrote in message
news:265C677D-5326-4F28-89B3-429080864286[ at ]microsoft.com...
[Quoted Text] >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?
|
|
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." <RobS[ at ]discussions.microsoft.com> wrote in message
news:265C677D-5326-4F28-89B3-429080864286[ at ]microsoft.com...
[Quoted Text] >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?
|
|
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 [MVP]" wrote:
[Quoted Text] > 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." <RobS[ at ]discussions.microsoft.com> wrote in message > news:265C677D-5326-4F28-89B3-429080864286[ at ]microsoft.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? > > > |
|
"Rob S." <RobS[ at ]discussions.microsoft.com> wrote in message
news:A59FA3F3-90D6-4B02-84A7-A43949D4F5FC[ at ]microsoft.com...
[Quoted Text] > Could I send you the database to see better? My teacher is even
> struggling to solve this one.
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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
|
|
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 [MVP]" wrote:
[Quoted Text] > 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." <RobS[ at ]discussions.microsoft.com> wrote in message > news:265C677D-5326-4F28-89B3-429080864286[ at ]microsoft.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? > > > |
|
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)
"Arvin Meyer [MVP]" wrote:
[Quoted Text] > "Rob S." <RobS[ at ]discussions.microsoft.com> wrote in message > news:A59FA3F3-90D6-4B02-84A7-A43949D4F5FC[ at ]microsoft.com... > > > Could I send you the database to see better? My teacher is even > > struggling to solve this one. > > 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. > -- > Arvin Meyer, MCP, MVP > http://www.datastrat.com> http://www.mvps.org/access> http://www.accessmvp.com > > > |
|
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
http://I.Am/DougSteele
(no e-mails, please!)
"Rob S." <RobS[ at ]discussions.microsoft.com> wrote in message
news:7273E352-EBE6-4E74-B45D-67A5FB7BB915[ at ]microsoft.com...
[Quoted Text] > 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 [MVP]" wrote: > >> 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." <RobS[ at ]discussions.microsoft.com> wrote in message >> news:265C677D-5326-4F28-89B3-429080864286[ at ]microsoft.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? >> >> >>
|
|
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" wrote:
[Quoted Text] > 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 > http://I.Am/DougSteele> (no e-mails, please!) > > > "Rob S." <RobS[ at ]discussions.microsoft.com> wrote in message > news:7273E352-EBE6-4E74-B45D-67A5FB7BB915[ at ]microsoft.com... > > 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 [MVP]" wrote: > > > >> 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." <RobS[ at ]discussions.microsoft.com> wrote in message > >> news:265C677D-5326-4F28-89B3-429080864286[ at ]microsoft.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? > >> > >> > >> > > > |
|
Between Nz([Forms]![SwitchboardStaffStats]![start_prompt], #1/1/1950#) And
Nz([Forms]![SwitchboardStaffStats]![enddate_prompt], #1/1/2050#)
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Rob S." <RobS[ at ]discussions.microsoft.com> wrote in message
news:C9BCB63E-E69A-43F7-92EB-F878AAE39C60[ at ]microsoft.com...
[Quoted Text] > 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" wrote: > >> 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 >> http://I.Am/DougSteele>> (no e-mails, please!) >> >> >> "Rob S." <RobS[ at ]discussions.microsoft.com> wrote in message >> news:7273E352-EBE6-4E74-B45D-67A5FB7BB915[ at ]microsoft.com... >> > 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 [MVP]" wrote: >> > >> >> 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." <RobS[ at ]discussions.microsoft.com> wrote in message >> >> news:265C677D-5326-4F28-89B3-429080864286[ at ]microsoft.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? >> >> >> >> >> >> >> >> >>
|
|
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" wrote:
[Quoted Text] > Between Nz([Forms]![SwitchboardStaffStats]![start_prompt], #1/1/1950#) And > Nz([Forms]![SwitchboardStaffStats]![enddate_prompt], #1/1/2050#) > > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele> (no e-mails, please!) > > > "Rob S." <RobS[ at ]discussions.microsoft.com> wrote in message > news:C9BCB63E-E69A-43F7-92EB-F878AAE39C60[ at ]microsoft.com... > > 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" wrote: > > > >> 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 > >> http://I.Am/DougSteele> >> (no e-mails, please!) > >> > >> > >> "Rob S." <RobS[ at ]discussions.microsoft.com> wrote in message > >> news:7273E352-EBE6-4E74-B45D-67A5FB7BB915[ at ]microsoft.com... > >> > 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 [MVP]" wrote: > >> > > >> >> 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." <RobS[ at ]discussions.microsoft.com> wrote in message > >> >> news:265C677D-5326-4F28-89B3-429080864286[ at ]microsoft.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? > >> >> > >> >> > >> >> > >> > >> > >> > > > |
|
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
http://I.Am/DougSteele
(no e-mails, please!)
"Rob S." <RobS[ at ]discussions.microsoft.com> wrote in message
news:EDECC897-F4A8-46A4-9C76-AF731A50DEDC[ at ]microsoft.com...
[Quoted Text] > 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" wrote: > >> Between Nz([Forms]![SwitchboardStaffStats]![start_prompt], #1/1/1950#) >> And >> Nz([Forms]![SwitchboardStaffStats]![enddate_prompt], #1/1/2050#) >> >> >> -- >> Doug Steele, Microsoft Access MVP >> http://I.Am/DougSteele>> (no e-mails, please!) >> >> >> "Rob S." <RobS[ at ]discussions.microsoft.com> wrote in message >> news:C9BCB63E-E69A-43F7-92EB-F878AAE39C60[ at ]microsoft.com... >> > 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" wrote: >> > >> >> 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 >> >> http://I.Am/DougSteele>> >> (no e-mails, please!) >> >> >> >> >> >> "Rob S." <RobS[ at ]discussions.microsoft.com> wrote in message >> >> news:7273E352-EBE6-4E74-B45D-67A5FB7BB915[ at ]microsoft.com... >> >> > 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 [MVP]" wrote: >> >> > >> >> >> 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." <RobS[ at ]discussions.microsoft.com> wrote in message >> >> >> news:265C677D-5326-4F28-89B3-429080864286[ at ]microsoft.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? >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>
|
|
|