#error

[Guest]

Howdy folks!

I've got a query expression that is giving me a #error when the field
[Process Key] does not contain "A". It goes like this: Age: Mid([Process
Key],InStr([Process Key],"A"),1). When the string does not contain the
character "A", an error results. There are several other expressions with
different criteria (improper use of the word, I know) also, but with similar
results. How can I manage these errors? I plan to use the results of the
query in a form, but don't want "#error" to appear in the text boxes.
Ideally, I would like to able to use some sort of criteria (proper use this
time) to filter out undesireable results. Please let me know what you think.

 

[Guest]

The Mid function requires a number greater than 0. If the string doesn't have
an "A", InStr returns a 0. If you have a Null, InStr returns a Null which is
just as bad for the Mid function.

So what do you want to happen if [Process Key] does not contain an A or is
null?

You could eliminate the A problem by putting the following in the query then
using > 0 as the criteria.

HasA: InStr([Process Key],"A")

 

[Guest]

Terrific! I believe that will work just fine. Thank you. BTW, is there a
handy dandy reference for VBA functions and descriptions that is easier to
use and negotiate than the MS Help Library? I'm constantly having to ask,
"Is there a function that does this?"
--
Why are you asking me? I dont know what Im doing!

Jaybird

The Mid function requires a number greater than 0. If the string doesn't have
an "A", InStr returns a 0. If you have a Null, InStr returns a Null which is
just as bad for the Mid function.

So what do you want to happen if [Process Key] does not contain an A or is
null?

You could eliminate the A problem by putting the following in the query then
using > 0 as the criteria.

HasA: InStr([Process Key],"A")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Howdy folks!

I've got a query expression that is giving me a #error when the field
[Process Key] does not contain "A". It goes like this: Age: Mid([Process
Key],InStr([Process Key],"A"),1). When the string does not contain the
character "A", an error results. There are several other expressions with
different criteria (improper use of the word, I know) also, but with similar
results. How can I manage these errors? I plan to use the results of the
query in a form, but don't want "#error" to appear in the text boxes.
Ideally, I would like to able to use some sort of criteria (proper use this
time) to filter out undesireable results. Please let me know what you think.