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?
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)