Query Prompt Data Validation 关注

0
Avatar
Legacy Poster

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?

 

Doug

3 评论

0
Avatar
Domingo Rodriguez
版主
评论操作 固定链接

In BarTender's Database connection setup dialog, select the "Query" tab on the right node once you've select the "database" name on the left node (if you don't use the "Query" tab yet, make sure to setup a query prompt rather than setting up your own query via the "SQL" tab). Now click the "Query Prompts" button and press the "More Options..." button. You can either set up here a minimum number of characters to be automatically filled with " " if you don't enter sufficient characters at print time, or, if you wish something more advanced, you can click on "VB Script" and write some VB Scripting to first check if the data entered is numeric or alphanumeric, and then add 2 extra leading spaces if it's only numeric.

0
Avatar
Legacy Poster
评论操作 固定链接

I have this set-up all the way to the point where it looks like I need some VB Scripting. But, I don't know Visual Basic. Can someone help me with a script to test the data entered, if it consists of 4 numeric characters, then add 2 leading spaces?

0
Avatar
Domingo Rodriguez
版主
评论操作 固定链接

You can try using

 

if IsNumeric (value) Then
    value = "  " & value
End If
 
or 
 
if IsNumeric (value) Then
    value = "  " & CStr (value)
End If
 
But I'm not sure if the query prompt value will now match with the value on your database.
 

请先登录再写评论。