Alternatives to Nested If statements?

  • Thread starter Thread starter Twee
  • Start date Start date
T

Twee

Hi all,

I am new to Access and am having problems because I am writing a nested iif
statement that has 15 conditions, which I believe, not only exceeds the limit
of nested if allowed but also probably indicative that my approach is not the
best way to do things.

In a query, here is what my if statement did in the DX order column:
iif(len([Dx code 1]>1,"1",iif(len([Dx code 2]>1,"2","3")). Unfortunately, the
only way to tell the order of the diagnosis is by those columns called Dx
Code 1, Dx Code 2...Dx Code 15

If I had only three diagnosis, it would work out great but each patient has
about 15 diagnosis so my nested if statements would look crazy.

Patient Name Dx Code Dx code 1 Dx code 2 Dx code 3 Dx Order
Patient 1 511 0511
2
Patient 1 0811 0811
3
Patient 1 0528 528
1

Can anyone help me with alternatives? I am very new to VBA codes but would
be willing to try it. Thank you so much!!
 
Select Case will not work in a query.

To the OP, if you have fifteen diagnosis fields within a single record your
design is suspect. Diagnoses should probably be in a related table.

Having said that, take a look at your IIf statement:

iif(len([Dx code 1]>1,"1",iif(len([Dx code 2]>1,"2","3"))

First, you would need to close the parentheses for each Len:
iif(len([Dx code 1])>1,"1",iif(len([Dx code 2])>1,"2","3"))

If Len([Dx code 1]) > 1, the expression will return 1 no matter what else
happens in the expression. All parts of the IIf statement are evaluated,
but the only one returned is the first one that passes the logical test.

If the idea is that Dx Code 15 is used only if the fourteen previous Dx Code
fields have been filled in you may be able to work backwards using the
Switch function:

Switch(Len([Dx Code 15]) > 1,15,Len([Dx Code 14]) > 1,14, etc.)

Be sure there are no one-digit codes. If there are, check for Len > 0
rather than 1.
See Help for more information about the Switch function.

If you are working with a query you are using SQL, which is the actual code
behind the query design view. VBA is used in event procedures, and in code
modules to create subs and functions. Functions created with VBA can be
used in queries the same as a built-in functions, but the syntax of SQL and
VBA is quite different.

Again, your design is probably unsound. It will almost surely cause grief,
particularly if you ever try to add or remove a diagnosis field.

Golfinray said:
Look at some of examples on here of Case statements.

Twee said:
Hi all,

I am new to Access and am having problems because I am writing a nested
iif
statement that has 15 conditions, which I believe, not only exceeds the
limit
of nested if allowed but also probably indicative that my approach is not
the
best way to do things.

In a query, here is what my if statement did in the DX order column:
iif(len([Dx code 1]>1,"1",iif(len([Dx code 2]>1,"2","3")). Unfortunately,
the
only way to tell the order of the diagnosis is by those columns called Dx
Code 1, Dx Code 2...Dx Code 15

If I had only three diagnosis, it would work out great but each patient
has
about 15 diagnosis so my nested if statements would look crazy.

Patient Name Dx Code Dx code 1 Dx code 2 Dx code 3 Dx Order
Patient 1 511 0511
2
Patient 1 0811
0811
3
Patient 1 0528 528
1

Can anyone help me with alternatives? I am very new to VBA codes but
would
be willing to try it. Thank you so much!!
Click to expand...
Click to expand...
 
Your table structure needs to be different --
Patient Name Dx Code Dx Order
Patient 1 511 1
Patient 1 0511 2
Patient 1 0811 3
Patient 1 0811 4
Patient 1 0528 5
Patient 1 0528 6
 
Hi all,

http://spreadsheets.google.com/pub?key=p26SdyqhouO7GRCPa8VAAZg

Unfortunately, for me, the data is generated from a very old database and so
I'm stuck with it. Hopefully you can see what I mean better in the link that
I've provided.

I know if is very unstable table (this is how I received the data) so I'm
trying to get it into this sound data format with DX ORDER column. I'm
sorry, that I left out some parenthesis when I did it...I just didn't cut and
paste like I should have. Regardless, the if statements work but it's the 15
nested if that is the problem. I'll give the Switch statement a try...

Thanks!



KARL DEWEY said:
Your table structure needs to be different --
Patient Name Dx Code Dx Order
Patient 1 511 1
Patient 1 0511 2
Patient 1 0811 3
Patient 1 0811 4
Patient 1 0528 5
Patient 1 0528 6

--
KARL DEWEY
Build a little - Test a little


Twee said:
Hi all,

I am new to Access and am having problems because I am writing a nested iif
statement that has 15 conditions, which I believe, not only exceeds the limit
of nested if allowed but also probably indicative that my approach is not the
best way to do things.

In a query, here is what my if statement did in the DX order column:
iif(len([Dx code 1]>1,"1",iif(len([Dx code 2]>1,"2","3")). Unfortunately, the
only way to tell the order of the diagnosis is by those columns called Dx
Code 1, Dx Code 2...Dx Code 15

If I had only three diagnosis, it would work out great but each patient has
about 15 diagnosis so my nested if statements would look crazy.

Patient Name Dx Code Dx code 1 Dx code 2 Dx code 3 Dx Order
Patient 1 511 0511
2
Patient 1 0811 0811
3
Patient 1 0528 528
1

Can anyone help me with alternatives? I am very new to VBA codes but would
be willing to try it. Thank you so much!!
Click to expand...
Click to expand...
 
Use a union query to put the data in a useable order --
SELECT [Patient Name], [Dx Code] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 1] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 2] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 3] AS DX_Code]
FROM YourTable
......
UNION ALL SELECT [Patient Name], [Dx Code 15] AS DX_Code]
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


Twee said:
Hi all,

http://spreadsheets.google.com/pub?key=p26SdyqhouO7GRCPa8VAAZg

Unfortunately, for me, the data is generated from a very old database and so
I'm stuck with it. Hopefully you can see what I mean better in the link that
I've provided.

I know if is very unstable table (this is how I received the data) so I'm
trying to get it into this sound data format with DX ORDER column. I'm
sorry, that I left out some parenthesis when I did it...I just didn't cut and
paste like I should have. Regardless, the if statements work but it's the 15
nested if that is the problem. I'll give the Switch statement a try...

Thanks!



KARL DEWEY said:
Your table structure needs to be different --
Patient Name Dx Code Dx Order
Patient 1 511 1
Patient 1 0511 2
Patient 1 0811 3
Patient 1 0811 4
Patient 1 0528 5
Patient 1 0528 6

--
KARL DEWEY
Build a little - Test a little


Twee said:
Hi all,

I am new to Access and am having problems because I am writing a nested iif
statement that has 15 conditions, which I believe, not only exceeds the limit
of nested if allowed but also probably indicative that my approach is not the
best way to do things.

In a query, here is what my if statement did in the DX order column:
iif(len([Dx code 1]>1,"1",iif(len([Dx code 2]>1,"2","3")). Unfortunately, the
only way to tell the order of the diagnosis is by those columns called Dx
Code 1, Dx Code 2...Dx Code 15

If I had only three diagnosis, it would work out great but each patient has
about 15 diagnosis so my nested if statements would look crazy.

Patient Name Dx Code Dx code 1 Dx code 2 Dx code 3 Dx Order
Patient 1 511 0511
2
Patient 1 0811 0811
3
Patient 1 0528 528
1

Can anyone help me with alternatives? I am very new to VBA codes but would
be willing to try it. Thank you so much!!
Click to expand...
Click to expand...
Click to expand...
 
Hi Karl,

Thanks for helping me. forgive my idiot question but I tried this but
becasue I'm trying to populate the DX Order column, I am unsure that the
union query will tell me the order of the diagnosis codes. I am sorry that
I'm unable to make anything work so far.

KARL DEWEY said:
Use a union query to put the data in a useable order --
SELECT [Patient Name], [Dx Code] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 1] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 2] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 3] AS DX_Code]
FROM YourTable
.....
UNION ALL SELECT [Patient Name], [Dx Code 15] AS DX_Code]
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


