Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Ms Access IIF statement

Posted on 2011-09-28
Medium Priority
686 Views
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
0
Question by:BajanPaul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 12
• 8
• 5
• +3

LVL 93

Expert Comment

ID: 36719564
0

LVL 120

Expert Comment

ID: 36719566

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

LVL 48

Expert Comment

ID: 36719572
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

LVL 93

Expert Comment

ID: 36719585
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")

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.html
0

LVL 93

Expert Comment

ID: 36719589
0

LVL 48

Expert Comment

ID: 36719599
I like Switch() too, but in this case, where the values are all numeric, and sequential, I prefer to use the Choose() function.
0

LVL 48

Expert Comment

ID: 36719613

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

without the ?
0

Author Comment

ID: 36719635
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

LVL 12

Expert Comment

ID: 36719637

``````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
``````

Then in your control source put

=GetOrderType([Order Number])
0

Author Comment

ID: 36719712
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

LVL 120

Expert Comment

ID: 36719797
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

Author Comment

ID: 36719861
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

LVL 120

Expert Comment

ID: 36719879
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

Author Comment

ID: 36719889
It says...Invalid or Missing Expression
0

LVL 48

Expert Comment

ID: 36720488
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

LVL 120

Expert Comment

ID: 36736233
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

LVL 1

Expert Comment

ID: 36816432
IIf((Left([order number],4)=1),"S",(IIf((Left([order number],4)=2),"Othr",(IIf((Left([order number],4)=3),"R","P")))))
0

Author Comment

ID: 36816462
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

Author Comment

ID: 36816483
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

LVL 120

Expert Comment

ID: 36816505
BajanPaul,

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

Author Comment

ID: 36816718
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

LVL 48

Expert Comment

ID: 36816827
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

Author Comment

ID: 36816868
Sorry to say I get the same as above.
0

LVL 120

Expert Comment

ID: 36816898

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

did you get any result ?
0

Author Comment

ID: 36816930
This works.

SELECT      [Order Number], LEFT([Order Number], 1) AS OrderType
FROM          dbo.[qry_TotalTimeCalac_9-28-2011]
ThisWorks.png
0

LVL 12

Expert Comment

ID: 36817077
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

LVL 120

Expert Comment

ID: 36817105
i think think too, that you have to use "CASE" not IIF
0

Author Comment

ID: 36817244
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

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 36817338
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

Author Comment

ID: 36817401
Good god it worked!!!
0

Author Closing Comment

ID: 36817405
Thanks for sticking it out.  It worked perfectly.

Thanks again.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question