Incorrect iif statement, need some correction

[haerion]

Hi all,

I'm trying to get an iif statement that would give me back all the data that got 1 or 600, if the "1-600" is selected, 299 or 999 if the "désuet" is selected and check in the txtCat if neither is selected to get the number inside the textbox. If i put 600 its work, but if i try 600 or 1, it doesn't work anymore, same for the 299-999. Is it the way i write it? or it just doesn't work this way?
if i would put 1 or 600 in the criterai without formula and everything it would report these 2, buit when in an iif statement, nothing...

Thanks for your help and here is the code:

INSERT INTO [PPA en traitement] ( STITEMNO, ITEMNO, ST, CAT, OH, OO, COM, TRSF, YTD, Cost, PYTD )
SELECT [ST] & [ITEMNO] AS stitemno, INVMSTR.ITEMNO, INVSTORE.ST, INVMSTR.PRO, INVSTORE.[QTY ON HAND], INVSTORE.[QTY ON ORDER], INVSTORE.[QTY COMMITTED], INVSTORE.[TRANSFER QTY], INVSTORE.[UNITS YTD], INVSTORE.AVGCOST, PURCHHST.[UNITS Y-T-D]
FROM (Clerks RIGHT JOIN INVMSTR ON Clerks.[Clerk#]=INVMSTR.PAGE) INNER JOIN (INVSTORE LEFT JOIN PURCHHST ON (INVSTORE.ST=PURCHHST.LC) AND (INVSTORE.ITEMNO1=PURCHHST.[ITEM  NUMBER])) ON INVMSTR.ITEMNO=INVSTORE.ITEMNO1
WHERE ((INVMSTR.PRO)=IIf(forms![Main Form Info]!txtReportType="1-600",1 or 600,IIf(forms![Main Form Info]!txtReportType="désuet",299 Or 999,IIf(Trim(forms![Main Form Info]!txtCat & "")="",INVMSTR.PRO,forms![Main Form Info]!txtCat)))) And (([QTY ON HAND]>=forms![Main Form Info]!txtOH And forms![Main Form Info]!chkOH=1) Or ([QTY ON HAND]=forms![Main Form Info]!txtOH And forms![Main Form Info]!chkOH=2)) And (([UNITS YTD]>=forms![Main Form Info]!txtSoldYTD And forms![Main Form Info]!chkYTD=1) Or ([UNITS YTD]=forms![Main Form Info]!txtSoldYTD And forms![Main Form Info]!chkYTD=2)) And (([UNITS Y-T-D]>=forms![Main Form Info]!txtPurchYTD And forms![Main Form Info]!chkPurchYTD=1) Or ([UNITS Y-T-D]=forms![Main Form Info]!txtPurchYTD And forms![Main Form Info]!chkPurchYTD=2))
ORDER BY INVMSTR.ITEMNO, INVSTORE.ST;

Haerion

 

[PHV]

A starting point:

WHERE ((INVMSTR.PRO IN (1,600) AND [Forms]![Main Form Info]!txtReportType='1-600')
    OR (INVMSTR.PRO IN (299,999) AND [Forms]![Main Form Info]!txtReportType='désuet')
    OR INVMSTR.PRO=[Forms]![Main Form Info]!txtCat
    OR [Forms![Main Form Info]!txtCat & ''='')
AND ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rac2]

Yeah, it doesnt work that way.

...
WHERE
  ( INVMSTR.PRO BETWEEN IIf(forms![Main Form Info]!txtReportType="1-600", 1,
                          IIf(forms![Main Form Info]!txtReportType="désuet", 299, 0)
                            )
                AND
    INVMSTR.PRO BETWEEN IIf(forms![Main Form Info]!txtReportType="1-600", 600,
                          IIf(forms![Main Form Info]!txtReportType="désuet", 999, 0)
                            )
  )

  OR

  (  Trim(forms![Main Form Info]!txtCat & "") = ""   
       AND INVMSTR.PRO = forms![Main Form Info]!txtCat
  )

...

The above is intended to show you an approach to writing the conditions you wish. Here is the idea.

There are two kinds of conditions to be tested.
First-
If the form data is a value such as "1 - 600" or "désuet"
then the condition is of the type
INVMSTR.PRO BETWEEN low_value AND high_value
For example, if the form data is "désuet" then the condition is

WHERE INVMSTR.PRO BETWEEN 299 AND 999

Second-
If the form data is some other value then the condition is of the type
INVMSTR.PRO = some_value

WHERE INVMSTR.PRO = 409

These can be put into the WHERE clause by combining the two types with OR . Suppose the search form has a specific value such as 409. The IIf() will evaluate to

WHERE INVMSTR.PRO BETWEEN 0 AND 0 
   OR INVMSTR.PRO = 409

If the input value is "1 - 600" the IIf() will evaluate to

WHERE INVMSTR.PRO BETWEEN 1 AND 600 
   OR INVMSTR.PRO = "1 - 600"

There is a bit of a problem here with the datatype of INVMSTR.PRO. Is it text or number?

 

[haerion]

number

 

[rac2]

That is awkward. Probably should not allow text data in a form field which is used with a numeric column. You might think about revising the form to have different fields for the ranges and the specific value.

Or add another IIf() in the specific value condition to evaluate to -1 or some other number which is not used in the table.

 

[haerion]

Hi rac2,

Sorry for the late answer, was kind of busy with all the other work around.

But yes, I think i will revise the fields in the form to a simpler one, it was too complicate for nothing. So in the end I just had 2 txtbox, one for the minimum and one for the maximum and will get a between formula.

thanks you all for you help, you too PHV, you gave me some good formula to use

Have a nice day guys