IIF Statements nested

[Guest]

Hi, I this statement I wrote. It works for nearly all 7500 records except
for a sporatic 1,200! I can't figure out why. Any suggestions?

PartInj: IIf([PriDx] Is Not Null,[PriDx],IIf([Sec1] Is Not
Null,[Sec1],IIf([Sec2] Is Not Null,[Sec2],IIf([Sec3] Is Not
Null,[Sec3],IIf([Sec4] Is Not Null,[Sec4],IIf([Sec5] Is Not
Null,[Sec5],IIf([Sec6] Is Not Null,[Sec6],"")))))))

If the primary diagnosis is not null then show me or else look at the next
diagnosis and if that is not null then show that one else...

Thanks,

Todd

 

[Jason Lepack]

Try this:
PartInj: IIf([PriDx] Is Not Null,[PriDx],IIf([Sec1] Is Not
Null,[Sec1],IIf([Sec2] Is Not Null,[Sec2],IIf([Sec3] Is Not
Null,[Sec3],IIf([Sec4] Is Not Null,[Sec4],IIf([Sec5] Is Not
Null,[Sec5],IIf([Sec6] Is Not Null,[Sec6],"ooga booga")))))))

The ones that still return 'blanks' are not null, but have some form
of non-visible character (ie. space or return key)

Cheers,
Jason Lepack

 

[Guest]

Gotcha! I never thought of that...thanks!

Now, how do I go about deleting or ignoring those charactors?

Thanks,

Try this:
PartInj: IIf([PriDx] Is Not Null,[PriDx],IIf([Sec1] Is Not
Null,[Sec1],IIf([Sec2] Is Not Null,[Sec2],IIf([Sec3] Is Not
Null,[Sec3],IIf([Sec4] Is Not Null,[Sec4],IIf([Sec5] Is Not
Null,[Sec5],IIf([Sec6] Is Not Null,[Sec6],"ooga booga")))))))

The ones that still return 'blanks' are not null, but have some form
of non-visible character (ie. space or return key)

Cheers,
Jason Lepack

Hi, I this statement I wrote. It works for nearly all 7500 records except
for a sporatic 1,200! I can't figure out why. Any suggestions?

PartInj: IIf([PriDx] Is Not Null,[PriDx],IIf([Sec1] Is Not
Null,[Sec1],IIf([Sec2] Is Not Null,[Sec2],IIf([Sec3] Is Not
Null,[Sec3],IIf([Sec4] Is Not Null,[Sec4],IIf([Sec5] Is Not
Null,[Sec5],IIf([Sec6] Is Not Null,[Sec6],"")))))))

If the primary diagnosis is not null then show me or else look at the next
diagnosis and if that is not null then show that one else...

Thanks,

Todd

 

[Jason Lepack]

Try an update query. But make sure that you backup your data first.

UPDATE table1
SET Pridx = Null
WHERE Pridx = ' '

This will fix it if the spaces are only single spaces in pridx.

You need to determine what will fix your problem.

Cheers,
Jason Lepack

Gotcha! I never thought of that...thanks!

Now, how do I go about deleting or ignoring those charactors?

Thanks,

Try this:
PartInj: IIf([PriDx] Is Not Null,[PriDx],IIf([Sec1] Is Not
Null,[Sec1],IIf([Sec2] Is Not Null,[Sec2],IIf([Sec3] Is Not
Null,[Sec3],IIf([Sec4] Is Not Null,[Sec4],IIf([Sec5] Is Not
Null,[Sec5],IIf([Sec6] Is Not Null,[Sec6],"ooga booga")))))))

The ones that still return 'blanks' are not null, but have some form
of non-visible character (ie. space or return key)

Cheers,
Jason Lepack

