multiple criteria statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

how do i create multiple criteria statements using the same fields for
different criterias and attach to a single form to produce filtered data
based on multiple select choices from the form:

1. date, name, goal
2. date, goal, title
3. goal, name, title
etc...
 
Do you mean create a query with multiple criteria? IOW, the criteria for one
line would be something like : [Date]=... AND [Name]=... AND [Goal]=...
Then the next criteria line would be treated as an alternate to the first
criteria (OR).
So you'd have as many lines as there are independent criteria combinations.
 
Yes,... but when I do so, having (a lot of selections to combine) it looses
some of the choices when placing the same fields on different OR rows
attached to different AND choices...

kingston via AccessMonster.com said:
Do you mean create a query with multiple criteria? IOW, the criteria for one
line would be something like : [Date]=... AND [Name]=... AND [Goal]=...
Then the next criteria line would be treated as an alternate to the first
criteria (OR).
So you'd have as many lines as there are independent criteria combinations.
how do i create multiple criteria statements using the same fields for
different criterias and attach to a single form to produce filtered data
based on multiple select choices from the form:

1. date, name, goal
2. date, goal, title
3. goal, name, title
etc...
Click to expand...
Click to expand...
 
Are you sure you're not duplicating a set of criteria? I'm not sure I
understand what you mean by losing some of the choices.
Yes,... but when I do so, having (a lot of selections to combine) it looses
some of the choices when placing the same fields on different OR rows
attached to different AND choices...
Do you mean create a query with multiple criteria? IOW, the criteria for one
line would be something like : [Date]=... AND [Name]=... AND [Goal]=...
Click to expand...
[quoted text clipped - 10 lines]
Click to expand...
 
so you are saying that the following is supposed to work:

[date]=
or [date]= AND [name]=
or [date]= AND [name]=
or [date]= AND [name]= AND [goal]=
or [date]= AND [name]= AND [goal]= AND [location]=

when selecting 1, or 2, or 3, or 4, etc selections from an unattached option
group?

kingston via AccessMonster.com said:
Are you sure you're not duplicating a set of criteria? I'm not sure I
understand what you mean by losing some of the choices.
Yes,... but when I do so, having (a lot of selections to combine) it looses
some of the choices when placing the same fields on different OR rows
attached to different AND choices...
Do you mean create a query with multiple criteria? IOW, the criteria for one
line would be something like : [Date]=... AND [Name]=... AND [Goal]=...
Click to expand...
[quoted text clipped - 10 lines]
3. goal, name, title
etc...
Click to expand...
Click to expand...
Click to expand...
 
Yes, it does work. Interpret this literally (consider each criteria
independently because of the OR). The second and third criteria may be
condensed by Access if they end up being the same. If there is only a date
specified, all criteria will be evaluated, but only the first one will return
results unless there are Null values in your dataset. If a date and a name
are specified, criteria 1 will still return results based only on the date
because Access evaluates this independently. Does this make sense or am I
still not understanding the question correctly?
so you are saying that the following is supposed to work:

[date]=
or [date]= AND [name]=
or [date]= AND [name]=
or [date]= AND [name]= AND [goal]=
or [date]= AND [name]= AND [goal]= AND [location]=

when selecting 1, or 2, or 3, or 4, etc selections from an unattached option
group?
Are you sure you're not duplicating a set of criteria? I'm not sure I
understand what you mean by losing some of the choices.
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
typo on line 3, forgot to add another field,... i'm new at this, and have yet
to see examples of same data fields being repeated on different rows for
different criteria, reread MS Access 2003 (Bible) over and over, and thought
that logic was correct, but it's not working for me,... unless my vba
statements in the form are not written correctly which is causing it not to
interpret the query the right way, and when i do print preview of the report,
some columns of data for one or two fields, disappear...

kingston via AccessMonster.com said:
Yes, it does work. Interpret this literally (consider each criteria
independently because of the OR). The second and third criteria may be
condensed by Access if they end up being the same. If there is only a date
specified, all criteria will be evaluated, but only the first one will return
results unless there are Null values in your dataset. If a date and a name
are specified, criteria 1 will still return results based only on the date
because Access evaluates this independently. Does this make sense or am I
still not understanding the question correctly?
so you are saying that the following is supposed to work:

[date]=
or [date]= AND [name]=
or [date]= AND [name]=
or [date]= AND [name]= AND [goal]=
or [date]= AND [name]= AND [goal]= AND [location]=

when selecting 1, or 2, or 3, or 4, etc selections from an unattached option
group?
Are you sure you're not duplicating a set of criteria? I'm not sure I
understand what you mean by losing some of the choices.
Click to expand...
[quoted text clipped - 8 lines]
3. goal, name, title
etc...
Click to expand...
Click to expand...
Click to expand...
 
The report and the query are two separate objects and won't necessarily
return the same results. Check the query results directly. How are you
using VBA in the form? The query can work directly from the form's controls
without VBA:

Field: [Name]
Criteria: [Forms]![FormName]![NameControl]
typo on line 3, forgot to add another field,... i'm new at this, and have yet
to see examples of same data fields being repeated on different rows for
different criteria, reread MS Access 2003 (Bible) over and over, and thought
that logic was correct, but it's not working for me,... unless my vba
statements in the form are not written correctly which is causing it not to
interpret the query the right way, and when i do print preview of the report,
some columns of data for one or two fields, disappear...
Yes, it does work. Interpret this literally (consider each criteria
independently because of the OR). The second and third criteria may be
Click to expand...
[quoted text clipped - 21 lines]
Click to expand...
 
