Converting nvarchar data type to number in MS Access Passthrough query for subsequent calculations-CodePudding

I'm working with a SQL passthrough to SQL Server query in a MS Access environment. I'm displaying the query results in an Access form and performing a display calculation to it in a text box.

The data type is nvarchar in SQL Server. In the passthrough query I trim spaces in the select statement. All other uses of the data support this remaining a text type. But I would like to subtract a value when displayed in one particular place.

The passthrough looks like this:

Select
  ,Trim([NumberStoreAsTextWithSpaces]) as NumberStoredAsTextNoSpaces
  ,[Other stuff]
  ,[Other stuff1]
  ,[Other stuff2]
  ,[Other stuff...]
From [dbo].[Table of Numbers of Stuff]

When I get to Access the control data source of the form is the passthrough query, and in the text box control the control data source is:

=[NumberStoredAsTextNoSpaces]-42.0

This results in #Type! as a result in the text box.

Attempting to CDbl() prior to the subtraction operation results in a zero or null value minus the 42.0 and simply shows as -42.0 in the text box.

I don't necessarily care if there is a bad data item somewhere in the query, the Select ... Trim jumps right over that. And the Access form should only error at the cursor.

What am I missing?

CodePudding user response：

Since this is a pt query, then you can cast it like this:

Select
,CAST(Trim([NumberStoreAsTextWithSpaces]) AS real) as NumberStoredAsTextNoSpaces
,[Other stuff]
,[Other stuff1]
,[Other stuff2]
,[Other stuff...]
From [dbo].[Table of Numbers of Stuff]

So, for cast:

 int (Access long)
 float (Access double)
 real  (Access single)
 money (Access currency)