Still getting #error in the results

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
..
 
PatT123 said:
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")))
Click to expand...


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!
 
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.

John Spencer said:
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
..

PatT123 said:
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?
Click to expand...
Click to expand...
 
John,

I was surprised that it worked with the !
Do you know when that became an allowed syntax?
 
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
..
 
John said:
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.
Click to expand...


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 ;-)
 
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
..
 
John said:
No, you should keep that up, but change the statement to "!" may not work.
The suggested separator is ".".
Click to expand...


Good words John, I'll keep it in mind.
 
First I had the Microsoft Scipt Message error, manage I think to elinimate
that one, but still having problems with the syntax Error.

Dennisl
 
Back
Top