Issues formatting numbers in Access from SQL Server query

Question

I have a production database that uses a SQL server back end with tables linked to an Access front end. For performance reasons, I am trying to write most of my report queries as SQL views and simply passing parameters in from Access.

The problem I'm running into though is that I don't seem to be allowed to format fields that were calculated in the SQL view.

For example, I have a table:

CaseLot    Drums    GrossWeight    %H2O
   01        10       10,301       5.21

Drums, GrossWeight, and Drums are all decimal data types but each have varying scale.

In my view I include the values from the above table, but also the following calculated fields:

[GrossWeight] - 19.6*[Drums] AS NetWeight
([GrossWeight] -19.6*[Drums])*(100-[%H2O])/100 AS DryWeight

When I pull these calculated fields through to Access, I have no issue formatting the NetWeight field, but it won't let me for the DryWeight field. It is really frustrating, because I would like to display the DryWeight field to only two decimal places and it won't do it.

I have also tried applying the Round() function in Access, but it seems to give me a "circular reference" error when I include a scale to round to. Round(DryWeight) works to round it to 0 decimal places, but Round(DryWeight,2) give me an error.

What is happening here?

Answer 1

Its almost as if Access is recognizing these calculated numbers as strings.

It is. I recreated the issue using SQL Server 2005 and Access 2010. I have a View in SQL Server named [CaseLotView] ...

CREATE VIEW [dbo].[CaseLotView]
AS
SELECT
    CaseLot, 
    Drums, 
    GrossWeight, 
    [%H2O], 
    GrossWeight - 19.6 * Drums AS NetWeight, 
    (GrossWeight - 19.6 * Drums) * (100 - [%H2O]) / 100 AS DryWeight
FROM
    dbo.CaseLotData

...and when I create a linked table in Access and open it I see the following:

Notice that the [NetWeight] value is right-aligned (interpreted as numeric) while the [DryWeight] value is left-aligned (interpreted as string).

One workaround would be to create an Access query based on the linked table that converts [DryWeight] to numeric using the CDbl() function:

SELECT 
    dbo_CaseLotView.CaseLot, 
    dbo_CaseLotView.NetWeight, 
    CDbl([DryWeight]) AS dblDryWeight
FROM dbo_CaseLotView;

Reports based on that query will recognize [dblDryWeight] as numeric and offer the expected formatting options.

(Note that while there is a CDec() function in VBA it does not work in Access queries - ref: here.)