Multiple Conditions in IIf statement

[vjboaz]

I trying to update the value of Bulk based on the value in BulkA:

Bulk:
IIf((BulkA="0","0"),IIf((BulkA>"0") and
(BulkA<="0.0925"),"1/8"),IIf((BulkA>"0.0925") And (BulkA<="0.2800"),"1/4"),
IIf((BulkA>"0.2800") And (BulkA<="0.4050"),"3/8"), IIf((BulkA>"0.4050") And
(BulkA<="0.5300"),"1/2"), IIf((BulkA>"0.5300") And (BulkA<="0.6550"),"5/8"),
IIf((BulkA>"0.6550") And (BulkA<="0.7800"),"3/4"), IIf((BulkA>"0.7800") And
(BulkA<="0.9050"),"7/8"), IIf((BulkA>"0.9050") And
(BulkA<="1.030"),"1"), IIf((BulkA>"1.030") And
(BulkA<="1.1550"),"1-1/8"), IIf((BulkA>"1.1550") And
(BulkA<="1.2800"),"1-1/4"),IIf((BulkA>"1.2800") And
(BulkA<="1.5000"),"1-1/2"),IIF(BulkA>"1.5000","Bulk is greater than 1-1/2"))

I keep getting an error message about the arguments... what am I doing wrong?

Veronica

 

[Bruce]

Veronica,

I could not test this but believe this should corrrect your situation. If it
does not I would suggest you step through the process doing one IIF statement
at a time until you have exhausted your values. Give this a try:


IIf(BulkA="0","0",IIf(BulkA>"0" and BulkA<="0.0925","1/8",IIf(BulkA>"0.0925"
And BulkA<="0.2800","1/4",IIf(BulkA>"0.2800" And BulkA<="0.4050","3/8",
IIf(BulkA>"0.4050" And
BulkA<="0.5300","1/2",IIf(BulkA>"0.5300" And
BulkA<="0.6550","5/8",IIf(BulkA>"0.6550" And
BulkA<="0.7800","3/4",IIf(BulkA>"0.7800" And
BulkA<="0.9050","7/8",IIf(BulkA>"0.9050" And
BulkA<="1.030","1", IIf(BulkA>"1.030" And
BulkA<="1.1550","1-1/8",IIf(BulkA>"1.1550" And
BulkA<="1.2800","1-1/4",IIf(BulkA>"1.2800" And BulkA<="1.5000","1-1/2","Bulk
is greater than 1-1/2")))))))))))

 

[Jeff Boyce]

"error message" is pretty vague...

what does it say?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

One problem is that you do not have a closing parenthesis for every opening
parenthesis.
I think it would be better to use a translation table like this --
Min Max Bulk
0 0 0
0 0.0925 1/8
0.0925 0.2800 1/4
0.2800 0.4050 3/8
0.4050 0.5300 1/2
0.5300 0.6550 5/8
0.6550 0.7800 3/4
0.7800 0.9050 7/8
0.9050 1.030 1
1.030 1.1550 1-1/8
1.1550 1.2800 1-1/4
1.2800 1.5000 1-1/2
1.5000 99999 Bulk is greater than 1-1/2

Then in query design view, Translation table not joined, put field BulkA and
Bulk.
Critera for BulkA would be >[Min] And <=[Max].

 

[Jerry Whittle]

First off you shouldn't even have a Bulk field if you can derive it's value
based on the BulkA field. Instead you should base all forms, reports, and
queries on the BulkA field directly and 'translate' it into what you want to
see. For this a Case statement in a code module would be the best bet.

Function fStay(strStay As Variant) As String
Dim TheStay As String
Select Case strStay
Case Is 0
TheStay = "0"
Case "0" To "0.0925"
TheStay = "1/8"
Case "0.09251" To "0.2800"
TheStay = "1/4"
Case "0.28001" To "0.4050"
TheStay = "3/8"
Case Else ' Other values.
TheStay = "something fishy"
End Select
fStay = TheStay
End Function

Something like the above could put in a function and called by a query,
form, or report. One problem might be that your numbers seem to be text and
the 'TO' might not work the same way with text as numbers. You man need to
use a coversion function like CDbl on the text first.