Ms Access IIF statement

To all Ms Access Guru's,
How do I make this FUNCTION work in Access:

'=IIF(LEFT([Order Number],1)="1","S","P")'

I am trying to look at [Order Number], the first number and if it Equals 1 then put a "S" if not put "P"...What I really need this function to do is evaluate the first number
if it is a "1" put "S",
if it is a "2" put "Othr",
if it is a "3" put "R"  
if it is a "4" put "P"
OrderNumber.png
BajanPaulAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
remove the comma {,} after  END

      SELECT
         [Order Number],
       
              CASE LEFT([Order Number], 1)
                   WHEN '1' THEN 'S'
                   WHEN '2' THEN 'Othr'
                   WHEN '3' THEN 'R'
                        WHEN '4' THEN 'P'
                   ELSE '?'
              END As OrderType

    FROM          dbo.[qry_TotalTimeCalac_9-28-2011];
0
 
Patrick MatthewsCommented:
What about those leading zeroes?
0
 
Rey Obrero (Capricorn1)Commented:

test this

=IIF(LEFT(Val([Order Number]),1)="1","S", IIF(LEFT(Val([Order Number]),1)="2","Othr",IIF(LEFT(Val([Order Number]),1)="3", "R", "P")))
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Dale FyeCommented:
If your left 3 characters will always be 0, or if you just want to evaluate the 4th character you could use:

=?choose(val(Mid([Order Number], 4, 1)), "S", "Othr", "R", "P")
0
 
Patrick MatthewsCommented:
Will it always be 3 leading zeroes, or will it sometimes be different?

Or is really the 4th character that you need to check?

In any event, embedded IIf statements make my head hurt.  I prefer:

=Switch(Left(Val([Order Number]), 1) = "1", "S", Left(Val([Order Number]), 1) = "2", "Othr", Left(Val([Order Number]), 1) = "3", "R", Left(Val([Order Number]), 1) = "4", "P")

That will, of course, return Null if there are no matches.  You could also do:

=Switch(Left(Val([Order Number]), 1) = "1", "S", Left(Val([Order Number]), 1) = "2", "Othr", Left(Val([Order Number]), 1) = "3", "R", Left(Val([Order Number]), 1) = "4", "P", True, "None of the above")

For more about Switch:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.html
0
 
Patrick MatthewsCommented:
Hm, completely forgot about Choose...
0
 
Dale FyeCommented:
I like Switch() too, but in this case, where the values are all numeric, and sequential, I prefer to use the Choose() function.
0
 
Dale FyeCommented:
oops, should have read:

=choose(val(Mid([Order Number], 4, 1)), "S", "Othr", "R", "P")

without the ?
0
 
BajanPaulAuthor Commented:
I am in the process of trying to eliminate the leading zeros, but having issues with it in SQL. SQL does not like Val for some reason.
0
 
pdebaetsCommented:
How about a simple function.

Function GetOrderType(pstrOrderNumber As String)
Dim rtn As String
Select Case Left(CStr(CLng(pstrOrderNumber)), 1)
Case 1: rtn = "S"
Case 2: rtn = "Othr"
Case 3: rtn = "R"
Case 4: rtn = "P"
Case Else
End Select
GetOrderType = rtn
End Function

Open in new window


Then in your control source put

=GetOrderType([Order Number])
0
 
BajanPaulAuthor Commented:
I trimmed out all the leading zeros, and removed the Val and stillI could not get this to work
'=IIF(LEFT([Order Number]),1)=[1],[S], IIF(LEFT([Order Number]),1)=[2],[Othr],IIF(LEFT([Order Number]),1)=[3], [R], [P])))'
0
 
Rey Obrero (Capricorn1)Commented:
BajanPaul,

 COPY AND PASTE


=IIF(LEFT([Order Number]),1)="1","S", IIF(LEFT([Order Number]),1)="2","Othr",IIF(LEFT([Order Number]),1)="3", "R", "P")))


or this

=IIF(LEFT([Order Number]),1)=1,"S", IIF(LEFT([Order Number]),1)=2,"Othr",IIF(LEFT([Order Number]),1)=3, "R", "P")))
0
 
