Validation of query parameters?

[Ellen]

Hello,
I've designed a select query which uses a parameter as
matching criteria. This way the query will filter
whatever parameter the user keys in. The field of
interest should always be a nine digit number. Is there a
way to have an error box come up when something other than
a nine digit number is entered in this query?

Thanks in advance,
Ellen

 

[Duane Hookom]

I doubt this can be done. Consider using a control on a form where you have
much more control and flexibility.

 

[Allen Browne]

The query cannot do that kind of validation, but you can use a text box on a
form. Then in the query, replace the parameter with a reference to the text
box, e.g.:
[Forms].[Form1].[Text0]

On your form, you can insist that the entry is 9 digits by setting the Input
Mask of the text box to:
000\-000\-000

Alternatively, if you do not like input masks, you could cancel the
BeforeUpdate event of the text box if its Len() is not 9, and use the
KeyPress event to set KeyCode to 0 if the character is not a digit.

 

[Guest]

Thanks for your reply. I'll do some study to understand
your solution.

-----Original Message-----
The query cannot do that kind of validation, but you can use a text box on a
form. Then in the query, replace the parameter with a reference to the text
box, e.g.:
[Forms].[Form1].[Text0]

On your form, you can insist that the entry is 9 digits by setting the Input
Mask of the text box to:
000\-000\-000

Alternatively, if you do not like input masks, you could cancel the
BeforeUpdate event of the text box if its Len() is not 9, and use the
KeyPress event to set KeyCode to 0 if the character is not a digit.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,
I've designed a select query which uses a parameter as
matching criteria. This way the query will filter
whatever parameter the user keys in. The field of
interest should always be a nine digit number. Is there a
way to have an error box come up when something other than
a nine digit number is entered in this query?

Thanks in advance,
Ellen

.

 

[Guest]

Thanks, Duane. What you've suggested should be easy to do.

Ellen