Changing format on calculated field in a query

[ibvalentine]

I have a calculated field in which I am using a switch function. The function
calculates a bonus discount based on how many units a customer purchases. The
units purchased are listed in the Quantity field and the Quantity field is
used in my function. This query is based on one table and the Quantity field
in that table has a number datatype.

The switch function works fine but I can't change the formatting to a
percent format. There is nothing in the Format text box drop down menu in the
Field Properties dialog box. I tried typing in percent, but that didn't work.

Can anyone enlighten me?

 

[John Spencer]

Could you post the expression you are using?
When you use the switch function Access has no idea of the type of the data
that will be returned, so it does not display the format options based on
the type of data.

You can try forcing the data type (if a number is ALWAYS returned by the
swtich function)
CDbl(Switch(...))
should tell Access what type of data is being returned. However it will
blow up if switch returns a null or non-number value.

Or you could try
Format(Switch(...),"Percent")
The problem with this is that the value returned is a STRING and therefore
you will have trouble doing any math operations or sorting with it.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[ibvalentine]

Could you post the expression you are using?
When you use the switch function Access has no idea of the type of the data
that will be returned, so it does not display the format options based on
the type of data.

You can try forcing the data type (if a number is ALWAYS returned by the
swtich function)
CDbl(Switch(...))
should tell Access what type of data is being returned. However it will
blow up if switch returns a null or non-number value.

Or you could try
Format(Switch(...),"Percent")
The problem with this is that the value returned is a STRING and therefore
you will have trouble doing any math operations or sorting with it.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..




The switch function is as follows:

Switch(Sum([Quantity])>=150,0.15, Sum([Quantity])>=100,0.1,
Sum([Quantity])>=50,0.05, Sum([Quantity])<50,0))

I enclosed it in the CDbl() function and that did the trick. For some reason
Access was not interpreting the field type correctly. The switch uses the
Quantity fields, but it was necessary to force the data type. I did some
searching on the web and found one explanation made some sense. A guy name
Mike Ellison suggested that the ODBC driver sometimes misinterprets the field
type. In these cases, a CDbl function can be used to force the datatype.

Thanks for your help John. I would have never figured this one out.