passing to criteria to a query

[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

 

[Jeff Boyce]

Please post the SQL statement of your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

Post the SQL of the query, please.

 

[Question Boy]

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]));

 

[Douglas J. Steele]

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!)

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]));

Please post the SQL statement of your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Question Boy]

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.

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!)

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]));

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