asked on

How can I convert or cast data in a query to an Access Database?

I need to query an Access database and the query involves joining two tables.
The joining column on table1 (named ID) has a datatype of 'Number: Long integer'. The joining column on table2 (named PartInfo)has a datatype of 'Text'. The PartInfo contains data that is the string representation of the ID column. I'm not sure why the PartInfo column uses a text datatype but the db is output from another application so I have no control over it. When I write my query, if I use CAST, then I get an error "IErrorInfo.GetDescription failed with E_FAIL(0x80004005)".. If I use CONVERT, then I get an
error "Undefined function 'CONVERT' in expression." I've researched and found that the E_FAIL error is thrown when a Keyword is used in the query, but as you can see, I've used brackets around all of my table and field names. I've also found that the Jet engine does not support the CONVERT function. How can I write this query so that I can join the two tables by these two columns? The error is thrown on the line: MyReader = MyConmand.ExecuteReader

'Using CONVERT:
                MyCommand = New OleDbCommand("", MyConnection)
                MyCommand.CommandText = String.Format("Select CONVERT(smallint, [Label Sequence:{0}].[PartInfo]) as pi, From [{0}] Inner Join [Label Sequence:{0}] On [{0}].[ID] = [Label Sequence:{0}].[ID]", s)
                MyReader = MyCommand.ExecuteReader
 
'Using CAST:
                MyCommand = New OleDbCommand("", MyConnection)
                MyCommand.CommandText = String.Format("Select CAST([Label Sequence:{0}].[PartInfo] AS smallint) as pi, From [{0}] Inner Join [Label Sequence:{0}] On [{0}].[ID] = [Label Sequence:{0}].[ID]", s)
                MyReader = MyCommand.ExecuteReader
 
'Using CAST (with a comma instead of AS):
                MyCommand = New OleDbCommand("", MyConnection)
                MyCommand.CommandText = String.Format("Select CAST([Label Sequence:{0}].[PartInfo], smallint) as pi, From [{0}] Inner Join [Label Sequence:{0}] On [{0}].[ID] = [Label Sequence:{0}].[ID]", s)
                MyReader = MyCommand.ExecuteReader

Open in new window

SOLUTION

Rob Siklos

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Jez Walters

How about this:

SELECT [Label Sequence:{0}].[PartInfo] as pi
FROM [{0}] INNER JOIN [Label Sequence:{0}]
ON [{0}].[ID] = CLng([Label Sequence:{0}].[ID])

Open in new window

Jez Walters

Or, if you prefer:

SELECT [Label Sequence:{0}].[PartInfo] as pi
FROM [{0}] INNER JOIN [Label Sequence:{0}]
ON CStr([{0}].[ID]) = [Label Sequence:{0}].[ID]

Open in new window

ASKER CERTIFIED SOLUTION

Jez Walters

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

paul_hitchcock

ASKER

Thanks for your quick responses.
It seems so obvious now that I know the answer, but doesn't it always?