Hi, I this statement I wrote. It works for nearly all 7500 records except
for a sporatic 1,200! I can't figure out why. Any suggestions?
PartInj: IIf([PriDx] Is Not Null,[PriDx],IIf([Sec1] Is Not
Null,[Sec1],IIf([Sec2] Is Not Null,[Sec2],IIf([Sec3] Is Not
Null,[Sec3],IIf([Sec4] Is Not Null,[Sec4],IIf([Sec5] Is Not
Null,[Sec5],IIf([Sec6] Is Not Null,[Sec6],"")))))))
If the primary diagnosis is not null then show me or else look at the next
diagnosis and if that is not null then show that one else...
Thanks,
Todd- Hide quoted text -

- Show quoted text -

 

[Guest]

I looked at the table I created a join with and found that to be a problem.
I got rid of any field that was blank and that fixed the problem! Thanks for
your time!

Try an update query. But make sure that you backup your data first.

UPDATE table1
SET Pridx = Null
WHERE Pridx = ' '

This will fix it if the spaces are only single spaces in pridx.

You need to determine what will fix your problem.

Cheers,
Jason Lepack

Gotcha! I never thought of that...thanks!

Now, how do I go about deleting or ignoring those charactors?

Thanks,

Try this:
PartInj: IIf([PriDx] Is Not Null,[PriDx],IIf([Sec1] Is Not
Null,[Sec1],IIf([Sec2] Is Not Null,[Sec2],IIf([Sec3] Is Not
Null,[Sec3],IIf([Sec4] Is Not Null,[Sec4],IIf([Sec5] Is Not
Null,[Sec5],IIf([Sec6] Is Not Null,[Sec6],"ooga booga")))))))

The ones that still return 'blanks' are not null, but have some form
of non-visible character (ie. space or return key)

Cheers,
Jason Lepack

Hi, I this statement I wrote. It works for nearly all 7500 records except
for a sporatic 1,200! I can't figure out why. Any suggestions?

PartInj: IIf([PriDx] Is Not Null,[PriDx],IIf([Sec1] Is Not
Null,[Sec1],IIf([Sec2] Is Not Null,[Sec2],IIf([Sec3] Is Not
Null,[Sec3],IIf([Sec4] Is Not Null,[Sec4],IIf([Sec5] Is Not
Null,[Sec5],IIf([Sec6] Is Not Null,[Sec6],"")))))))

If the primary diagnosis is not null then show me or else look at the next
diagnosis and if that is not null then show that one else...

Todd- Hide quoted text -

- Show quoted text -

 

[Jason Lepack]

You're welcome!

I looked at the table I created a join with and found that to be a problem.
I got rid of any field that was blank and that fixed the problem! Thanks for
your time!

Try an update query. But make sure that you backup your data first.

UPDATE table1
SET Pridx = Null
WHERE Pridx = ' '

This will fix it if the spaces are only single spaces in pridx.

You need to determine what will fix your problem.

Cheers,
Jason Lepack

Gotcha! I never thought of that...thanks!
Now, how do I go about deleting or ignoring those charactors?
Thanks,
:
Try this:
PartInj: IIf([PriDx] Is Not Null,[PriDx],IIf([Sec1] Is Not
Null,[Sec1],IIf([Sec2] Is Not Null,[Sec2],IIf([Sec3] Is Not
Null,[Sec3],IIf([Sec4] Is Not Null,[Sec4],IIf([Sec5] Is Not
Null,[Sec5],IIf([Sec6] Is Not Null,[Sec6],"ooga booga")))))))
The ones that still return 'blanks' are not null, but have some form
of non-visible character (ie. space or return key)
Cheers,
Jason Lepack
Hi, I this statement I wrote. It works for nearly all 7500 records except
for a sporatic 1,200! I can't figure out why. Any suggestions?
PartInj: IIf([PriDx] Is Not Null,[PriDx],IIf([Sec1] Is Not
Null,[Sec1],IIf([Sec2] Is Not Null,[Sec2],IIf([Sec3] Is Not
Null,[Sec3],IIf([Sec4] Is Not Null,[Sec4],IIf([Sec5] Is Not
Null,[Sec5],IIf([Sec6] Is Not Null,[Sec6],"")))))))
If the primary diagnosis is not null then show me or else look at the next
diagnosis and if that is not null then show that one else...
Thanks,
Todd- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -