passing to criteria to a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Question Boy - 24 Apr 2008 18:36 GMT

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

Reply to this Message

Jeff Boyce - 24 Apr 2008 18:47 GMT

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
[quoted text clipped - 12 lines]
>
> QB

Reply to this Message

Question Boy - 24 Apr 2008 18:58 GMT

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.
>
[quoted text clipped - 19 lines]
> >
> > QB

Reply to this Message

Douglas J. Steele - 24 Apr 2008 19:05 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> SELECT Count([dtApproR]) AS [on time]
> FROM ([tbl_Plan] INNER JOIN [qry_last approved] ON [tbl_Plan].PlanifIngId
[quoted text clipped - 29 lines]
>> >
>> > QB

Reply to this Message

Question Boy - 24 Apr 2008 19:44 GMT

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
>
[quoted text clipped - 47 lines]
> >> >
> >> > QB

Reply to this Message

Klatuu - 24 Apr 2008 18:49 GMT

Post the SQL of the query, please.

Signature

Dave Hargis, Microsoft 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.
[quoted text clipped - 9 lines]
>
> QB

Reply to this Message