Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 How to convert SQL int column to string column ?

Author  Topic 

matkwan
Starting Member

36 Posts

Posted - 2003-05-02 : 12:32:59
Hi,

I am having problem trying to execute this SQL in Access:

select itemID + ' ' + itemname as item from tbl_stock

it outputs errors.

I think the problem is I am trying to use an integer type column and string type column to form a new column.

I guess to solve the problem is to convert the int column to string, am I correct ? and How ?

Matthew

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-02 : 13:11:35
Yup that's exactly it...you've got a datatype conversion problem. You need to convert the id column to be char datatype.

Let's see if I can scan the grey matter data banks.....it's been a while...

Try:

select Format$(itemID) + ' ' + itemname as item from tbl_stock



Brett

8-)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-02 : 13:22:14
Access and VBA in general uses double quotes not single quotes as a string delimiter.

you don't need to convert ... it does it implicitly. Also, I prefer the & operator because it makes it very clear you are doing a concatenation and not addition if the datatypes or fieldnames are ambigious.

select itemID & " " & itemname as item from tbl_stock


- Jeff
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-02 : 15:00:48
Oh that's right....I forgot,

But I did test the method I put forward, and it does work that way...

I'm sure it failed because the '+' operator thought you were try to do Math...



Brett

8-)
Go to Top of Page
   

- Advertisement -