4 combo boxes need and/or query for single or multiple sorts

G

Guest

Hi,

User needs to be able to sort from any single combo box or any combination
of all 4 boxes

The results are displayed in a list box

I have been able to get results where the records have all combinations but
i can't seem to figure how to get single and combination sorts combine to
display.

cbo1: orgs
cbo2: states
cbo3: processes
cbo4: packagaging

For example:
User wants to be see any process w/wo packaging info
if packaging info then user wants to be select from the packaging combo box.

Same for the other combo boxes

Below is one of the criteria (the check box is clear) on the query source
that I am using
IIf(IsNull([Forms]![form1]![cboProcess]),[tmakorgprocess].[processtypeid],[Forms]![form1]![cboprocess])

There is a criteria for all of the combo box fields.
I have a query with a left join tha will give the data I need, but when I
put in the criteria for the form. All I have is records with all 4
combinations


Please, Please help.
 
J

John Vinson

Hi,

User needs to be able to sort from any single combo box or any combination
of all 4 boxes
Click to expand...

Do you mean "sort" - define the sequential order of the records
returned by a query? or "search" - select which records to display?
I'm guessing you're making the common erroneous use of the term.
The results are displayed in a list box

I have been able to get results where the records have all combinations but
i can't seem to figure how to get single and combination sorts combine to
display.

cbo1: orgs
cbo2: states
cbo3: processes
cbo4: packagaging

For example:
User wants to be see any process w/wo packaging info
if packaging info then user wants to be select from the packaging combo box.

Same for the other combo boxes

Below is one of the criteria (the check box is clear) on the query source
that I am using
IIf(IsNull([Forms]![form1]![cboProcess]),[tmakorgprocess].[processtypeid],[Forms]![form1]![cboprocess])
Click to expand...

Try instead using criteria like

=[Forms]![form1]![cboProcess] OR [Forms]![form1]![cboProcess] IS NULL

With four combos this query will get REALLY ugly in query design view
but it should still work.

The alternative is to write VBA code to examine all four combo boxes,
and build up a SQL query string depending on which combo has been
selected.

John W. Vinson[MVP]
 
G

Guest

Thank you for your help.

I tried what you suggested for the query but did not get the desired results.

I am going to attempt to build the sql query. Wish me luck1

Thanks again
Subject: Re: 4 combo boxes need and/or query for single or multiple sorts
3/10/2006 9:49 AM PST

By: John Vinson In: microsoft.public.access.formscoding


Hi,

User needs to be able to sort from any single combo box or any combination
of all 4 boxes
Click to expand...

Do you mean "sort" - define the sequential order of the records
returned by a query? or "search" - select which records to display?
I'm guessing you're making the common erroneous use of the term.
The results are displayed in a list box

I have been able to get results where the records have all combinations but
i can't seem to figure how to get single and combination sorts combine to
display.

cbo1: orgs
cbo2: states
cbo3: processes
cbo4: packagaging

For example:
User wants to be see any process w/wo packaging info
if packaging info then user wants to be select from the packaging combo box.

Same for the other combo boxes

Below is one of the criteria (the check box is clear) on the query source
that I am using
IIf(IsNull([Forms]![form1]![cboProcess]),[tmakorgprocess].[processtypeid],[Forms]![form1]![cboprocess])
Click to expand...

Try instead using criteria like

=[Forms]![form1]![cboProcess] OR [Forms]![form1]![cboProcess] IS NULL


Subject: Re: 4 combo boxes need and/or query for single or multiple sorts
3/10/2006 9:49 AM PST

By: John Vinson In: microsoft.public.access.formscoding


Hi,

User needs to be able to sort from any single combo box or any combination
of all 4 boxes
Click to expand...

Do you mean "sort" - define the sequential order of the records
returned by a query? or "search" - select which records to display?
I'm guessing you're making the common erroneous use of the term.
The results are displayed in a list box