Twee said:
Hi all,

http://spreadsheets.google.com/pub?key=p26SdyqhouO7GRCPa8VAAZg

Unfortunately, for me, the data is generated from a very old database and so
I'm stuck with it. Hopefully you can see what I mean better in the link that
I've provided.

I know if is very unstable table (this is how I received the data) so I'm
trying to get it into this sound data format with DX ORDER column. I'm
sorry, that I left out some parenthesis when I did it...I just didn't cut and
paste like I should have. Regardless, the if statements work but it's the 15
nested if that is the problem. I'll give the Switch statement a try...

Thanks!



KARL DEWEY said:
Your table structure needs to be different --
Patient Name Dx Code Dx Order
Patient 1 511 1
Patient 1 0511 2
Patient 1 0811 3
Patient 1 0811 4
Patient 1 0528 5
Patient 1 0528 6

--
KARL DEWEY
Build a little - Test a little


:

Hi all,

I am new to Access and am having problems because I am writing a nested iif
statement that has 15 conditions, which I believe, not only exceeds the limit
of nested if allowed but also probably indicative that my approach is not the
best way to do things.

In a query, here is what my if statement did in the DX order column:
iif(len([Dx code 1]>1,"1",iif(len([Dx code 2]>1,"2","3")). Unfortunately, the
only way to tell the order of the diagnosis is by those columns called Dx
Code 1, Dx Code 2...Dx Code 15

If I had only three diagnosis, it would work out great but each patient has
about 15 diagnosis so my nested if statements would look crazy.

Patient Name Dx Code Dx code 1 Dx code 2 Dx code 3 Dx Order
Patient 1 511 0511
2
Patient 1 0811 0811
3
Patient 1 0528 528
1

Can anyone help me with alternatives? I am very new to VBA codes but would
be willing to try it. Thank you so much!!
Click to expand...
Click to expand...
Click to expand...
 
You could modify it as:

SELECT [Patient Name], [Dx Code] AS [DX_Code], 0 as [DX_Order]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 1] AS DX_Code], 1 as [DX_Order]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 2] AS DX_Code], 2 as [DX_Order]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 3] AS DX_Code], 3 as [DX_Order]
FROM YourTableUNION ALL SELECT [Patient Name], [Dx Code 15] AS DX_Code], 15 as [DX_Order]


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Twee said:
Hi Karl,

