multiple selection criteria with wildcard

B

Brigitte P

My users use a form to drill their reporting requirements down to specific
data, e.g., Department, Ward, Job Positon, which is then passed on to the
query which is the basis for the resulting report. They have the option to
fill fields in or leave them blank to have their selection as broad or
narrow as needed. Now I have on field with job positions with the query
criteria: Like [Forms]![frm LookupMadatoryTraining]![txtPosition] & "*"
which works fine. But now I would like to include perhaps 4 job options so
they can select all within one department who meet a certain job criteria,
e.g., Nursing - Registered Nurse, Practical Nurse, Aide, Secretary. They
have 4 selection fields named txtPos1, txt Pos2 and so on.
I tried Like [Forms]![frm LookupMadatoryTraining]![txtPosition] & "*" or
Like [Forms]![frm LookupMadatoryTraining]![txtPos1] & "*" or Like
[Forms]![frm LookupMadatoryTraining]![txtPos2] & "*"
I also tried Like [Forms]![frm LookupMadatoryTraining]![txtPosition] or Like
[Forms]![frm LookupMadatoryTraining]![txtPos2] or Like [Forms]![frm
LookupMadatoryTraining]![txtPos3] & "*"
All doesn't work; my logic is somehow wrong even though I know it's an Or
criteria.
Would someone please point me in the right direction.
As always, many thanks to your helpful people.
Brigitte P
 
B

Brigitte P

I've tried some more. The expression Like [Forms]![frm
LookupMadatoryTraining]![txtPosition] Or [Forms]![frm
LookupMadatoryTraining]![txtPos2] Or [Forms]![frm
LookupMadatoryTraining]![txtPos3] & "*" will take the first two fields, but
it ignores the third field. Is this a limitation in Access97?
Brigitte P.
 
T

Tom Ellison

Dear Brigitte:

I don't think it is Access that is limiting you, but perhaps your
logic and syntax are. What I see mostly is a mistake in your
understanding of how the things you are doing will work.

WHERE Somecolumn LIKE
[Forms]![frmLookupMandatoryTraining]![txtPosition] & "*"
OR
Somecolumn LIKE [Forms]![frmLookupMandatoryTraining]![txtPos2] & "*"
OR
Somecolumn LIKE [Forms]![frmLookupMandatoryTraining]![txtPos3] & "*"

This is probably NOT the complete solution, but is a step in that
direction.

The last thing that happens is the OR between 3 tests. Before that,
the LIKE is done. The first thing that happens is that it retrieves
the value from the form and appends the asterisk.

Therefore, you need to append the asterisk all 3 times (if I
understand your requirement). And you must use LIKE 3 times.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts



I've tried some more. The expression Like [Forms]![frm
LookupMadatoryTraining]![txtPosition] Or [Forms]![frm
LookupMadatoryTraining]![txtPos2] Or [Forms]![frm
LookupMadatoryTraining]![txtPos3] & "*" will take the first two fields, but
it ignores the third field. Is this a limitation in Access97?
Brigitte P.


Brigitte P said:
My users use a form to drill their reporting requirements down to specific
data, e.g., Department, Ward, Job Positon, which is then passed on to the
query which is the basis for the resulting report. They have the option to
fill fields in or leave them blank to have their selection as broad or
narrow as needed. Now I have on field with job positions with the query
criteria: Like [Forms]![frm LookupMadatoryTraining]![txtPosition] & "*"
which works fine. But now I would like to include perhaps 4 job options so
they can select all within one department who meet a certain job criteria,
e.g., Nursing - Registered Nurse, Practical Nurse, Aide, Secretary. They
have 4 selection fields named txtPos1, txt Pos2 and so on.
I tried Like [Forms]![frm LookupMadatoryTraining]![txtPosition] & "*" or
Like [Forms]![frm LookupMadatoryTraining]![txtPos1] & "*" or Like
[Forms]![frm LookupMadatoryTraining]![txtPos2] & "*"
I also tried Like [Forms]![frm LookupMadatoryTraining]![txtPosition] or Like
[Forms]![frm LookupMadatoryTraining]![txtPos2] or Like [Forms]![frm
LookupMadatoryTraining]![txtPos3] & "*"
All doesn't work; my logic is somehow wrong even though I know it's an Or
criteria.
Would someone please point me in the right direction.
As always, many thanks to your helpful people.
Brigitte P
Click to expand...
Click to expand...
 
