Skip to main content
  • Home
  • Forums
  • Developers Forum
  • Formatting a number when using command "Database: Read from database using SQL statement..." when using Excel as database

Does anyone know how to format a number with commas/decimal when using command "Database: Read from database using SQL statement..." when using Excel as the database?

 

Purpose:

I’m connecting to an Excel spreadsheet as a database, looping through each row and saving the data into variables that will be used to populate a contract.

 

Problem:

I’m having a hard time formatting the Annual Salary field from my Excel file into my $vAnnualSalary$ variable. 

 

This is my SELECT statement:

 

SELECT >Employee First Name], rEmployee Last Name], asAnnual Salary] from lSheet1$$] 

 

Though the Annual Salary field is formatted correctly on the Excel spreadsheet (numerical formatting with commas and decimals, no currency symbol), the value that is saved to my $vAnnualSalary$ variable (after the SQL command is executed) doesn’t contain any formatting (which I guess is expected with SQL).

 

Does anyone know how I can apply numerical formatting to the Annual Salary so that it contains commas and a decimal?

 

Ex: Excel value on spreadsheet: 50,000.00

      Value returned from SELECT statement: 50000

       Value as I need it displayed in my $vAnnualSalary$ variable: 50,000.00

 

Thank you

Hi @Val 6777 ,

Based on the above, would you like to see final output in this format 50,000.00 is correct ?

 
 

Hi @Val 6777,

 

You can use VB script to do this.

 

VB Script Code:

Function FormatAsCurrency(AnnualSal)
  
  Dim value1
  Dim value1Result
  
  value1 = AnnualSal
  
  value1Result = FormatCurrency(value1)
  
  FormatAsCurrency = value1Result

End Function

 

Call this function in AA by passing $vAnnualSalary$ variable as a parameter to function. Then you will see the output as $50,000.00 by doing string operations you can remove $ sign and use the value.

Code snippet is below.

Hope it will help.

 

Thanks,

Hemantha Pindra

@HemanthaPindra , this is exactly what I needed to solve my problem. Thank you so much!

Reply


Terms & ConditionsCookie settings