passing to criteria to a query

  • Thread starter Thread starter Question Boy
  • Start date Start date
Q

Question Boy

I have a query in which I set the criteria of a field to the value of a combo
box on my form.

On the form, the combo box has three values
A
B
A Or B

When the 1st 2 are used the query run properly. However the third does not
(return nothing, although individually they do). What must I change so that
it will accept multiple criteria?

QB
 
Please post the SQL statement of your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
SELECT Count([dtApproR]) AS [on time]
FROM ([tbl_Plan] INNER JOIN [qry_last approved] ON [tbl_Plan].PlanifIngId =
[qry_Approved].PlanifIngId) INNER JOIN [PARAMETRE] ON [tbl_Plan].ProNo =
[PARAMETRE].[Number]
WHERE ((([tbl_Plan].dtApproR)=[DateApproP]) AND ((IIf([Type
A]=True,"A",IIf([Type B]=True,"B","C"))) Like [Forms]![frm_rpt_Stats Appro et
Fab]![Combo0]));
 
Try

SELECT Count([dtApproR]) AS [on time]
FROM ([tbl_Plan] INNER JOIN [qry_last approved] ON [tbl_Plan].PlanifIngId =
[qry_Approved].PlanifIngId) INNER JOIN [PARAMETRE] ON [tbl_Plan].ProNo =
[PARAMETRE].[Number]
WHERE [tbl_Plan].dtApproR=[DateApproP]
AND (([Type A]=True AND [Forms]![frm_rpt_Stats Appro et Fab]![Combo0] IN
("A", "A or B"))
OR ([Type B=True AND [Forms]![frm_rpt_Stats Appro et Fab]![Combo0] IN ("B,
"A or B")))

BTW, the fact that you've got fields named Type A and Type B is usually
indicative of the fact that you haven't correctly normalized your tables.
Check some of the resources Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
to learn how to correct your design.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Question Boy said:
SELECT Count([dtApproR]) AS [on time]
FROM ([tbl_Plan] INNER JOIN [qry_last approved] ON [tbl_Plan].PlanifIngId
=
[qry_Approved].PlanifIngId) INNER JOIN [PARAMETRE] ON [tbl_Plan].ProNo =
[PARAMETRE].[Number]
WHERE ((([tbl_Plan].dtApproR)=[DateApproP]) AND ((IIf([Type
A]=True,"A",IIf([Type B]=True,"B","C"))) Like [Forms]![frm_rpt_Stats Appro
et
Fab]![Combo0]));


Jeff Boyce said:
Please post the SQL statement of your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
 
Ya I know. I took over someone else's work and the boss doesn't want me to
spend time fixing such issues. It works, don't touch....

Isn't the first time it creates problems for me. I spent more time working
around it then solving it.




Douglas J. Steele said:
Try

SELECT Count([dtApproR]) AS [on time]
FROM ([tbl_Plan] INNER JOIN [qry_last approved] ON [tbl_Plan].PlanifIngId =
[qry_Approved].PlanifIngId) INNER JOIN [PARAMETRE] ON [tbl_Plan].ProNo =
[PARAMETRE].[Number]
WHERE [tbl_Plan].dtApproR=[DateApproP]
AND (([Type A]=True AND [Forms]![frm_rpt_Stats Appro et Fab]![Combo0] IN
("A", "A or B"))
OR ([Type B=True AND [Forms]![frm_rpt_Stats Appro et Fab]![Combo0] IN ("B,
"A or B")))

BTW, the fact that you've got fields named Type A and Type B is usually
indicative of the fact that you haven't correctly normalized your tables.
Check some of the resources Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
to learn how to correct your design.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Question Boy said:
SELECT Count([dtApproR]) AS [on time]
FROM ([tbl_Plan] INNER JOIN [qry_last approved] ON [tbl_Plan].PlanifIngId
=
[qry_Approved].PlanifIngId) INNER JOIN [PARAMETRE] ON [tbl_Plan].ProNo =
[PARAMETRE].[Number]
WHERE ((([tbl_Plan].dtApproR)=[DateApproP]) AND ((IIf([Type
A]=True,"A",IIf([Type B]=True,"B","C"))) Like [Forms]![frm_rpt_Stats Appro
et
Fab]![Combo0]));


Jeff Boyce said:
Please post the SQL statement of your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a query in which I set the criteria of a field to the value of a
combo
box on my form.

On the form, the combo box has three values
A
B
A Or B

When the 1st 2 are used the query run properly. However the third does
not
(return nothing, although individually they do). What must I change so
that
it will accept multiple criteria?

QB
Click to expand...
Click to expand...
Click to expand...
 
Back
Top