trouble with yes/no/all combo box

[pubdude2003 via AccessMonster.com]

hey all,

trying to make a combo box that passes three possible values to the grid in a
query

Value list is
-1;"Ordered";0;"Not Ordered";1;"All"

in the grid
[Forms]![analysis]![Combo28] or [Forms]![analysis]![Combo28]=1

the field is a yes/no

but when I test it the 1 value in the combo box returns the -1 values from
the query, can't seem to get all

I have set the parameters in the query and the yes/no format on the combo box

any thoughts anyone?

 

[TC]

Why do you say:
[Forms]![analysis]![Combo28] >>> OR <<<
[Forms]![analysis]![Combo28]=1

Don't you just mean:
[Forms]![analysis]![Combo28]=1

?

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Douglas J. Steele]

What does the actual SQL look like?

 

[Abdul Basit]

Yes/No field can get only two values Yes (-1) or No (0). If you don't provide
any value it will assume No (0)

 

[pubdude2003 via AccessMonster.com]

shrug

it was code I had found on another posting, couldn't understand how that
worked but the original poster responded with "a thanks works perfectly!"

I guess the real request is, when passing a value to the Query grid for a
Yes/No field what value should I assign in the combo select box to return
both Yes AND No?

 

[pubdude2003 via AccessMonster.com]

eeak... the SQL is pretty brutal (and may just serve to complicate matters)
which is why I am passing a value from my form to the form's query in order
to filter the form records (not the best choice to filter records but too
complex to do otherwise). There are actually 4 queries involved to supply the
pertinent data to the main form and two sub forms

and thanks Abdul, I know I can only store two values in the field, what I am
attempting to do is filter a form's records by applying a value from a combo
box on the form to the form's query and then requerying the form

for what it's worth.......
PARAMETERS [Forms]![analysis]![Combo28] Bit;
SELECT [Master Printing Order Estimate].date, [Master Printing Order Estimate]
..[Job / Estimate Number], [Master Printing Order Estimate].[Customer Number],
[1 printing price].IncludeQ1Price, [1 printing price].OrdereredQ1Price, [1
printing price].ProfitQ1 AS ActualProfit, ([ActualProfit]>[QuotedProfit]) AS
MoreProfitThanQuoted, Categories.Categories AS Category, [1 printing price].
[Quoted 1 MPOE D]+[1 printing price].[Quoted 1 MPOE F]+[1 printing price].
[Quoted 1 MPOE P]+[1 printing price].[Quoted 1 MPOE B]+[1 printing price].
[Quoted 1 MPOE O]+[1 printing price].[Quoted 1 MPOE Db]+[1 printing price].
[Quoted 1 MPOE Fb]+[1 printing price].[Quoted 1 MPOE Pb]+[1 printing price].
[Quoted 1 MPOE Bb] AS TotalCostInQuote, ([ActualProfit]+[TotalCostInQuote])/
[TotalCostInQuote]-1 AS ActualMU, [Quoted Price 11]*[q 1] AS QuotedPrice,
[QuotedPrice]-[TotalCostInQuote] AS QuotedProfit, [1 printing price].[Quoted
1 MPOE MU]/100 AS QuotedMU, ([QuotedPrice])/[TotalCostInQuote]-1 AS
RealMarkUp, [1 printing price].ProfitQ1, ([1 printing price].[Quoted 1 MPOE D]
+[1 printing price].[Quoted 1 MPOE F]+[1 printing price].[Quoted 1 MPOE P]+[1
printing price].[Quoted 1 MPOE B]+[1 printing price].[Quoted 1 MPOE O]+[1
printing price].[Quoted 1 MPOE Db]+[1 printing price].[Quoted 1 MPOE Fb]+[1
printing price].[Quoted 1 MPOE Pb]+[1 printing price].[Quoted 1 MPOE Bb])*(1+
[1 printing price].[Quoted 1 MPOE MU]/100) AS TotalQuotedPrice, (NZ([1
printing price].[Quoted 1 MPOE D])+NZ([1 printing price].[Quoted 1 MPOE F])
+NZ([1 printing price].[Quoted 1 MPOE P])+NZ([1 printing price].[Quoted 1
MPOE B])+NZ([1 printing price].[Quoted 1 MPOE O])+NZ([1 printing price].
[Quoted 1 MPOE Db])+NZ([1 printing price].[Quoted 1 MPOE Fb])+NZ([1 printing
price].[Quoted 1 MPOE Pb])+NZ([1 printing price].[Quoted 1 MPOE Bb])) AS
[Total 1], [1 printing price].Q1Price, (IIf((DLookUp("Op1","UserPass"))=3,
[QP3]/1000,IIf((DLookUp("Op1","UserPass"))=2,[QP2]/1000,[QP1]/1000))) AS
[Quoted Price 1], Round([quoted price 1]+0.00001,4) AS [Quoted Price 11],
(DLookUp("Op1","UserPass")) AS Op1, [Master Printing Order Estimate].
[Customer Number], IIf([Q1Price] Is Not Null,([Q1Price]*1000),[Total 1]*(1+(
[1 printing price].[Quoted 1 MPOE MU]/100))/([1 printing price].[q 1]/1000))
AS QP1, IIf([Q1Price] Is Not Null,([Q1Price]*1000),([Total 1]-[1 printing
price].[Quoted 1 MPOE D])*(1+([1 printing price].[Quoted 1 MPOE MU]/100))/([1
printing price].[q 1]/1000)+([1 printing price].[Quoted 1 MPOE D])*(1+([1
printing price].[Quoted 8 MPOE Bb]/100))/([1 printing price].[q 1]/1000)) AS
QP2, IIf([Q1Price] Is Not Null,([Q1Price]*1000),([Total 1]-[1 printing price].
[Quoted 1 MPOE D]-[1 printing price].[Quoted 1 MPOE Bb])*(1+([1 printing
price].[Quoted 1 MPOE MU]/100))/([1 printing price].[q 1]/1000)+([1 printing
price].[Quoted 1 MPOE D])*(1+([1 printing price].[Quoted 8 MPOE Bb]/100))/([1
printing price].[q 1]/1000)+([1 printing price].[Quoted 1 MPOE Bb])*(1+([1
printing price].[Quoted 1 MPOE Ob]/100))/([1 printing price].[q 1]/1000)) AS
QP3, [1 printing price].[Q 1]
FROM [Master Printing Order Estimate] INNER JOIN (Categories INNER JOIN ([1
printing price] INNER JOIN [Master Printing / Order] ON [1 printing price].
[Job / Estimate Number] = [Master Printing / Order].[Job / Estimate Number])
ON Categories.auto = [1 printing price].Q1Desc) ON [Master Printing Order
Estimate].[Job / Estimate Number] = [1 printing price].[Job / Estimate Number]

WHERE ((([1 printing price].IncludeQ1Price)=-1) AND (([1 printing price].
OrdereredQ1Price)=[Forms]![analysis]![Combo28]))
ORDER BY [Master Printing Order Estimate].[Job / Estimate Number];

 

[pubdude2003 via AccessMonster.com]

Well not a terribly elegant solution, but I created a three value combo that
directed values to two checkbox controls and sent the values of those two
controls to an OR statement in the Query grid to get Yes, NO and Yes OR No