BajanPaulAuthor Commented:
Capricorn1

I COPIED AND PASTED as you suggested and for some reason still can get it to work.  Please see my snapshot below.
OrderNumber2.png
0
 
Rey Obrero (Capricorn1)Commented:
remove the  "="


IIF(LEFT([Order Number]),1)="1","S", IIF(LEFT([Order Number]),1)="2","Othr",IIF(LEFT([Order Number]),1)="3", "R", "P")))
0
 
BajanPaulAuthor Commented:
It says...Invalid or Missing Expression
0
 
Dale FyeCommented:
It looks like your are trying to do this in a query of a SQL Server data table, is that the case?  Can you post the entire SQL statement of the Query?
0
 
Rey Obrero (Capricorn1)Commented:
test this query, copy and paste

select [Order Number],IIF(LEFT([Order Number],1)="1","S", IIF(LEFT([Order Number],1)="2","Othr",IIF(LEFT([Order Number],1)="3", "R", "P"))) AS OrderType
From TableX


change TableX with actual name of the table
0
 
MzLiberty22Commented:
IIf((Left([order number],4)=1),"S",(IIf((Left([order number],4)=2),"Othr",(IIf((Left([order number],4)=3),"R","P")))))
0
 
BajanPaulAuthor Commented:
MzLiberty22:

It give me this error INVALID OR MISSING EXPRESSION. If I put an equal sign in front of the IIF, it will convert into a string
0
 
BajanPaulAuthor Commented:
I've been able to make this work so far:
LEFT ([Order Number], 1)...this will return all the numbers..What I cant understand is why I cant make the IIF work. Does anyone know if the database type Ms Access (.ADP) has anything to do with it?
0
 
Rey Obrero (Capricorn1)Commented:
BajanPaul,

did you try to make a new query using the sql statement from post http:#a36736233 ?
0
 
BajanPaulAuthor Commented:
capricorn1:

SELECT      [Order Number], IIF(LEFT([Order Number], 1) = [1], [S], IIF(LEFT([Order Number], 1) = [2], [Othr], IIF(LEFT([Order Number], 1) = [3], [R], [P])))
                        AS OrderType
FROM          dbo.[qry_TotalTimeCalac_9-28-2011]
Query.png
0
 
Dale FyeCommented:
Since you are working with SQL Server, try:

select [Order Number],IIF(LEFT([Order Number],1)='1','S', IIF(LEFT([Order Number],1)='2','Othr',IIF(LEFT([Order Number],1)='3', 'R', 'P'))) AS OrderType
From TableX

This is capricorn1's query with quotes replaced by apostrophes.

0
 
BajanPaulAuthor Commented:
Sorry to say I get the same as above.
0
 
Rey Obrero (Capricorn1)Commented:
how about this


select [Order Number], LEFT([Order Number],1)
From TableX


did you get any result ?
0
 
BajanPaulAuthor Commented:
This works.

SELECT      [Order Number], LEFT([Order Number], 1) AS OrderType
FROM          dbo.[qry_TotalTimeCalac_9-28-2011]
ThisWorks.png
0
 
pdebaetsCommented:
Are you running this query in SQL Server or Access? Is this a pass-through query? There is no "iif" in SQL Server's Transact SQL Language. If you're using SQL Server or a pass-through query from Access, please try:

      SELECT
         [Order Number],
        OrderType =
              CASE LEFT([Order Number], 1)
                   WHEN '1' THEN 'S'
                   WHEN '2' THEN 'Othr'
                   WHEN '3' THEN 'R'
                        WHEN '4' THEN 'P'
                   ELSE '?'
              END,

    FROM          dbo.[qry_TotalTimeCalac_9-28-2011];


0
 
Rey Obrero (Capricorn1)Commented:
i think think too, that you have to use "CASE" not IIF
0
 
BajanPaulAuthor Commented:
pdebaets:
This is a pass through query I believe.  

Error in list of function arguments: 'FROM' not recognized.
Missing FROM clause.
Unable to parse query text.
0
 
BajanPaulAuthor Commented:
Good god it worked!!!
0
 
BajanPaulAuthor Commented:
Thanks for sticking it out.  It worked perfectly.

Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.