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")


Relevant Pages

  • tDateTimePicker behavior
    ... Is there a way to get tDateTimePicker to behave in the following ways ... set the format to 'gg' and then set the format to the empty string ... been using Orpheus tOvcDateEdit for dates, but I'm trying to get away ...
  • Re: Adding field: Property value is too large
    ... lies with the text box and the default value of Empty String. ... I'd recommend not setting the default value to an empty string. ... such as Default Value, Format and Input Mask. ... and I want the Format to be m/d/yyyy ...
  • Re: If statement
    ... I'd use a real date and an empty string: ... I have an if statement that looks at the date on another spreadsheet. ... through September work perfectly but for some reason despite the fact that ... the look up field is formatted as text but I have tried to format it as a ...
  • Re: Microsoft Query
    ... empty string is a string, ... details how Jet determines the data type for an Excel column, ... While the format is used to determine data type, ...