Thanks for helping me. forgive my idiot question but I tried this but
becasue I'm trying to populate the DX Order column, I am unsure that the
union query will tell me the order of the diagnosis codes. I am sorry that
I'm unable to make anything work so far.

KARL DEWEY said:
Use a union query to put the data in a useable order --
SELECT [Patient Name], [Dx Code] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 1] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 2] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 3] AS DX_Code]
FROM YourTable
.....
UNION ALL SELECT [Patient Name], [Dx Code 15] AS DX_Code]
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


Twee said:
Hi all,

http://spreadsheets.google.com/pub?key=p26SdyqhouO7GRCPa8VAAZg

Unfortunately, for me, the data is generated from a very old database and so
I'm stuck with it. Hopefully you can see what I mean better in the link that
I've provided.

I know if is very unstable table (this is how I received the data) so I'm
trying to get it into this sound data format with DX ORDER column. I'm
sorry, that I left out some parenthesis when I did it...I just didn't cut and
paste like I should have. Regardless, the if statements work but it's the 15
nested if that is the problem. I'll give the Switch statement a try...

Thanks!



:

Your table structure needs to be different --
Patient Name Dx Code Dx Order
Patient 1 511 1
Patient 1 0511 2
Patient 1 0811 3
Patient 1 0811 4
Patient 1 0528 5
Patient 1 0528 6

--
KARL DEWEY
Build a little - Test a little


:

Hi all,

I am new to Access and am having problems because I am writing a nested iif
statement that has 15 conditions, which I believe, not only exceeds the limit
of nested if allowed but also probably indicative that my approach is not the
best way to do things.

In a query, here is what my if statement did in the DX order column:
iif(len([Dx code 1]>1,"1",iif(len([Dx code 2]>1,"2","3")). Unfortunately, the
only way to tell the order of the diagnosis is by those columns called Dx
Code 1, Dx Code 2...Dx Code 15

If I had only three diagnosis, it would work out great but each patient has
about 15 diagnosis so my nested if statements would look crazy.

Patient Name Dx Code Dx code 1 Dx code 2 Dx code 3 Dx Order
Patient 1 511 0511
2
Patient 1 0811 0811
3
Patient 1 0528 528
1

Can anyone help me with alternatives? I am very new to VBA codes but would
be willing to try it. Thank you so much!!
Click to expand...
Click to expand...
Click to expand...
 
Thank you, Dale. I gave it a try referencing my table called tblCopyCSA09POA
with the following fields:
patient ID, DX Code, Dx Code 1, Dx Code 2, Dx Code 3, Dx Order

Dx order is blank, of course. Though my true dataset has goes all the way
to DX Code 15, I tried the shortened codes just to test. I don't know what
I'm doing wrong as I get the message that says" Select statement includes a
reserved word or an argument that is misspelled or missing or incorrectly
punctuated..."

Being completely new at this, I tried troublshooting but to no avail.
PLease help because I'm feeling so discouraged by Access. I would very much
appreciate the help! Thank you!!!

SELECT [Patient ID], [Dx Code] AS [DX_Code], 0 as [DX_Order]
FROM tblCopyCSA09POA
UNION ALL SELECT [Patient ID], [Dx Code 1] AS DX_Code], 1 as [DX_Order]
FROM tblCopyCSA09POA
UNION ALL SELECT [Patient ID], [Dx Code 2] AS DX_Code], 2 as [DX_Order]
FROM tblCopyCSA09POA
UNION ALL SELECT [Patient ID], [Dx Code 3] AS DX_Code], 3 as [DX_Order]
FROM tblCopyCSA09POA;


