If statement

[jenniferspnc]

I'm not sure if I'm even going down the right path, I've gotten some help on
null columns but it was much too advanced for me to implement out so I'm
taking a different path. I have a query

Country APAC EMEA Co-op
Result
austria level x
level x
australia level y
level y
xxxxxx level x
level x

So I need to have one finalized column, RESULT, at the end. I don't need to
see the APAC, EMEA, etc. header, just one that says Results and lists the
level. So If APAC is null, nothing, but check EMEA, and if EMEA is NOT null
then return it's value, of course keep checking other columns I have. At
this point I don't have any row result where there would be an entry in both
columns...should that change could I am guessing I'd be in some sort of
trouble.

 

[Evi]

Hi Jennifer. If you get advice that just makes you go "Huh???" please say so
and your respondant (or someone else) will try to pitch the reply more
simply.
As you can see, your beautiful table has been killed by text wrap but if I
read correctly, what about having a column in your query that says

YourResult: [APAC] & [EMEA] & [Co-op] & [Otherfield]

etc

If only one has a value in it, then this is the value what will show in this
field.

Evi

 

[jenniferspnc]

Wow, who would of thought it would be that easy, Many Thanks!

Preparing for it, but how would I handle it say if two columns had a value?
I don't have any at this time, but I do believe that will be a possibility
very soon.

Hi Jennifer. If you get advice that just makes you go "Huh???" please say so
and your respondant (or someone else) will try to pitch the reply more
simply.
As you can see, your beautiful table has been killed by text wrap but if I
read correctly, what about having a column in your query that says

YourResult: [APAC] & [EMEA] & [Co-op] & [Otherfield]

etc

If only one has a value in it, then this is the value what will show in this
field.

Evi

I'm not sure if I'm even going down the right path, I've gotten some help on
null columns but it was much too advanced for me to implement out so I'm
taking a different path. I have a query

Country APAC EMEA Co-op
Result
austria level x
level x
australia level y
level y
xxxxxx level x
level x

So I need to have one finalized column, RESULT, at the end. I don't need to
see the APAC, EMEA, etc. header, just one that says Results and lists the
level. So If APAC is null, nothing, but check EMEA, and if EMEA is NOT null
then return it's value, of course keep checking other columns I have. At
this point I don't have any row result where there would be an entry in both
columns...should that change could I am guessing I'd be in some sort of
trouble.

 

[Evi]

It can be done using IIFs but you need to decide what you want to put in the
Result field if this happens.
the IIF will use the fact that a true statement has a value of -1 and a
false one has a value of 0

So

Abs(NZ([ASPAC],0)<>0)

(Abs ignores the minus sign)

means that
If ASPAC is Null then the statement is untrue so this makes 0
if ASPAC isn't Null the statement is truethis will make 1

So you can test this for all your fields and if there is more than one which
is not null then the sum will be greater than 1

IIF((Abs(NZ([ASPAC],0)<>0) + Abs(NZ([EMEA],0)<>0) +
Abs(NZ([Co-op],0)<>0))>1,"Whatever you want to do if this happens",[APAC] &
[EMEA] & [Co-op] & [Otherfield])


Evi

Wow, who would of thought it would be that easy, Many Thanks!

Preparing for it, but how would I handle it say if two columns had a value?
I don't have any at this time, but I do believe that will be a possibility
very soon.

Hi Jennifer. If you get advice that just makes you go "Huh???" please say so
and your respondant (or someone else) will try to pitch the reply more
simply.
As you can see, your beautiful table has been killed by text wrap but if I
read correctly, what about having a column in your query that says

YourResult: [APAC] & [EMEA] & [Co-op] & [Otherfield]

etc

If only one has a value in it, then this is the value what will show in this
field.

Evi

I'm not sure if I'm even going down the right path, I've gotten some

help
on

null columns but it was much too advanced for me to implement out so I'm
taking a different path. I have a query

Country APAC EMEA Co-op
Result
austria level x
level x
australia level y
level y
xxxxxx level x
level x

So I need to have one finalized column, RESULT, at the end. I don't

need
to

see the APAC, EMEA, etc. header, just one that says Results and lists the
level. So If APAC is null, nothing, but check EMEA, and if EMEA is

NOT
null

then return it's value, of course keep checking other columns I have. At
this point I don't have any row result where there would be an entry

in
both

columns...should that change could I am guessing I'd be in some sort of
trouble.

 

[Jeff Boyce]

I can't be sure from your description, but it seems like you might be trying
to describe a situation in which one country could have zero, 1 or many ...
?memberships (as I said, I can't tell). ?And maybe that memberships can
happen at different "levels" (whatever those are)?

If my statement above is reasonably close to your situation, then your table
structure more closely resembles a spreadsheet than a relational database
table. It may be that you're running into trouble using Access' tools
because the data isn't set up for the tools.

It all starts with the data! Can you describe the relationship(s), if any,
between Country, APAC, EMEA, Co-op, and "levels"? Knowing more specifically
about your data/situation may help folks respond with more specific
suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP