How to stop Error# message

[Guest]

Hi.

I have the following code in a query field which takes the content of field
Function, which contains, say, Maths Mentor, English Mentor etc and produces
a new field containing just Maths, English etc. The problem is that when the
field Function does not contain anything, MentorSubject shows #Error which
causes a type mismatch error to appear when the associated report is opened.
Is there a way I can stop #Error appearing for emply Function fields?

Thanks, JohnB

MentorSubject: Replace([tblSECFunctionList].[Function]," Mentor","")

 

[Guest]

The most obvious way to fix this problem would simply be to put "Function Is
Not Null" in your WHERE clause. However, it sounds like you want to display
those records that are null, so I would try something like this:

MentorSubject: IIf(Function Is Not Null,
Replace([tblSECFunctionList].[Function]," Mentor",""), "")

Theoretically, this should return an empty string if your Function column is
null.

-Chris

 

[Guest]

Thanks for the quick reply Chris. You are right -I want to display those
records that have no Function. I'll try your modification tomorrow, when I
get back to the office and I'll reply again then.

Cheers, JohnB

The most obvious way to fix this problem would simply be to put "Function Is
Not Null" in your WHERE clause. However, it sounds like you want to display
those records that are null, so I would try something like this:

MentorSubject: IIf(Function Is Not Null,
Replace([tblSECFunctionList].[Function]," Mentor",""), "")

Theoretically, this should return an empty string if your Function column is
null.

-Chris

Hi.

I have the following code in a query field which takes the content of field
Function, which contains, say, Maths Mentor, English Mentor etc and produces
a new field containing just Maths, English etc. The problem is that when the
field Function does not contain anything, MentorSubject shows #Error which
causes a type mismatch error to appear when the associated report is opened.
Is there a way I can stop #Error appearing for emply Function fields?

Thanks, JohnB

MentorSubject: Replace([tblSECFunctionList].[Function]," Mentor","")

 

[Guest]

Hi again Chris.

Very sorry for not getting back to you before now - been a bit busy at work.
Anyway, your suggestion does the job. I just had to modify it slightly, by
fully defining [tblSECFunctionList].[Function] to:

MentorSubject: IIf([tblSECFunctionList].[Function]," Is Not Null,
Replace([tblSECFunctionList].[Function]," Mentor",""), "")

Thank you very much for your help.

JohnB

The most obvious way to fix this problem would simply be to put "Function Is
Not Null" in your WHERE clause. However, it sounds like you want to display
those records that are null, so I would try something like this:

MentorSubject: IIf(Function Is Not Null,
Replace([tblSECFunctionList].[Function]," Mentor",""), "")

Theoretically, this should return an empty string if your Function column is
null.

-Chris

Hi.

I have the following code in a query field which takes the content of field
Function, which contains, say, Maths Mentor, English Mentor etc and produces
a new field containing just Maths, English etc. The problem is that when the
field Function does not contain anything, MentorSubject shows #Error which
causes a type mismatch error to appear when the associated report is opened.
Is there a way I can stop #Error appearing for emply Function fields?

Thanks, JohnB

MentorSubject: Replace([tblSECFunctionList].[Function]," Mentor","")