I have been able to get results where the records have all combinations but
i can't seem to figure how to get single and combination sorts combine to
display.

cbo1: orgs
cbo2: states
cbo3: processes
cbo4: packagaging

For example:
User wants to be see any process w/wo packaging info
if packaging info then user wants to be select from the packaging combo box.

Same for the other combo boxes

Below is one of the criteria (the check box is clear) on the query source
that I am using
IIf(IsNull([Forms]![form1]![cboProcess]),[tmakorgprocess].[processtypeid],[Forms]![form1]![cboprocess])
Click to expand...

Try instead using criteria like

=[Forms]![form1]![cboProcess] OR [Forms]![form1]![cboProcess] IS NULL



John Vinson said:
Hi,

User needs to be able to sort from any single combo box or any combination
of all 4 boxes
Click to expand...

Do you mean "sort" - define the sequential order of the records
returned by a query? or "search" - select which records to display?
I'm guessing you're making the common erroneous use of the term.
The results are displayed in a list box

I have been able to get results where the records have all combinations but
i can't seem to figure how to get single and combination sorts combine to
display.

cbo1: orgs
cbo2: states
cbo3: processes
cbo4: packagaging

For example:
User wants to be see any process w/wo packaging info
if packaging info then user wants to be select from the packaging combo box.

Same for the other combo boxes

Below is one of the criteria (the check box is clear) on the query source
that I am using
IIf(IsNull([Forms]![form1]![cboProcess]),[tmakorgprocess].[processtypeid],[Forms]![form1]![cboprocess])
Click to expand...

Try instead using criteria like

=[Forms]![form1]![cboProcess] OR [Forms]![form1]![cboProcess] IS NULL

With four combos this query will get REALLY ugly in query design view
but it should still work.

The alternative is to write VBA code to examine all four combo boxes,
and build up a SQL query string depending on which combo has been
selected.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

Thank you for your help.

I tried what you suggested for the query but did not get the desired results.

I am going to attempt to build the sql query. Wish me luck1
Click to expand...

All queries are in SQL - the query grid is just a tool to build SQL.

If you post your existing query's SQL and an explanation of what
undesired results you're getting, someone should be able to help.

John W. Vinson[MVP]
 
G

Guest

Please help.

Below is the sql
What I did is put the suggested criteria =[Forms]![form1]![cboProcess] OR
[Forms]![form1]![cboProcess] IS NULL
in each of the fields that corresponds to each combo boxes on the form.

However when the user selects the display results does not correspond to the
selection. All records are displayed.

