Query Prompt Data Validation Follow
I am creating a database connection that seems to require a somewhat unique validation (or at least I haven’t figured out how to set it up).
The field is 6 characters, alphanumeric. It is a text field in the table.
Here is the SQL I have set up so far:
SELECT DISTINCT "MATNO", "MATERIAL", "UOM" FROM "CRWRITER"."cv_SSINVENT" WHERE "MATNO" = '?MA NO'
I want the user to be able to enter in alphanumeric characters, and not worry about leading spaces.
If the record is numeric, it has to be padded on the left with spaces to reach the 6 character minimum. For example:
If the MA# they type in is "1001", it needs to be padded with 2 leading spaces.
However, If the record is alphanumeric, it does not require padding to reach the minimum. For example, if the MA# is "PD1" it does not require that it be padded.
How do I set up the query option so the user does not have to manually enter the spaces if the record desired is only numeric?