Solved

Ms Access IIF statement

Posted on 2011-09-28
31
628 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 119

Expert Comment

by:Rey Obrero
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
 
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now