Statement error

[melaniecarr23]

I have this statement on my report, and it's not working out. Here is what I have:

=iif([max_visits]>0, "You have a max of "&[max_visits]&" visits per year." AND iif([used_visits]>0,[used_visits]" have been used.",""),iif([pocket_max]>0,"You have an out of pocket max of $"&[pocket_max]&".","Your max visits are based on medical necessity",""))

I would assume the problems are due to trying to use an AND statement inside the THEN section of my original if statement. I'd really like this to be one control so they write clean like a paragraph.

Can I do something like this, or do I really need to have a few controls to cover this many statements?

Thanks!

 

[Blorf]

The And is confusing me.

Do you want you have max visits, & you have used ...?

If so, try
=iif([max_visits]>0, "You have a max of "&[max_visits]&" visits per year.", "") & iif([used_visits]>0,[used_visits]& " have been used.","") & iif([pocket_max]>0,"You have an out of pocket max of $"&[pocket_max]&".","Your max visits are based on medical necessity",""))


I can't test if, but I hope I understand.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[Darrylles]

Melanie,

Blorf isn't the only confused one.
Also Blorf, is it possible to & iif statements in such a way? I tried your code and it froze my machine

Mel, you need to explain your required logic more clearly, before the syntax is attempted.

i.e. With your logic, the user may see any one of the following statements (dependant on the field values), and they don't make sense:

mv = 10, uv = 0, pm = 0
Your max visits are based on medical necessity.

mv = 0, uv = 3, pm = 0
Your max visits are based on medical necessity.

mv = 10, uv = 3, pm = 0
You have a max of 10 visits per year. 3 visits have been used. Your max visits are based on medical necessity.

mv = 10, uv = 3, pm = 30
You have a max of 10 visits per year. 3 visits have been used. You have an out of pocket max of $30. Your max visits are based on medical necessity.

mv = 0, uv = 3, pm = 30
3 visits have been used. You have an out of pocket max of $30. Your max visits are based on medical necessity.

Regards,

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[Blorf]

Yup. Assume that X = 1, and Y = 1, then

=iif (X=1, "Yup, X=1", "Nope") & iif (Y=1, " Yup, Y=1")

will give you Yup, X=1 Yup, Y=1

X = 0, Y = 1 gives you

Nope Yup, Y=1.

ChaZ



There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[PHV]

=iif([max_visits]>0, "You have a max of " & [max_visits] & " visits per year." & iif([used_visits]>0,[used_visits] & " have been used.",""), iif([pocket_max]>0,"You have an out of pocket max of $" & [pocket_max] & ".","Your max visits are based on medical necessity",""))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Blorf]

Hi PHV.

With your solution, wouldn't you only get used visits and all the others if Max vists is greater than 0?

I kinda thought the goal was listing all of them, if conditions are met. I could be wrong. Usually next to you I am. Given any thought to that wire to the fore head to share knowoledge?

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[melaniecarr23]

If there is a max # of visits, I want to be able to tell the patient how many of their max visits they have used prior to their plan. If they don't have a max # of visits, I don't care about how many they used.

If there is no max or out of pocket max, they are based on medical necessity.

So I needed to use the "&" sign to add the iif statement for the used visits in the then section of the iif statement. Thank you!

Melanie

 

[Darrylles]

Hi again,

Ok - there's the logic, here's the SQL...

IIf([max_visits]=0 And [pocket_max]=0,"Your max visits are based on medical necessity",IIf([max_visits]>0,"You have a max of " & [max_visits] & " visits per year, with " & [used_visits] & " visits used." & IIf([pocket_max]>0,"You have an out of pocket max of $" & [pocket_max] & ".","")));

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[Darrylles]

Hi,

I'd like to add a point for this post:

SQL query IIF constructs are not easy to 'visualise' to people that have been brought up on the 'Basic' IF .. THEN .. ELSE construct.

They are obviously not that easy to visualise to someone with PHV's obvious experience - even he had to 'second-guess' what Mel wanted.

This is because of the lack of indentation with SQL queries within Access. (Access does not use indentation within it's SQL string display).

I stated that Mel's logic had to be flawed in my first post, because the results from her AND's and OR's could never make sense.
I reached this conclusion by taking out her conditional statements and then applying her attempted SQL IIF construct to the normal IF..THEN..ELSE construct.

When using standard IF..THEN..ELSE, the indentation shouts out that the logic is not correct.

I think, that this should be added as a 'tip'. If PHV re-visits, hopefully he will add it - I don't visit often enough to be bothered to do it.

The alternative to transposing the SQL iif construct over to the general IF..THEN..ELSE, is simply to ask the initiator of the post to explain the conditions and required results explicitly. (As in Mels final post).

ATB

Darrylle





Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[Darrylles]

Hi,

I'd like to add a point for this post:

SQL query IIF constructs are not easy to 'visualise' to people that have been brought up on the 'Basic' IF .. THEN .. ELSE construct.

They are obviously not even that easy to visualise to someone with PHV's obvious experience - even he had to 'second-guess' what Mel wanted.

This is because of the lack of indentation with SQL queries within Access. (Access does not use indentation within it's SQL string display).

I stated that Mel's logic had to be flawed in my first post, because the results from her AND's and OR's could never make sense.
I reached this conclusion by taking out her conditional statements and then applying her attempted SQL IIF construct to the normal IF..THEN..ELSE construct.

When using standard IF..THEN..ELSE, the indentation shouts out that the logic is not correct.

I think, that this should be added as a 'tip'. If PHV re-visits, hopefully he will add it - I don't visit often enough to be bothered to do it.

Breaking the logic down into standard IF..THEN..ELSE along with indentation makes it simple to see any problems.

ATB

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk