Converting Number to Text

[HumanJHawkins]

Hi,

I have two tables. One of them has a text field, and the other has a numeric
(integer) field that serves a similar purpose. I want to connect (UNION
actually) the two tables, and store the text from the first table in the
same field as the number from the other.

For example:
SELECT TextID AS ID
FROM W1
UNION
SELECT NumericID AS ID
FROM W2

The above query results in a type mismatch error due to trying to store an
Int in a text field. I have tried the SQL CONVERT function, but the docs
seem to indicate that it is just for Dates. In any case, it hasn't worked
for me.

Any ideas?
Thanks!

[MGFoster]

HumanJHawkins wrote:

Hi,

I have two tables. One of them has a text field, and the other has a numeric
(integer) field that serves a similar purpose. I want to connect (UNION
actually) the two tables, and store the text from the first table in the
same field as the number from the other.

For example:
SELECT TextID AS ID
FROM W1
UNION
SELECT NumericID AS ID
FROM W2

The above query results in a type mismatch error due to trying to store an
Int in a text field. I have tried the SQL CONVERT function, but the docs
seem to indicate that it is just for Dates. In any case, it hasn't worked
for me.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
What db are you working in SQL server or Access (JET)?

SQL'r:

SELECT TextID AS ID
FROM W1
UNION
SELECT CAST(NumericID As VARCHAR(20)) AS ID
FROM W2

Or, if you like CONVERT():

CONVERT(VARCHAR(20), NumericID)

Access:

SELECT TextID AS ID
FROM W1
UNION
SELECT CStr(NumericID)
FROM W2

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQG40H4echKqOuFEgEQII0wCgiMyVf3hCs8jWqZ07WscJzN nvfREAn01H
HiEh8gvGqSaM0Tq5L2yY2T4m
=cLy3
-----END PGP SIGNATURE-----

[Allen Browne]

Try Str():
... UNION SELECT Str(NumericID) AS ID ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"HumanJHawkins" <JH******@HumanitiesSoftware.Com> wrote in message
news:kH*******************@newsread1.news.pas.eart hlink.net...

Hi,

I have two tables. One of them has a text field, and the other has a numeric (integer) field that serves a similar purpose. I want to connect (UNION
actually) the two tables, and store the text from the first table in the
same field as the number from the other.

For example:
SELECT TextID AS ID
FROM W1
UNION
SELECT NumericID AS ID
FROM W2

The above query results in a type mismatch error due to trying to store an
Int in a text field. I have tried the SQL CONVERT function, but the docs
seem to indicate that it is just for Dates. In any case, it hasn't worked
for me.

Any ideas?
Thanks!

[Bob Quintal]

"HumanJHawkins" <JH******@HumanitiesSoftware.Com> wrote in
news:kH*******************@newsread1.news.pas.eart hlink.net:

Hi,

I have two tables. One of them has a text field, and the other
has a numeric (integer) field that serves a similar purpose. I
want to connect (UNION actually) the two tables, and store the
text from the first table in the same field as the number from
the other.

For example:
SELECT TextID AS ID
FROM W1
UNION
SELECT NumericID AS ID
FROM W2

The above query results in a type mismatch error due to trying
to store an Int in a text field. I have tried the SQL CONVERT
function, but the docs seem to indicate that it is just for
Dates. In any case, it hasn't worked for me.

Any ideas?
Thanks!

You could wrap the numericID field in the format() function. This
returns a text string. Or wrap it in the Cstr() function.

Bob Q