Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Ms Access IIF statement

Posted on 2011-09-28
31
Medium Priority
?
701 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 93

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 49

Expert Comment

by:Dale Fye
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 93

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 93

Expert Comment

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

Expert Comment

by:Dale Fye
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 49

Expert Comment

by:Dale Fye
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 49

Expert Comment

by:Dale Fye
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 49

Expert Comment

by:Dale Fye
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 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

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

926 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