B

Brigitte P

Thanks Tom. I agree that my logic is probably wrong, but I tried sort of
what you suggested. I did Like [Forms]![frm
LookupMadatoryTraining]![txtPosition] & "*" or
Like [Forms]![frm LookupMadatoryTraining]![txtPos1] & "*" or Like
[Forms]![frm LookupMadatoryTraining]![txtPos2] & "*"
I don't know what you mean by Somecolumn, and also noted that the
interactive query writer used HAVING vs the WHERE that you suggested. When I
change the HAVING to WHERE, the query will not run.

Based on another post in this group, I also tried Like "*" & [Forms]![frm
LookupMadatoryTraining]![txtPosition] & "*" Or Like "*" & [Forms]![frm
LookupMadatoryTraining]![txtPos2] & "*" Or Like "*" & [Forms]![frm
LookupMadatoryTraining]![txtPos3] & "*" Or Like "*" & [Forms]![frm
LookupMadatoryTraining]![txtPos4] & "*".
So each conditions is evaluated using Like "*" & [Forms]![frm
LookupMadatoryTraining]![txtPos4] & "*"
This works if the user completes all selection fields, but if we leave on
empty, the wildcards kicks in (which we told it to do) and all the previous
selections are canceled out. I kind of understand why this is, but I can't
figure out how to do it different.
Any additional ideas.
Thanks for any input.
Brigitte P.

Tom Ellison said:
Dear Brigitte:

I don't think it is Access that is limiting you, but perhaps your
logic and syntax are. What I see mostly is a mistake in your
understanding of how the things you are doing will work.

WHERE Somecolumn LIKE
[Forms]![frmLookupMandatoryTraining]![txtPosition] & "*"
OR
Somecolumn LIKE [Forms]![frmLookupMandatoryTraining]![txtPos2] & "*"
OR
Somecolumn LIKE [Forms]![frmLookupMandatoryTraining]![txtPos3] & "*"

This is probably NOT the complete solution, but is a step in that
direction.

The last thing that happens is the OR between 3 tests. Before that,
the LIKE is done. The first thing that happens is that it retrieves
the value from the form and appends the asterisk.

Therefore, you need to append the asterisk all 3 times (if I
understand your requirement). And you must use LIKE 3 times.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts



I've tried some more. The expression Like [Forms]![frm
LookupMadatoryTraining]![txtPosition] Or [Forms]![frm
LookupMadatoryTraining]![txtPos2] Or [Forms]![frm
LookupMadatoryTraining]![txtPos3] & "*" will take the first two fields, but
it ignores the third field. Is this a limitation in Access97?
Brigitte P.


Brigitte P said:
My users use a form to drill their reporting requirements down to specific
data, e.g., Department, Ward, Job Positon, which is then passed on to the
query which is the basis for the resulting report. They have the option to
fill fields in or leave them blank to have their selection as broad or
narrow as needed. Now I have on field with job positions with the query
criteria: Like [Forms]![frm LookupMadatoryTraining]![txtPosition] & "*"
which works fine. But now I would like to include perhaps 4 job options so
they can select all within one department who meet a certain job criteria,
e.g., Nursing - Registered Nurse, Practical Nurse, Aide, Secretary. They
have 4 selection fields named txtPos1, txt Pos2 and so on.
I tried Like [Forms]![frm LookupMadatoryTraining]![txtPosition] & "*" or
Like [Forms]![frm LookupMadatoryTraining]![txtPos1] & "*" or Like
[Forms]![frm LookupMadatoryTraining]![txtPos2] & "*"
I also tried Like [Forms]![frm LookupMadatoryTraining]![txtPosition] or Like
[Forms]![frm LookupMadatoryTraining]![txtPos2] or Like [Forms]![frm
LookupMadatoryTraining]![txtPos3] & "*"
All doesn't work; my logic is somehow wrong even though I know it's an Or
criteria.
Would someone please point me in the right direction.
As always, many thanks to your helpful people.
Brigitte P
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Brigitte:

You're looking at a "design view" or your query, which I cannot see,
and which you cannot transmit to me over this newsgroup.

In order to communicate, we need to work from the SQL View of your
query, which you can communicate to me. Afterward, you will be able
to see how this is represented in design view by switcfhing back to
that.

