473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Converting Number to Text

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!
Nov 12 '05 #1
3 11237
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-----

Nov 12 '05 #2
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!

Nov 12 '05 #3
"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
Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Ciar?n | last post by:
I have a table with over a million rows and one of the fields contains amounts of money in text format. What is the most efficient way of converting this field to a number format that I can sum...
2
by: nanookfan | last post by:
Hi all, I'm having a bizarre problem converting XML files to HTML using an XSLT. The problem is only occuring in my Netscape 7.0 browser. What makes it more bizarre is that it is only...
11
by: Chris Online | last post by:
Hi all, I'm using C++ Builder5. I want to get data from an edit-box and send it to a development kit. The dev-kit can only receive char and no char* here's a part of my code: char* Data_byte...
2
by: Asbjørn Ulsberg | last post by:
Hi. I'm trying to convert Brady Hegberg's great RTF2HTML VB 6.0 module to C#. I've managed to convert the VB code to VB.NET, which gave me the following code: Option Strict On Option...
4
by: Clark Stevens | last post by:
I have a program that I'm converting from VB6 to VB.NET. It reads in a text file containing barcode numbers and their corresponding descriptions. Then the user enters the barcode number and the...
11
by: Penfold | last post by:
I'd appreciate help converting student average test scores into grades. My problem is that I need to allocate one of about 20 grades (3a,3b,3c,4a,4b,4c etc through to 8c plus a couple of others)....
1
by: UKuser | last post by:
Hi Guys, I have a program which converts Excel spreadsheets to Javascript and allows interactivity. However it can't convert it to PHP, which is obviously better for users to view (in case J/S...
3
by: RG | last post by:
I am reading in from a serial port a 16 bit number from 0 to 65536. It is being read as a string from my microcontroller into VB using DEC5 or 5 digits are displayed. I need to display this value...
3
by: Jef Driesen | last post by:
How can I convert a date string to a number (e.g. a time_t value or a tm struct)? I know about the strptime function, but then I have to know the format string. And that is a problem. I'm trying...
0
Frinavale
by: Frinavale | last post by:
Convert a Hex number into a decimal number and a decimal number to Hex number This is a very simple script that converts decimal numbers into hex values and hex values into decimal numbers. The...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.