NUmber Format in SQL

[bstice]

Hello all,

I use SQL to populate several listboxes on a form and the number format for one of the columns isn't passing from the table to the listbox correctly. Is there was way in SQL to define the format for that field?

Here is an example of the statement that I have right now:
str = "SELECT MasterBacklog.[Master Customer Name], MasterBacklog.[Order Number], MasterBacklog.[Aging Bucket], " _
& "MasterBacklog.[Order Age], MasterBacklog.[Backlog Value], MasterBacklog.[Product ID] FROM [MasterBacklog]" _
& "WHERE (((MasterBacklog.[LEVEL 3])like '*' & '" & industryLST & "' & '*') AND ((MasterBacklog.[Deferred Revenue]) Is Null))ORDER BY MasterBacklog.[Master Customer Name], MasterBacklog.[Order Number] ;"

I would like the Backlog Value to show in the standard format with 2 decimal places. Thanks for help! I always award reputation points. Thanks

Brennan

 

[boblarson]

You should be able to do this:

str = "SELECT MasterBacklog.[Master Customer Name], MasterBacklog.[Order Number], MasterBacklog.[Aging Bucket], " _
& "MasterBacklog.[Order Age], Format(MasterBacklog.[Backlog Value],"Standard"), MasterBacklog.[Product ID] FROM [MasterBacklog]" _
& "WHERE (((MasterBacklog.[LEVEL 3])like '*' & '" & industryLST & "' & '*') AND ((MasterBacklog.[Deferred Revenue]) Is Null))ORDER BY MasterBacklog.[Master Customer Name], MasterBacklog.[Order Number] ;"

 

[bstice]

Thanks Bob - works great!