[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

IIF looking at two different fields

Posted on 2011-03-23
5
Medium Priority
?
396 Views
Last Modified: 2012-05-11
I need to use an IIF that looks at two fields as in:

Expr2: IIf((Len([phone#])=7) And Mid$([TextField],1,4)='CRED',result1,result2)

all I get is result2
0
Comment
Question by:stirus
[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
  • Learn & ask questions
5 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 168 total points
ID: 35200772
Try with double quotes:
Expr2: IIf((Len([phone#])=7) And Mid$([TextField],1,4) = "CRED",result1,result2)

But are you SURE the condition is actually being met?

mx
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 168 total points
ID: 35200826
I agree with mx's last comment.

I think you are misreading your data.

Add the expressions temporarily as extra columns and see..

mylen:Len([phone#])

mymid: Mid$([TextField],1,4)
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 164 total points
ID: 35200959
Try this.  I moved the parentheses:

IIf((Len([phone#])=7 And Mid$([TextField],1,4)="CRED"),result1,result2)
0
 

Author Closing Comment

by:stirus
ID: 35201006
all three helped.  Thanks
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35201275
Thanks, glad to help.  Can you post Expr2 as you now have it?
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 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