Convert Number to Text

[Wink20TCU]

How can I conver a number to text in a query?

 

[Graham Mandeno]

Use the Format function. Here are some examples:

Format( 100, "0.00" ) gives "100.00"
Format( 42, "00000" ) gives "00042"
Format( 1234.5, "$#,##0.00") gives "$1,234.50"

 

[Ofer Cohen]

What do you mean convert number to text?

Is that what you mean,
Check this link on "Convert Currency ($500) into words (Five Hundred Dollars)"

http://www.mvps.org/access/modules/mdl0001.htm

 

[Graham Mandeno]

Good catch, Ofer! It didn't occur to me the OP meant numbers to words.

 

[Tom Wickerath MDB]

you can either use the CONVERT or CAST functions


CONVERT(varchar, 1)

 

[James]

can't you just use cstr()?
e.g.
SELECT cstr(NumField) as StrField FROM Table1

 

[Larry Linson]

How can I conver a number to text in a query?

Do you mean "how do I convert 100 to 'one hundred'" or do you mean how do I
convert the numeric value to the corresponding numeric characters? If the
second is what you are asking, Graham has answered. If the other, there are
a few conversion procedures available...

The first place I look for procedures to handle common needs such as this is
the FAQ at http://www.mvps.org/access. Surely enough, the very first entry
in the Modules section http://www.mvps.org/access/modules/mdl0001.htm, is
"Convert currency into words". I haven't examined it in depth, but suspect,
if you don't want dollars and cents, that it wouldn't be hard to modify

Searching the archives of this newsgroup, and of the USENET newsgroup,
comp.databases.msaccess using http://groups.google.com with the text
"numbers to text" gives a number of hits. Some of those may do exactly what
you want without change, but ALWAYS carefully test posted code before
relying on it.

Larry Linson
Microsoft Access MVP

 

[Wink20TCU]

I am trying to join like columns in two DB2 tables, and the data is nothing
but numbers.

Unfortunately, the types do not match (Text and Number), and when I create
the join, the "type mismatch in expression" error message appears.

I want to turn the column with a number type into text.

Use the Format function. Here are some examples:

Format( 100, "0.00" ) gives "100.00"
Format( 42, "00000" ) gives "00042"
Format( 1234.5, "$#,##0.00") gives "$1,234.50"
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

How can I conver a number to text in a query?

 

[Graham Mandeno]

You would get a better chance of matching values if you convert the text to
numbers, not the other way around.

For example, "12.3" will not match "12.30", but if they are both numeric
fields then they will be the same.

It's rather unorthodox, but you can use functions in a JOIN expression.
create a join as you would normally and then switch to SQL view and change:
INNER JOIN ... ON Table1.NumField = Table2.TextField
to:
INNER JOIN ... ON Table1.NumField = Val(Table2.TextField)

Note that you will not be able to switch back to grid design view.

The other way to do it is to leave the tables unjoined and use a WHERE
clause:
SELECT Table1.NumField, Table2.TextField, ... FROM Table1, Table2
WHERE Table1.NumField = Val(Table2.TextField)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am trying to join like columns in two DB2 tables, and the data is nothing
but numbers.

Unfortunately, the types do not match (Text and Number), and when I create
the join, the "type mismatch in expression" error message appears.

I want to turn the column with a number type into text.

Use the Format function. Here are some examples:

Format( 100, "0.00" ) gives "100.00"
Format( 42, "00000" ) gives "00042"
Format( 1234.5, "$#,##0.00") gives "$1,234.50"
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

How can I conver a number to text in a query?