Query Prompt Data Validation

Legacy Poster

• 24. Juni 2013 18:42

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

0

• 

  Domingo Rodriguez

  Moderator

  ★ BarTender Hero ★

  • 25. Juni 2013 08:05

  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
• 

  Legacy Poster

  ★ BarTender Hero ★

  • 25. Juni 2013 17:29

  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
• 

  Domingo Rodriguez

  Moderator

  ★ BarTender Hero ★

  • 26. Juni 2013 07:08

  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.

   

  0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.