?
Solved

IIF looking at two different fields

Posted on 2011-03-23
5
Medium Priority
?
395 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 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