queries

[azpat via AccessMonster.com]

I have a join that leaves null values, and is supposed to in a query result.
Only supervisors have a supervisor code. Employees do not. I am trying to
evaluate that code to give the manager level.

Manager, Supervisor, Employee. Employee will be null. Managers are less
than 60000 and Supervisors are greater than 60000. I continue to get #error
in my result. I have tried both NZ and several other error functions, with
and without a direct evaluation to 0.

Any suggestions?


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

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

 

[Smartin]

I have a join that leaves null values, and is supposed to in a query result.
Only supervisors have a supervisor code. Employees do not. I am trying to
evaluate that code to give the manager level.

Manager, Supervisor, Employee. Employee will be null. Managers are less
than 60000 and Supervisors are greater than 60000. I continue to get #error
in my result. I have tried both NZ and several other error functions, with
and without a direct evaluation to 0.

Any suggestions?


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

I don't think you can use Between in the criteria expression for IIf.
Perhaps this:

MgrLvl: IIf(Right(Nz([OrgSupeIndexCCode]![LinkSupe]), 5) <= 60000,
"M", IIf(Right(Nz([OrgSupeIndexCCode]![LinkSupe]), 5) > 60000, "S","E"))

 

[Rob Parker]

I pasted the two expressions you gave into a query, and find that the first
gives #error for a null in the LinkSupe field, but the second does not - it
gives results which seem to be fine, using data which consists simply of
numeric characters in a field with a datatype of either text or long
integer.

However, for a text field with mixed letters and numbers, the second
expression gives #error where the rightmost 5 characters are not numbers.
Perhaps that's your problem. If so, you'll need to parse only numeric
characters from the right of your string - I think you'd probably be best
writing a custom function for that (and it may be rather tricky).

HTH,

Rob