Convert a Number data type to Text using a query

[dfredriksson]

I have data coming from different sources. I would like to control the
formats when either using a Make Table query or an Append query. In some
sources, a column might be a number data type in another source it might be a
text data type.

I want to be able to convert all the data from the different sources to a
text data type.

Is there a way to convert data type from one table to another using a
formula in a query?

Thanks
Dana

 

[bhicks11 via AccessMonster.com]

Hi Dana,

You can append a number data type to a text data type field with a query.
Have you tried it and had problems? What errors are you having?

Bonnie
http://www.dataplus-svc.com

 

[Klatuu]

You can use any of the type conversion function to change data types.
In your case, you want to convert a number to text, so you could use the
Cstr() function.

So Basically, in the Query builder, it would be:

TextField: Cstr([NumberField])

Now, there is an issue that if there are any Null values in NumberField, you
will get an Invalid use of Null error, so you need to trap that using the Nz
function and you need to decide what to return for records with a Null Value.
Here are two examples:

Returns "0"
TextField: Cstr(Nz([NumberField],0))
Returns an Empty String:
TextField: Cstr(Nz([NumberField],""))

And, if you need a specific format, you can add the Format() function. In
this example, we want to alway show two decimal places, but if [NumberField]
is Null, we want to return an empty string:

TextField: Format(Cstr(Nz([NumberField],"")), "0.00")

 

[Marshall Barton]

I have data coming from different sources. I would like to control the
formats when either using a Make Table query or an Append query. In some
sources, a column might be a number data type in another source it might be a
text data type.

I want to be able to convert all the data from the different sources to a
text data type.

Is there a way to convert data type from one table to another using a
formula in a query?

Check VBA Help for CSrt and the other convert functions.

 

[John Spencer]

Or if you want to retain the null value then

IIF(TheField is Null, Null,CStr(TheField))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County