By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,154 Members | 2,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,154 IT Pros & Developers. It's quick & easy.

Help with IIF expression in query

newnewbie
P: 54
I have a query where I need Access to evaluate the "Name" field and return messages "Batch" or "Desktop". The "Name" field contains unique values like

A0612041843574124983.tif#http://fl-jax-sharp/sites% and
2006112200039CIN218.TIF#http://fl-jax-sharp/sites%

Values starting with A are "Desktop", starting with 2006 - "Batch"

I tried IIF expression

IIF([Name]="A*","Desktop", "Batch") and it did not work. My guess is that IIF can only work with exact values in the field (e.g. A, 2006) and cannot incorporate the "starts with" expression....

I also tried exporting the query results in Excel and using IF(A2=A06*,"Desktop", "Batch") and it does not work either for the same reason - it needs exact value and cannot deal with the values"starting with". Or maybe I just do not know and should use some other formula/expression.

I am very new with Access, mostly learning on the job. Please help.

Lena
Dec 5 '06 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,669
I have a query where I need Access to evaluate the "Name" field and return messages "Batch" or "Desktop". The "Name" field contains unique values like

A0612041843574124983.tif#http://fl-jax-sharp/sites% and
2006112200039CIN218.TIF#http://fl-jax-sharp/sites%

Values starting with A are "Desktop", starting with 2006 - "Batch"

I tried IIF expression

IIF([Name]="A*","Desktop", "Batch") and it did not work. My guess is that IIF can only work with exact values in the field (e.g. A, 2006) and cannot incorporate the "starts with" expression....

I also tried exporting the query results in Excel and using IF(A2=A06*,"Desktop", "Batch") and it does not work either for the same reason - it needs exact value and cannot deal with the values"starting with". Or maybe I just do not know and should use some other formula/expression.

I am very new with Access, mostly learning on the job. Please help.

Lena
'Create a Calculated Field and for its value set it to a Public Function with a Name Argument. Analyze the Name within the Function and return the appropriate result. The Calculated Field would look like:

Status: fAnalyzeName([Name]) 'Function returns correct Status based on Name passed to it

NOTE: If the need the Function & Code, please let me known. I'll be glad to
assist...
Dec 5 '06 #2

NeoPa
Expert Mod 15k+
P: 31,616
Try :
Expand|Select|Wrap|Line Numbers
  1. IIF([Name] Like "A*","Desktop", "Batch") 
or, if using ANSI-92 compatibility :
Expand|Select|Wrap|Line Numbers
  1. IIF([Name] Like "A%","Desktop", "Batch") 
Dec 5 '06 #3

newnewbie
P: 54
IIF([Name] Like "A*","Desktop", "Batch") (without a space before "Batch" WORKED!!!!
THANK YOU!
Lena
Dec 6 '06 #4

newnewbie
P: 54
This is a little bit beyond my understanding of Access yet :) I tried to create a field, but it did not work for me....Anyways, the IIF function worked :)

'Create a Calculated Field and for its value set it to a Public Function with a Name Argument. Analyze the Name within the Function and return the appropriate result. The Calculated Field would look like:

Status: fAnalyzeName([Name]) 'Function returns correct Status based on Name passed to it

NOTE: If the need the Function & Code, please let me known. I'll be glad to
assist...
Dec 6 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.