Re: Convert a Number data type to Text using a query



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

Klatuu wrote:
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")

.