muliple user defined query

G

Gerry H

I am trying to set up multiple user defined criteria, defined through a form
and combo boxes to ensure no mispelling.

I have managed to set up a form with one combo box and a query which pulls
the criteria from it. If I add a second combo box for another field, the
query ignors it.

What am I doing wrong!
 
J

Jeff Boyce

Gerry

You and I may have a different definition of "multiple use defined
criteria". Do you mean multiple selection criteria, as selected/defined by
the (single) user?

You did not include a copy of the SQL statement (i.e., the query) you are
using. If I had to hazard a guess, your query doesn't reference the second
combo box...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

Dirk Goldgar

In
Gerry H said:
I am trying to set up multiple user defined criteria, defined through
a form and combo boxes to ensure no mispelling.

I have managed to set up a form with one combo box and a query which
pulls the criteria from it. If I add a second combo box for another
field, the query ignors it.

What am I doing wrong!
Click to expand...

You'll need to post the details of the form and control names, the SQL
of the query, and any code that may be involved. Otherwise, anyone here
would just be guessing.
 
G

Gerry H

Sorry I am new to this forum (and to access)

basically, i have a number of tables of defined lists.

I use these through a form and combo boxes to let the user choose which item
applies to the entry. IE project A is in County X and is of type Y. X & Y
being defind lists.

I am trying to set up a report that the user can pick from combo boxes the
criteria to match the search he wants. IE what projects are in County X and
are of type Y.

I have gotten as far as setting up the form and macro for the OK button etc
and have built a query.

I have linked the criteria field to the combo box which works succesfully
when i only define one criteria IE what projects are in county X. But when I
add a second does not carry out the query correct.

As I am new to this, I am probable doing it all wrong.

Thanks for the help
 
D

Dirk Goldgar

In
Gerry H said:
Sorry I am new to this forum (and to access)

basically, i have a number of tables of defined lists.

I use these through a form and combo boxes to let the user choose
which item applies to the entry. IE project A is in County X and is
of type Y. X & Y being defind lists.

I am trying to set up a report that the user can pick from combo
boxes the criteria to match the search he wants. IE what projects are
in County X and are of type Y.

I have gotten as far as setting up the form and macro for the OK
button etc and have built a query.

I have linked the criteria field to the combo box which works
succesfully when i only define one criteria IE what projects are in
county X. But when I add a second does not carry out the query
correct.

As I am new to this, I am probable doing it all wrong.
Click to expand...

Not necessarily. What you describe doesn't sound like a bad approach.
Am I right in thinking the OK button's macro just opens the report? If
so, probably the fault is in the query. Please post the SQL of the
query.
 
G

Gerry H

Many Thanks,

SQL

SELECT tbl_PROJECTS.[Project ID], tbl_PROJECTS.[Project Name],
tbl_PROJECTS.[Start Date], tbl_PROJECTS.[End Date], tbl_PROJECTS.Client,
tbl_PROJECTS.[Road Type], tbl_PROJECTS.[Rural Urban ID],
tbl_PROJECTS.[Location ID], tbl_PROJECTS.[Contract Type], tbl_PROJECTS.[Const
Cost], tbl_PROJECTS.[Project Description]
FROM [tbl_Services Available] INNER JOIN (tbl_PROJECTS INNER JOIN
[tbl_Services Provided] ON tbl_PROJECTS.[Project ID] = [tbl_Services
Provided].[Project ID]) ON [tbl_Services Available].[Services ID] =
[tbl_Services Provided].[RPS Services]
WHERE (((tbl_PROJECTS.[Rural Urban ID]) Like [Forms]![frm_gerry]![Combo36])
AND ((tbl_PROJECTS.[Location ID]) Like [Forms]![frm_gerry]![Combo38])) OR
((([Forms]![frm_gerry]![Combo36]) Is Null) AND
(([Forms]![frm_gerry]![Combo38]) Is Null));
 
D

Dirk Goldgar

In
Gerry H said:
SQL

