473,889 Members | 1,357 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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!
Jul 20 '05 #1
3 142404
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:::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-----

Jul 20 '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.

"HumanJHawk ins" <JH******@Human itiesSoftware.C om> wrote in message
news:kH******** ***********@new sread1.news.pas .earthlink.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!

Jul 20 '05 #3
"HumanJHawk ins" <JH******@Human itiesSoftware.C om> wrote in
news:kH******** ***********@new sread1.news.pas .earthlink.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
Jul 20 '05 #4

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

Similar topics

0
9860
by: Sven Mayer | last post by:
Assume a database (e.g. Oracle) field is declared as a) NUMBER (length=10) b) NUMBER (length=26) c) VARCHAR2 (length=1) d) VARCHAR2 (length=50) e) DATE How do I retrieve the contents from these resultset fields and put it into a (numeric) java resp Date field? For other field types it is already clear e.g.):
3
4684
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 on? Regards, Ciarán
5
7151
by: Mukesh Kumar | last post by:
hi I am trying to pass range parameters to crystal reports from my .net environment. The start and end values of the range parameters are of currency type. I need to convert the number to currency data type for which I am using CCur function provided by .Net, but it throws an error stating "The name 'CCur' does not exist in the class or namespace. Any help would be appreciated. Thanks in advance Mukes
3
11356
by: HumanJHawkins | last post by:
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
3
11156
by: Homer Simpson | last post by:
How do I convert a value in a textbox to a real number so I can perform some math ops on it? I understand I will need to validate the textbox's input to make sure it is a real number and not a string so any suggestions along those lines are also appreciated. Thanks, Scott
2
2560
by: mac | last post by:
Assume you have a textbox named "displayValue" What is the difference (if any) between these two approaches: 1.) float var; var = 0.42F; displayValue.Text = var.ToString 2.)
7
4491
anukagni
by: anukagni | last post by:
Hi friends, I want to get this thing happen.. I want to convert the number to word i.e. if i entry 1 in an text box then it should give the reflect to One in an lable .. Is this possible i have gone through the forum i got some result .But it is not understandable please give me clear step by step explanation .This is my humble request.. Thanks anukagni
5
3585
by: ria3 | last post by:
Hi, I have to write a program that will alow the user to specify a number (decimal, binary, or hexadecimal) and a base to convert to (decimal, binary, or hexadecimal) using subroutine methods and menu - so that the user can choose their options. I know how to convert with these number systems on paper using a pencil. However, I am not sure how to covert them in a java program. (I am using ready to program). Is there a formula I could use?...
0
1282
by: Tania Louie | last post by:
Hi, I just need help coverting a number to a time in SQL. Currently it is returning a number of 1260 for the time and I need it to show as the actual time of 9:30. Not sure what to use here. Thanks :)
0
9807
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11188
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10889
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10441
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9606
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7993
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7150
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5828
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4647
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.