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]

Re: Converting Number to Text

HumanJHawkins wrote:
[color=blue]
> 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.[/color]

-----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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQG40H4echKq OuFEgEQII0wCgiM yVf3hCs8jWqZ07W scJzNnvfREAn01H
HiEh8gvGqSaM0Tq 5L2yY2T4m
=cLy3
-----END PGP SIGNATURE-----

[Allen Browne]

Re: Converting Number to Text

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.

"HumanJHawk ins" <JHawkins@Human itiesSoftware.C om> wrote in message
news:kHmbc.1323 4$lt2.11551@new sread1.news.pas .earthlink.net. ..[color=blue]
> Hi,
>
> I have two tables. One of them has a text field, and the other has a[/color]
numeric[color=blue]
> (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!
>
>[/color]

[Bob Quintal]

Re: Converting Number to Text

"HumanJHawk ins" <JHawkins@Human itiesSoftware.C om> wrote in
news:kHmbc.1323 4$lt2.11551@new sread1.news.pas .earthlink.net:
[color=blue]
> 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!
>[/color]
You could wrap the numericID field in the format() function. This
returns a text string. Or wrap it in the Cstr() function.

Bob Q