Query output containing different Number format.

[Neil07979]

All,

I have a simple database that stores values against KPI's. Each KPI is different - and as such can be in different formats (%, #, $ , etc)

Is it possible, when querying the data - to have the results of the data represented in the required formats?

The data at present is in a number format, and against each record I have created a field with the format in (%, #, $), Is it possible to create a field in the query that will look at the format field and apply that to the number?

Any idea's welcome.

Thanks.

 

[DCrake]

Each column can only have one format type unless you convert the KPI to a string.

To do this you would have some logic to be able to distinguish between the different formats expected. Having established this then you would need to crete a function to convert the incoming raw data to the outgoing formatted data.


Aircode:

Public Function Reformat(ByVal AnyValue,fType As Integer) As String

Select Case fType
    Case 1 ' Currency
       AnyValue = CStr(Format(AnyValue,"Currency"))
    Case 2 ' Double
       AnyValue = CStr(CDbl(AnyValue))
    Case 3 ' etc
       etc
End Select

Reformat = Anyvalue

End Function