Private Sub OK_Click()
If Me!BegYR >= 1990 Then
If Me!BegYR <= 2015 Then
If Me!EndYR >= 1990 Then
If Me!EndYR <= 2015 Then
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub DATE_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub NAME_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub GOAL_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub LOCATION_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

etc...

for each button option, when clicked, the form is supposed to filter their
information from the query, and the query does list the criteria fields like
u had it:

[Forms]![ComboQ]![Date]
[Forms]![ComboQ]![Name] etc...
kingston via AccessMonster.com said:
The report and the query are two separate objects and won't necessarily
return the same results. Check the query results directly. How are you
using VBA in the form? The query can work directly from the form's controls
without VBA:

Field: [Name]
Criteria: [Forms]![FormName]![NameControl]
typo on line 3, forgot to add another field,... i'm new at this, and have yet
to see examples of same data fields being repeated on different rows for
different criteria, reread MS Access 2003 (Bible) over and over, and thought
that logic was correct, but it's not working for me,... unless my vba
statements in the form are not written correctly which is causing it not to
interpret the query the right way, and when i do print preview of the report,
some columns of data for one or two fields, disappear...
Yes, it does work. Interpret this literally (consider each criteria
independently because of the OR). The second and third criteria may be
Click to expand...
[quoted text clipped - 21 lines]
3. goal, name, title
etc...
Click to expand...
Click to expand...
Click to expand...
 
Do the queries return the results you expect? If so, the problem is with the
reports. If not, you'll have to modify the queries. The code you posted
shouldn't affect the query results; it looks like it's just a way to open the
appropriate reports.
Private Sub OK_Click()
If Me!BegYR >= 1990 Then
If Me!BegYR <= 2015 Then
If Me!EndYR >= 1990 Then
If Me!EndYR <= 2015 Then
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub DATE_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub NAME_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub GOAL_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub LOCATION_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

etc...

for each button option, when clicked, the form is supposed to filter their
information from the query, and the query does list the criteria fields like
u had it:

[Forms]![ComboQ]![Date]
[Forms]![ComboQ]![Name] etc...
The report and the query are two separate objects and won't necessarily
return the same results. Check the query results directly. How are you
Click to expand...
[quoted text clipped - 17 lines]
Click to expand...
 
I was scrolling through the discussion group on forms, .... i had attached my
form to a RECORD SOURCE, and it's not suppose to have one, ... hopefully that
is the cure, ...

accessdesigner said:
Private Sub OK_Click()
If Me!BegYR >= 1990 Then
If Me!BegYR <= 2015 Then
If Me!EndYR >= 1990 Then
If Me!EndYR <= 2015 Then
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub DATE_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub NAME_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub GOAL_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub LOCATION_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

etc...

for each button option, when clicked, the form is supposed to filter their
information from the query, and the query does list the criteria fields like
u had it:

[Forms]![ComboQ]![Date]
[Forms]![ComboQ]![Name] etc...
kingston via AccessMonster.com said:
The report and the query are two separate objects and won't necessarily
return the same results. Check the query results directly. How are you
using VBA in the form? The query can work directly from the form's controls
without VBA:

Field: [Name]
Criteria: [Forms]![FormName]![NameControl]
typo on line 3, forgot to add another field,... i'm new at this, and have yet
to see examples of same data fields being repeated on different rows for
different criteria, reread MS Access 2003 (Bible) over and over, and thought
that logic was correct, but it's not working for me,... unless my vba
statements in the form are not written correctly which is causing it not to
interpret the query the right way, and when i do print preview of the report,
some columns of data for one or two fields, disappear...

Yes, it does work. Interpret this literally (consider each criteria
independently because of the OR). The second and third criteria may be
[quoted text clipped - 21 lines]
3. goal, name, title
etc...
Click to expand...
Click to expand...
Click to expand...
 
Yes, the queries does post the correct results, when I added up to about 3
lines of criterias, after that, can't say what happened (along with me
pushing a lot of buttons, lol), ... right now, I am setting up the query like
we previously discussed, but with the exception of not including a control
source on the actual form property... BUT as you stated, the query logical
should be correct to beable to use the OR lines with duplicate field names
attached to different AND field names... we shall see...

kingston via AccessMonster.com said:
Do the queries return the results you expect? If so, the problem is with the
reports. If not, you'll have to modify the queries. The code you posted
shouldn't affect the query results; it looks like it's just a way to open the
appropriate reports.
Private Sub OK_Click()
If Me!BegYR >= 1990 Then
If Me!BegYR <= 2015 Then
If Me!EndYR >= 1990 Then
If Me!EndYR <= 2015 Then
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub DATE_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub NAME_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub GOAL_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

Private Sub LOCATION_Click()
Me.Visible = False
DoCmd.OpenReport "database1", acPreview

etc...

for each button option, when clicked, the form is supposed to filter their
information from the query, and the query does list the criteria fields like
u had it:

[Forms]![ComboQ]![Date]
[Forms]![ComboQ]![Name] etc...
The report and the query are two separate objects and won't necessarily
return the same results. Check the query results directly. How are you
Click to expand...
[quoted text clipped - 17 lines]
3. goal, name, title
etc...
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

Access Dcount (multiple criteria) 3
dlookup with multiple criteria 2
Multiple "Ors" in query 2
IIF statement in query criteria, help! 2
Need help please with making this SELECT statement work!!!!!! 5
Criteria in a Querie 8
Query to see what percentage of people scored a specific number 1
Problems with COUNT in Query 2

Back
Top