SQL Pass through query returning numbers as strings woes (1 Viewer)

Minty

AWF VIP
Local time
Today, 14:56
Joined
Jul 26, 2013
Messages
10,355
Evening all - I have a passthrough query that is generating a dynamic Pivot result. It has to be dynamic as the number and names of customers are fluid.
The problem is that Access thinks the values are text.
They are definitely cast as Decimal in SQL (Azure) but if I use the pass-through results as a record set in PPT or Excel to populate chart data, it don't work!

If I copy and paste the results straight from SSMS into Excel they are treated as numbers. Access is the guilty party here.
Any ideas?

The data looks like this.

Report DateCustomer 1Customer 2 Customer 3Customer 4Customer 5
30-Sep-1929.9920.0019.9920.0019.95
07-Oct-1929.9920.0019.9920.0019.95
14-Oct-1929.9920.0019.9920.0019.95
21-Oct-1929.9920.0018.9920.0019.95
28-Oct-1929.9920.0018.9920.0019.95
04-Nov-1929.9920.0019.9920.0019.95
11-Nov-1929.9920.0019.9920.0019.95
18-Nov-1929.9920.0018.9920.0019.95
25-Nov-1924.9920.0018.9920.0019.95
02-Dec-1924.9919.0018.9920.0019.95
09-Dec-1929.9919.0019.9920.0019.95
16-Dec-1929.9920.0019.9920.0019.95
23-Dec-1929.9920.0019.9920.0019.95
30-Dec-19
24.99​
20.0019.9920.0019.95
 

Minty

AWF VIP
Local time
Today, 14:56
Joined
Jul 26, 2013
Messages
10,355
Try casting as float instead. Most likely your regional settings use a comma as a decimal point
Thank you namliam!
Bizarrely this works... Even though the data and regional settings all use the decimal points. I had tried Money & Decimal without any joy.
The data above is straight from SSMS before I changed it. Go figure.
 

Users who are viewing this thread

Top Bottom