Solved

Ms Access IIF statement

Posted on 2011-09-28
31
651 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
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…

856 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