SELECT tbl_PROJECTS.[Project ID], tbl_PROJECTS.[Project Name],
tbl_PROJECTS.[Start Date], tbl_PROJECTS.[End Date],
tbl_PROJECTS.Client, tbl_PROJECTS.[Road Type], tbl_PROJECTS.[Rural
Urban ID], tbl_PROJECTS.[Location ID], tbl_PROJECTS.[Contract Type],
tbl_PROJECTS.[Const Cost], tbl_PROJECTS.[Project Description]
FROM [tbl_Services Available] INNER JOIN (tbl_PROJECTS INNER JOIN
[tbl_Services Provided] ON tbl_PROJECTS.[Project ID] = [tbl_Services
Provided].[Project ID]) ON [tbl_Services Available].[Services ID] =
[tbl_Services Provided].[RPS Services]
WHERE (((tbl_PROJECTS.[Rural Urban ID]) Like
[Forms]![frm_gerry]![Combo36]) AND ((tbl_PROJECTS.[Location ID]) Like
[Forms]![frm_gerry]![Combo38])) OR ((([Forms]![frm_gerry]![Combo36])
Is Null) AND (([Forms]![frm_gerry]![Combo38]) Is Null));
Click to expand...

It seems to me you have the wrong grouping and conjunctions in your
WHERE clause. Try replacing that part of the SQL with this:

WHERE
(
tbl_PROJECTS.[Rural Urban ID] =
[Forms]![frm_gerry]![Combo36]
OR
[Forms]![frm_gerry]![Combo36] Is Null
)
AND
(
tbl_PROJECTS.[Location ID] =
[Forms]![frm_gerry]![Combo38]
OR
[Forms]![frm_gerry]![Combo38] Is Null
);

I've formatted the clause for easier reading and removed extraneous
parentheses, but that won't bother the query designer. I've also
replaced your "Like" operators with "=", since there's no point in using
"Like" if you're not going to use wild-card characters.
 
G

Gerry H

Worked a treat, many thanks

Dirk Goldgar said:
In
Gerry H said:
SQL

SELECT tbl_PROJECTS.[Project ID], tbl_PROJECTS.[Project Name],
tbl_PROJECTS.[Start Date], tbl_PROJECTS.[End Date],
tbl_PROJECTS.Client, tbl_PROJECTS.[Road Type], tbl_PROJECTS.[Rural
Urban ID], tbl_PROJECTS.[Location ID], tbl_PROJECTS.[Contract Type],
tbl_PROJECTS.[Const Cost], tbl_PROJECTS.[Project Description]
FROM [tbl_Services Available] INNER JOIN (tbl_PROJECTS INNER JOIN
[tbl_Services Provided] ON tbl_PROJECTS.[Project ID] = [tbl_Services
Provided].[Project ID]) ON [tbl_Services Available].[Services ID] =
[tbl_Services Provided].[RPS Services]
WHERE (((tbl_PROJECTS.[Rural Urban ID]) Like
[Forms]![frm_gerry]![Combo36]) AND ((tbl_PROJECTS.[Location ID]) Like
[Forms]![frm_gerry]![Combo38])) OR ((([Forms]![frm_gerry]![Combo36])
Is Null) AND (([Forms]![frm_gerry]![Combo38]) Is Null));
Click to expand...

It seems to me you have the wrong grouping and conjunctions in your
WHERE clause. Try replacing that part of the SQL with this:

WHERE
(
tbl_PROJECTS.[Rural Urban ID] =
[Forms]![frm_gerry]![Combo36]
OR
[Forms]![frm_gerry]![Combo36] Is Null
)
AND
(
tbl_PROJECTS.[Location ID] =
[Forms]![frm_gerry]![Combo38]
OR
[Forms]![frm_gerry]![Combo38] Is Null
);

I've formatted the clause for easier reading and removed extraneous
parentheses, but that won't bother the query designer. I've also
replaced your "Like" operators with "=", since there's no point in using
"Like" if you're not going to use wild-card characters.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
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

Creating a Search using a combo box and then creating a report. 4
Dynamic Naming of Form Button 3
How to implement two shifts per day with multiple teams 0
Open a form from a combobox on the switchboard based on a field inthe subform 1
VBA concatenate ranges into a new document 0
Combo Box Question 4
Parameter Query With Null Values 3
Criteria in Querry to include All and Empty Field 9

Top