So, please switch to SQL View on what you have so far and past the
text there into your next post here. That will give me something I
can study, and at the same time it may explain to you what I meant by
"Somecolumn".

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Thanks Tom. I agree that my logic is probably wrong, but I tried sort of
what you suggested. I did Like [Forms]![frm
LookupMadatoryTraining]![txtPosition] & "*" or
Like [Forms]![frm LookupMadatoryTraining]![txtPos1] & "*" or Like
[Forms]![frm LookupMadatoryTraining]![txtPos2] & "*"
I don't know what you mean by Somecolumn, and also noted that the
interactive query writer used HAVING vs the WHERE that you suggested. When I
change the HAVING to WHERE, the query will not run.

Based on another post in this group, I also tried Like "*" & [Forms]![frm
LookupMadatoryTraining]![txtPosition] & "*" Or Like "*" & [Forms]![frm
LookupMadatoryTraining]![txtPos2] & "*" Or Like "*" & [Forms]![frm
LookupMadatoryTraining]![txtPos3] & "*" Or Like "*" & [Forms]![frm
LookupMadatoryTraining]![txtPos4] & "*".
So each conditions is evaluated using Like "*" & [Forms]![frm
LookupMadatoryTraining]![txtPos4] & "*"
This works if the user completes all selection fields, but if we leave on
empty, the wildcards kicks in (which we told it to do) and all the previous
selections are canceled out. I kind of understand why this is, but I can't
figure out how to do it different.
Any additional ideas.
Thanks for any input.
Brigitte P.

Tom Ellison said:
Dear Brigitte:

I don't think it is Access that is limiting you, but perhaps your
logic and syntax are. What I see mostly is a mistake in your
understanding of how the things you are doing will work.

WHERE Somecolumn LIKE
[Forms]![frmLookupMandatoryTraining]![txtPosition] & "*"
OR
Somecolumn LIKE [Forms]![frmLookupMandatoryTraining]![txtPos2] & "*"
OR
Somecolumn LIKE [Forms]![frmLookupMandatoryTraining]![txtPos3] & "*"

This is probably NOT the complete solution, but is a step in that
direction.

The last thing that happens is the OR between 3 tests. Before that,
the LIKE is done. The first thing that happens is that it retrieves
the value from the form and appends the asterisk.

Therefore, you need to append the asterisk all 3 times (if I
understand your requirement). And you must use LIKE 3 times.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts



I've tried some more. The expression Like [Forms]![frm
LookupMadatoryTraining]![txtPosition] Or [Forms]![frm
LookupMadatoryTraining]![txtPos2] Or [Forms]![frm
LookupMadatoryTraining]![txtPos3] & "*" will take the first two fields, but
it ignores the third field. Is this a limitation in Access97?
Brigitte P.


My users use a form to drill their reporting requirements down to specific
data, e.g., Department, Ward, Job Positon, which is then passed on to the
query which is the basis for the resulting report. They have the option to
fill fields in or leave them blank to have their selection as broad or
narrow as needed. Now I have on field with job positions with the query
criteria: Like [Forms]![frm LookupMadatoryTraining]![txtPosition] & "*"
which works fine. But now I would like to include perhaps 4 job options so
they can select all within one department who meet a certain job criteria,
e.g., Nursing - Registered Nurse, Practical Nurse, Aide, Secretary. They
have 4 selection fields named txtPos1, txt Pos2 and so on.
I tried Like [Forms]![frm LookupMadatoryTraining]![txtPosition] & "*" or
Like [Forms]![frm LookupMadatoryTraining]![txtPos1] & "*" or Like
[Forms]![frm LookupMadatoryTraining]![txtPos2] & "*"
I also tried Like [Forms]![frm LookupMadatoryTraining]![txtPosition] or
Like
[Forms]![frm LookupMadatoryTraining]![txtPos2] or Like [Forms]![frm
LookupMadatoryTraining]![txtPos3] & "*"
All doesn't work; my logic is somehow wrong even though I know it's an Or
criteria.
Would someone please point me in the right direction.
As always, many thanks to your helpful people.
Brigitte P
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Criteria to return all records if selection from form is null 3
Report criteria 3
Multiple Criteria in Listbox 3
NEED HELP with a multi-criteria query 2
Issue with multiple parameter driven form- based Query 4
Creating parameter queries based on a form with multiple criteria 2
Multiple criteria in one field- query by form 1
Criteria Issue with multiple Option Class sorting. 3

Top