Solved

Ms Access IIF statement

Posted on 2011-09-28
31
642 Views
Last Modified: 2012-05-12
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
Comment
Question by:BajanPaul
  • 12
  • 8
  • 5
  • +3
31 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36719564
What about those leading zeroes?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 47

Expert Comment

by:Dale Fye (Access MVP)
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 92

Expert Comment

by:Patrick Matthews
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")

For more about Switch:

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

Expert Comment

by:Patrick Matthews
ID: 36719589
Hm, completely forgot about Choose...
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36719613
oops, should have read:

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

without the ?
0
 

Author Comment

by:BajanPaul
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

by:pdebaets
ID: 36719637
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
 

Author Comment

by:BajanPaul
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

by:Rey Obrero (Capricorn1)
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

by:BajanPaul
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

by:Rey Obrero (Capricorn1)
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

by:BajanPaul
ID: 36719889
It says...Invalid or Missing Expression
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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

by:Rey Obrero (Capricorn1)
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

by:MzLiberty22
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

by:BajanPaul
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

by:BajanPaul
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

by:Rey Obrero (Capricorn1)
ID: 36816505
BajanPaul,

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

Author Comment

by:BajanPaul
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 47

Expert Comment

by:Dale Fye (Access MVP)
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

by:BajanPaul
ID: 36816868
Sorry to say I get the same as above.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36816898
how about this


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


did you get any result ?
0
 

Author Comment

by:BajanPaul
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

by:pdebaets
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

by:Rey Obrero (Capricorn1)
ID: 36817105
i think think too, that you have to use "CASE" not IIF
0
 

Author Comment

by:BajanPaul
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

by:
Rey Obrero (Capricorn1) earned 500 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

by:BajanPaul
ID: 36817401
Good god it worked!!!
0
 

Author Closing Comment

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

Thanks again.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question