Dale Fye said:
You could modify it as:

SELECT [Patient Name], [Dx Code] AS [DX_Code], 0 as [DX_Order]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 1] AS DX_Code], 1 as [DX_Order]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 2] AS DX_Code], 2 as [DX_Order]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 3] AS DX_Code], 3 as [DX_Order]
FROM YourTableUNION ALL SELECT [Patient Name], [Dx Code 15] AS DX_Code], 15 as [DX_Order]


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Twee said:
Hi Karl,

Thanks for helping me. forgive my idiot question but I tried this but
becasue I'm trying to populate the DX Order column, I am unsure that the
union query will tell me the order of the diagnosis codes. I am sorry that
I'm unable to make anything work so far.

KARL DEWEY said:
Use a union query to put the data in a useable order --
SELECT [Patient Name], [Dx Code] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 1] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 2] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 3] AS DX_Code]
FROM YourTable
.....
UNION ALL SELECT [Patient Name], [Dx Code 15] AS DX_Code]
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


:

Hi all,

http://spreadsheets.google.com/pub?key=p26SdyqhouO7GRCPa8VAAZg

Unfortunately, for me, the data is generated from a very old database and so
I'm stuck with it. Hopefully you can see what I mean better in the link that
I've provided.

I know if is very unstable table (this is how I received the data) so I'm
trying to get it into this sound data format with DX ORDER column. I'm
sorry, that I left out some parenthesis when I did it...I just didn't cut and
paste like I should have. Regardless, the if statements work but it's the 15
nested if that is the problem. I'll give the Switch statement a try...

Thanks!



:

Your table structure needs to be different --
Patient Name Dx Code Dx Order
Patient 1 511 1
Patient 1 0511 2
Patient 1 0811 3
Patient 1 0811 4
Patient 1 0528 5
Patient 1 0528 6

--
KARL DEWEY
Build a little - Test a little


:

Hi all,

I am new to Access and am having problems because I am writing a nested iif
statement that has 15 conditions, which I believe, not only exceeds the limit
of nested if allowed but also probably indicative that my approach is not the
best way to do things.

In a query, here is what my if statement did in the DX order column:
iif(len([Dx code 1]>1,"1",iif(len([Dx code 2]>1,"2","3")). Unfortunately, the
only way to tell the order of the diagnosis is by those columns called Dx
Code 1, Dx Code 2...Dx Code 15

If I had only three diagnosis, it would work out great but each patient has
about 15 diagnosis so my nested if statements would look crazy.

Patient Name Dx Code Dx code 1 Dx code 2 Dx code 3 Dx Order
Patient 1 511 0511
2
Patient 1 0811 0811
3
Patient 1 0528 528
1

Can anyone help me with alternatives? I am very new to VBA codes but would
be willing to try it. Thank you so much!!
Click to expand...
Click to expand...
Click to expand...
 
Does your table contain a field named 'Dx Order'? Then use a different alias
in the query.
--
KARL DEWEY
Build a little - Test a little


Twee said:
Thank you, Dale. I gave it a try referencing my table called tblCopyCSA09POA
with the following fields:
patient ID, DX Code, Dx Code 1, Dx Code 2, Dx Code 3, Dx Order

Dx order is blank, of course. Though my true dataset has goes all the way
to DX Code 15, I tried the shortened codes just to test. I don't know what
I'm doing wrong as I get the message that says" Select statement includes a
reserved word or an argument that is misspelled or missing or incorrectly
punctuated..."

Being completely new at this, I tried troublshooting but to no avail.
PLease help because I'm feeling so discouraged by Access. I would very much
appreciate the help! Thank you!!!

SELECT [Patient ID], [Dx Code] AS [DX_Code], 0 as [DX_Order]
FROM tblCopyCSA09POA
UNION ALL SELECT [Patient ID], [Dx Code 1] AS DX_Code], 1 as [DX_Order]
FROM tblCopyCSA09POA
UNION ALL SELECT [Patient ID], [Dx Code 2] AS DX_Code], 2 as [DX_Order]
FROM tblCopyCSA09POA
UNION ALL SELECT [Patient ID], [Dx Code 3] AS DX_Code], 3 as [DX_Order]
FROM tblCopyCSA09POA;


