IF Statement 1

[accessewbie]

I've been struggling with a statement I want to create so it can eliminate manual work. I've had to create an audit database. I have the following fields:

Customer
Ops Center (combo box if that matters where the user can choose Global or North America - I might add more at a later date)
North America Price Parent (combo box as well where the user can choose either YES or NO)
EMEA Price Parent (combo box with same selection as above)
APAC Price Parent (combo box with same option as above).

I have a rough draft of what I want the statement to accomplish and I know it's not right. First of all when I did have a partial working statement it returned -1 (which I think may have to do with the combo box)???

Here's what I have:
(IIF [Ops Center] = "North America" AND [North America Price Parent]=yes or no, "yes","" OR IIF [Ops Center] = Global AND [North America Price Parent] AND [EMEA Price Parent] AND [APAC Price Parent]= yes or no, "yes","")

Could someone please help? My only other solution was to create another column and manually check whether or not all the fields were verified, but I want Access to work for me Let me know if you need additional information as my request of what I want to achieve may not be that clear. Thanks in advance for the help.

 

[lespaul]

when you say 'combo box', do you mean that in the TABLE you have a Lookup box? If so, you should reconsider Evil of Lookup Fields

for future reference though, the iif statement construct is:

iif(comparison, value if true, value if false)

if you want to do nested iif statements, the construct should be:

iif(comparison, value if true, iif(comparison, value if true, value if false))

I'm not sure what you are trying to accomplish with the comparison:

[North America Price Parent]=yes or no

if those are the only two choices (and the iif statement is constructed properly) then you will return every record.

-1 and 0 are what are stored when using a boolean (Yes/No, True/False) 0 = false/no and 1 (or -1) = true/yes.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[accessewbie]

Thanks for the example of how Nested If statements should appear - however, that's where I struggle - somehow I'm not putting the information in the right place as you can see.

There are actually other choices besides YES or NO. If the user chooses N/A or the field is blank then I want it to return a blank value as indicated by "". If it shows as yes or no then it should show a "yes" so that I know that area has been verified. I'm trying but I have become frustrated. I just don't understand how to tie the statements together.

Thanks for your quick reply. Your help is appreciated.

 

[lespaul]

if the Yes, No, "" and N/A are the field values, you don't have to do an iif statement in order to display that information. I think what you want is:

IIF ([Ops Center] = "North America" AND [North America Price Parent] IN ("yes", "no"), "yes", IIF ([Ops Center] = Global AND [North America Price Parent] AND [EMEA Price Parent] AND [APAC Price Parent] IN ("yes", "no"), "yes", "")

so if Ops Center = North America AND North America Price Parent is either Yes or No, then "YES" will appear in the query field, if either of those conditions is not true, then the second if query will run and if those conditions are met then "Yes" will appear in the query field, else a "" will be in the field.

Now the fields you have listed in your query that are bold is the way that you compare a boolean value. What you have written there is the same as saying If North America Price Parent = True AND EMEA Price Parent = True, but from your response, North America Price Parent is NOT a boolean value, so these criteria may be breaking your query.

HTH

Leslie

 

[accessewbie]

Leslie,
Wow I can't say thank you enough - that works perfectly. I've not heard about using IN within an IF statement. Where do I read more about this? I think I got confused using the OR command. The statement makes sense - still just a little puzzled. I want to fully understand how to put together these statements for the future. Also, where can I read on what is a boolean value?

I'm sure my criteria may change as I continue to work on my database. I just feel my field options now are limited or really don't capture the accuracy of the details.

Unfortunately, my access training is only through my readings or trial and error. So I'd appreciate any advice on where to go to learn more. I've made significant progress but as proven today - there is always more to learn. Thanks again!!!

 

[lespaul]

I would first recommend you read the Fundamentals document linked in my signature (see above). You can also check out Understanding SQL Joins

if you google 'Boolean Values' you will find many sites that describe what they are and how to use them.

The IN statement works like a bunch of OR statements; the following statements do the same thing:

WHERE SomeField = 'Yes' or SomeField = 'No' Or Somefield = 'Nothing' or Somefield = 'Something'

WHERE SomeField IN ('Yes', 'No', 'Nothing', 'Something')

Glad to help!

Leslie