Re: Convert a Number data type to Text using a query
- From: John Spencer <spencer@xxxxxxxxx>
- Date: Thu, 31 Jul 2008 15:42:53 -0400
Or if you want to retain the null value then
IIF(TheField is Null, Null,CStr(TheField))
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
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:
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 an Empty String:
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")