Still getting #error in the results

[Guest]

I have tried IIf is null, NZ and several other functions. I still get #error
in the results of my query. Any ideas?

MgrLvl: IIf([OrgSupeIndexCCode]![LinkSupe] Is
Null,"E",IIf(Right([OrgSupeIndexCCode]![LinkSupe],5)>60000,"S",IIf(Right([OrgSupeIndexCCode]![LinkSupe],5) Between 1 And 60000,"M","E")))

MgrLvl:
IIf(Right(NZ([OrgSupeIndexCCode]![LinkSupe],"00000"),5)>60000,"S",IIf(Right(NZ([OrgSupeIndexCCode]![LinkSupe],"00000"),5) Between 1 And 60000,"M","E")))

MgrLvl:
IIf(Right(NZ([OrgSupeIndexCCode]![LinkSupe],0),5)>60000,"S",IIf(Right(NZ([OrgSupeIndexCCode]![LinkSupe],0),5) Between 1 And 60000,"M","E")))

Any other ideas?

 

[John Spencer]

What values are in LinkSupe? Is LinkSupe a text field or a number field?
If text field can LinkSupe contain a zero length string?

Try trouble shooting by building up your statement a bit at a time. Do this
work?
MgrLvl: IIf([OrgSupeIndexCCode]![LinkSupe] Is Null,"E", "Not E")

If not, try
MgrLvl: IIf([OrgSupeIndexCCode]![LinkSupe] Is Null OR
[OrgSupeIndexCCode]![LinkSupe] = "" ,"E", "Not E")

Once that is working try the next step. I would try using the Val function
to force a number value since the Right function is going to return a string
MgrLvl: IIf([OrgSupeIndexCCode]![LinkSupe] Is Null OR
[OrgSupeIndexCCode]![LinkSupe] = "" ,"E",
IIF(Val(Right([OrgSupeIndexCCode]![LinkSupe],5) )> 60000,"S","Not S"))

If that works then add on the last part.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

I have tried IIf is null, NZ and several other functions. I still get #error
in the results of my query. Any ideas?

MgrLvl: IIf([OrgSupeIndexCCode]![LinkSupe] Is
Null,"E",IIf(Right([OrgSupeIndexCCode]![LinkSupe],5)>60000,"S",IIf(Right([OrgSupeIndexCCode]![LinkSupe],5) Between 1 And 60000,"M","E")))

MgrLvl:
IIf(Right(NZ([OrgSupeIndexCCode]![LinkSupe],"00000"),5)>60000,"S",IIf(Right(NZ([OrgSupeIndexCCode]![LinkSupe],"00000"),5) Between 1 And 60000,"M","E")))

MgrLvl:
IIf(Right(NZ([OrgSupeIndexCCode]![LinkSupe],0),5)>60000,"S",IIf(Right(NZ([OrgSupeIndexCCode]![LinkSupe],0),5) Between 1 And 60000,"M","E")))

Not sure about the rest of it, but if OrgSupeIndexCCode is a
table name, then you need to use . instead of !

If OrgSupeIndexCCode is the name of a form then it needs to
be preceeded by Forms!

 

[Guest]

Thanks, the answer was in putting in the IS NULL AND putting in the ""
qualifiers, It was leaving out the "" qualifier that was giving me the
#error.

What values are in LinkSupe? Is LinkSupe a text field or a number field?
If text field can LinkSupe contain a zero length string?

Try trouble shooting by building up your statement a bit at a time. Do this
work?
MgrLvl: IIf([OrgSupeIndexCCode]![LinkSupe] Is Null,"E", "Not E")

If not, try
MgrLvl: IIf([OrgSupeIndexCCode]![LinkSupe] Is Null OR
[OrgSupeIndexCCode]![LinkSupe] = "" ,"E", "Not E")

Once that is working try the next step. I would try using the Val function
to force a number value since the Right function is going to return a string
MgrLvl: IIf([OrgSupeIndexCCode]![LinkSupe] Is Null OR
[OrgSupeIndexCCode]![LinkSupe] = "" ,"E",
IIF(Val(Right([OrgSupeIndexCCode]![LinkSupe],5) )> 60000,"S","Not S"))

If that works then add on the last part.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have tried IIf is null, NZ and several other functions. I still get
#error
in the results of my query. Any ideas?

MgrLvl: IIf([OrgSupeIndexCCode]![LinkSupe] Is
Null,"E",IIf(Right([OrgSupeIndexCCode]![LinkSupe],5)>60000,"S",IIf(Right([OrgSupeIndexCCode]![LinkSupe],5)
Between 1 And 60000,"M","E")))

MgrLvl:
IIf(Right(NZ([OrgSupeIndexCCode]![LinkSupe],"00000"),5)>60000,"S",IIf(Right(NZ([OrgSupeIndexCCode]![LinkSupe],"00000"),5)
Between 1 And 60000,"M","E")))

MgrLvl:
IIf(Right(NZ([OrgSupeIndexCCode]![LinkSupe],0),5)>60000,"S",IIf(Right(NZ([OrgSupeIndexCCode]![LinkSupe],0),5)
Between 1 And 60000,"M","E")))

Any other ideas?

 

[Marshall Barton]

John,

I was surprised that it worked with the !
Do you know when that became an allowed syntax?

 

[John Spencer]

No idea how long that has been around.

Its been possible since Access 97. I use the period separator (delimiter)
myself and I usually advise posters to do so. I do seem to recall that if
you used the expression builder (in 97) it would use the ! as a separator
instead of the period. And I just checked with 2000 (I've got it open) and
the expression builder does the same thing.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

No idea how long that has been around.

Its been possible since Access 97. I use the period separator (delimiter)
myself and I usually advise posters to do so. I do seem to recall that if
you used the expression builder (in 97) it would use the ! as a separator
instead of the period. And I just checked with 2000 (I've got it open) and
the expression builder does the same thing.

Well, I'll be dipped. Not being a fan of the expression
builder, I never noticed that. Since ! is not in any SQL
standard I ever seen, I won't use it either. I do need to
stop telling people that ! won't work though ;-)

 

[John Spencer]

No, you should keep that up, but change the statement to "!" may not work.
The suggested separator is ".".

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

No, you should keep that up, but change the statement to "!" may not work.
The suggested separator is ".".

Good words John, I'll keep it in mind.

 

[Guest]

First I had the Microsoft Scipt Message error, manage I think to elinimate
that one, but still having problems with the syntax Error.

Dennisl