SELECT tall.orgid, tall.orgname, [Process Type].ProcessTypeName,
tTestPackingType.PackingName, tOrgState.State
FROM ((((tall LEFT JOIN tmakOrgProcess ON tall.orgid = tmakOrgProcess.orgid)
LEFT JOIN tmakPackaging ON tall.orgid = tmakPackaging.orgid) LEFT JOIN
tOrgState ON tall.orgid = tOrgState.orgid) LEFT JOIN [Process Type] ON
tmakOrgProcess.processtypeid = [Process Type].[ProcessType ID]) LEFT JOIN
tTestPackingType ON tmakPackaging.packingtypeid =
tTestPackingType.PackingTypeID
WHERE (((tall.orgid)="Forms]![form1]![cboorg]") AND
((tOrgState.State)="Forms]![form1]![cbostate]") AND
((tmakOrgProcess.processtypeid)="Forms]![form1]![cboProcess]") AND
((tmakPackaging.packingtypeid)="Forms]![form1]![cboPacking]")) OR
(((tall.orgid)="Forms]![form1]![cboorg]") AND
((tmakOrgProcess.processtypeid)="Forms]![form1]![cboProcess]") AND
((tmakPackaging.packingtypeid)="Forms]![form1]![cboPacking]") AND
(([Forms]![form1]![cbostate]) Is Null)) OR
(((tmakOrgProcess.processtypeid)="Forms]![form1]![cboProcess]") AND
((tmakPackaging.packingtypeid)="Forms]![form1]![cboPacking]") AND
(([Forms]![form1]![cboorg]) Is Null)) OR
(((tall.orgid)="Forms]![form1]![cboorg]") AND
((tmakPackaging.packingtypeid)="Forms]![form1]![cboPacking]") AND
(([Forms]![form1]![cboProcess]) Is Null)) OR
(((tmakPackaging.packingtypeid)="Forms]![form1]![cboPacking]") AND
(([Forms]![form1]![cboorg]) Is Null) AND (([Forms]![form1]![cboProcess]) Is
Null)) OR (((tall.orgid)="Forms]![form1]![cboorg]") AND
((tmakOrgProcess.processtypeid)="Forms]![form1]![cboProcess]") AND
(([Forms]![form1]![cboPacking]) Is Null)) OR
(((tmakOrgProcess.processtypeid)="Forms]![form1]![cboProcess]") AND
(([Forms]![form1]![cboorg]) Is Null) AND (([Forms]![form1]![cboPacking]) Is
Null)) OR (((tall.orgid)="Forms]![form1]![cboorg]") AND
(([Forms]![form1]![cboProcess]) Is Null) AND (([Forms]![form1]![cboPacking])
Is Null)) OR ((([Forms]![form1]![cboorg]) Is Null) AND
(([Forms]![form1]![cboProcess]) Is Null) AND (([Forms]![form1]![cboPacking])
Is Null)) OR (((tOrgState.State)="Forms]![form1]![cbostate]")) OR
((([Forms]![form1]![cbostate]) Is Null))
ORDER BY tall.orgname;
 
J

John Vinson

Please help.

Below is the sql
What I did is put the suggested criteria =[Forms]![form1]![cboProcess] OR
[Forms]![form1]![cboProcess] IS NULL
in each of the fields that corresponds to each combo boxes on the form.
Click to expand...

No, you didn't. You put in quotemarks, which will search for records
where (say) the State field contains the text string
"Forms]![form1]![cboState]" - and that's obviously not going to be
there!

Try

SELECT tall.orgid, tall.orgname, [Process Type].ProcessTypeName,
tTestPackingType.PackingName, tOrgState.State
FROM ((((tall LEFT JOIN tmakOrgProcess ON tall.orgid =
tmakOrgProcess.orgid)
LEFT JOIN tmakPackaging ON tall.orgid = tmakPackaging.orgid) LEFT JOIN
tOrgState ON tall.orgid = tOrgState.orgid) LEFT JOIN [Process Type] ON
tmakOrgProcess.processtypeid = [Process Type].[ProcessType ID]) LEFT
JOIN
tTestPackingType ON tmakPackaging.packingtypeid =
tTestPackingType.PackingTypeID
WHERE ([tall].[orgid]=[Forms]![form1]![cboorg]
OR [Forms]![form1]![cboorg] IS NULL)
AND
(tOrgState.State=[Forms]![form1]![cbostate]
OR [Forms]![form1]![cbostate] IS NULL)
AND
(tmakOrgProcess.processtypeid=[Forms]![form1]![cboProcess]
OR [Forms]![form1]![cboProcess] IS NULL)
AND
(tmakPackaging.packingtypeid=[Forms]![form1]![cboPacking]
OR [Forms]![form1]![cboPacking] IS NULL)
ORDER BY tall.orgname;

I'd suggest putting this into the SQL window and NOT opening it in
Form Design view, or Access will make a hash of the parenthesis
nesting.

John W. Vinson[MVP]
 

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

How to create and/or combination queries to display in a form 1
Using a list box and combo boxes as criteria for a query 2
Filtering with 4 combo boxes. 1
Combo Boxes in Sync 2
query relelated to 2 combo boxes 2
Expression for Criteria for Form with Combo Boxes 1
Multiple Combo Boxes on Form 6
Filter Records with Multiple Combo Boxes 7

Top