Dale Fye said:
You could modify it as:

SELECT [Patient Name], [Dx Code] AS [DX_Code], 0 as [DX_Order]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 1] AS DX_Code], 1 as [DX_Order]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 2] AS DX_Code], 2 as [DX_Order]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 3] AS DX_Code], 3 as [DX_Order]
FROM YourTable
.....
Click to expand...
UNION ALL SELECT [Patient Name], [Dx Code 15] AS DX_Code], 15 as [DX_Order]


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Twee said:
Hi Karl,

Thanks for helping me. forgive my idiot question but I tried this but
becasue I'm trying to populate the DX Order column, I am unsure that the
union query will tell me the order of the diagnosis codes. I am sorry that
I'm unable to make anything work so far.

:

Use a union query to put the data in a useable order --
SELECT [Patient Name], [Dx Code] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 1] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 2] AS DX_Code]
FROM YourTable
UNION ALL SELECT [Patient Name], [Dx Code 3] AS DX_Code]
FROM YourTable
.....
UNION ALL SELECT [Patient Name], [Dx Code 15] AS DX_Code]
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


:

Hi all,

http://spreadsheets.google.com/pub?key=p26SdyqhouO7GRCPa8VAAZg

Unfortunately, for me, the data is generated from a very old database and so
I'm stuck with it. Hopefully you can see what I mean better in the link that
I've provided.

I know if is very unstable table (this is how I received the data) so I'm
trying to get it into this sound data format with DX ORDER column. I'm
sorry, that I left out some parenthesis when I did it...I just didn't cut and
paste like I should have. Regardless, the if statements work but it's the 15
nested if that is the problem. I'll give the Switch statement a try...

Thanks!



:

Your table structure needs to be different --
Patient Name Dx Code Dx Order
Patient 1 511 1
Patient 1 0511 2
Patient 1 0811 3
Patient 1 0811 4
Patient 1 0528 5
Patient 1 0528 6

--
KARL DEWEY
Build a little - Test a little


:

Hi all,

I am new to Access and am having problems because I am writing a nested iif
statement that has 15 conditions, which I believe, not only exceeds the limit
of nested if allowed but also probably indicative that my approach is not the
best way to do things.

In a query, here is what my if statement did in the DX order column:
iif(len([Dx code 1]>1,"1",iif(len([Dx code 2]>1,"2","3")). Unfortunately, the
only way to tell the order of the diagnosis is by those columns called Dx
Code 1, Dx Code 2...Dx Code 15

If I had only three diagnosis, it would work out great but each patient has
about 15 diagnosis so my nested if statements would look crazy.

Patient Name Dx Code Dx code 1 Dx code 2 Dx code 3 Dx Order
Patient 1 511 0511
2
Patient 1 0811 0811
3
Patient 1 0528 528
1

Can anyone help me with alternatives? I am very new to VBA codes but would
be willing to try it. Thank you so much!!
Click to expand...
Click to expand...
Click to expand...
 
Dx order is blank, of course. Though my true dataset has goes all the way
to DX Code 15, I tried the shortened codes just to test. I don't know what
I'm doing wrong as I get the message that says" Select statement includes a
reserved word or an argument that is misspelled or missing or incorrectly
punctuated..."
Click to expand...

Looks like you're missing some opening square brackets! Try leaving off the
(unneeded and ignored) aliases on the second and subsequent SELECT statements.
Also, to avoid inserting a lot of uneeded blank records, consider adding a
criterion to each:

SELECT [Patient ID], [Dx Code] AS [DX_Code], 0 as [DX_Order]
FROM tblCopyCSA09POA
WHERE [Dx Code] IS NOT NULL
UNION ALL SELECT [Patient ID], [Dx Code 1], 1
FROM tblCopyCSA09POA
WHERE [Dx Code 1] IS NOT NULL
UNION ALL SELECT [Patient ID], [Dx Code 2] , 2
FROM tblCopyCSA09POA
WHERE [Dx Code 2] IS NOT NULL
UNION ALL SELECT [Patient ID], [Dx Code 3], 3
FROM tblCopyCSA09POA
WHERE [Dx Code 3] IS NOT NULL
<etc>
 
Back
Top