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

IIf statements

P: 76
Hello,

I have a series of IIf statements that return a number based on what value is displayed. I have multiple types of "European Workload" items that can appear. How can I change the IIf statement below to return [Text42] whenever any one of the European Workload items appear? Possible using a wildcard?
Heres the formula:

=IIf([Type]="European Workload Index",[Text42],"1")

Thanks,

Charlie
Nov 21 '06 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 218
Hi,

Are [type] and [text42] database fields, or form fields?

Steve
Nov 21 '06 #2

P: 76
Steve,

Thanks for responding. I'm not sure if I'm familiar with the terminology that you used (I'm still new to Access), so I'll describe what the text boxes are. [Type] is a field that is stored in a table. This table is what feeds the report for which I'm working on the IIf statement.

[Text42] is a dlookup function that sits on the report. It is not tied to any table (that is, the figure in [Text42] is not stored anywhere). It performs a dlookup from a query that is tied to a table.

As stated in my earlier post, I need an IIf statement that will give me [Text42] when [Type] is "European Workload" and then any combination of words or letters after "Workload", otherwise return a 1. I feel like it has something to do with "*" but I've tried a lot of different things and I can't seem to get it to work.

Thanks,

Charlie
Nov 21 '06 #3

Expert 5K+
P: 8,434
Try this
Expand|Select|Wrap|Line Numbers
  1. =IIf([Type] Like "European Workload *",[Text42],"1")
Or, if what I read here today is correct, you might need to try this version...
Expand|Select|Wrap|Line Numbers
  1. =IIf([Type] Like "European Workload %",[Text42],"1")
(Just uses % rather than *)
Nov 21 '06 #4

NeoPa
Expert Mod 15k+
P: 31,661
I'm not sure what you read Killer, but I've found that T-SQL (for MS SQL) requires the '%' but Access usually works with '*'.
I would guess the '%' is more standards compliant.
Other, more Standards Compliant SQL engines would probably favour the '%' too.
Nov 21 '06 #5

P: 76
Thanks for your help everyone.

One problem I ran into with this: when what appears is just "European Workload" with nothing after it (which does sometimes happen) it gives me a "1". Is there any way that I can alter the equation so that it gives me [Text42] when "Type" is "European Workload" or it's "European Workload 9901"?

Thanks again,

Charlie
Nov 27 '06 #6

NeoPa
Expert Mod 15k+
P: 31,661
Thanks for your help everyone.

One problem I ran into with this: when what appears is just "European Workload" with nothing after it (which does sometimes happen) it gives me a "1". Is there any way that I can alter the equation so that it gives me [Text42] when "Type" is "European Workload" or it's "European Workload 9901"?

Thanks again,

Charlie
Yes there is.
Expand|Select|Wrap|Line Numbers
  1. =IIf([Type] Like "European Workload*",[Text42],"1")
This doesn't require the extra space at the end of the line and will therefore match "European Workload".
Nov 27 '06 #7

P: 76
You all are great. Thanks for all your help.
Nov 27 '06 #8

Expert 5K+
P: 8,434
=IIf([Type] Like "European Workload*",[Text42],"1")
This doesn't require the extra space at the end of the line and will therefore match "European Workload".
Yeah, that's my goof - sorry.
Nov 27 '06 #9

Post your reply

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