MS Access VBA - Retrieve DAO.Field.Value as verbatim string

[ironfelix717]

Hi,



Before I write a function to do this, I figured I would consult here because it seems like a rather silly thing....

I am looking to get the "verbatim" value of a field.

For example, If the field's value is $100.00 then getting field.value in VBA returns "100"
Even more strange is that if the field's value is $100.50 then getting field.value returns "100.50" (it now treats it as a double?)

My desire is when the fields value is $100.00 then i can retrieve a string that is "$100.00" - currency sign and its level of precision.

This would be my desire for not just currency types but all (most) types.

MS Documentation points out.... https://docs.microsoft.com/en-us/off...e-property-dao

The setting or return value is a Variant data type that evaluates to a value appropriate for the data type, as specified by the Type property of an object.

The reason I need this is because List Boxes don't support individual formatting of rows (lame) - when the listbox rowsource is set to "Value List"... When set to RowSource, the listbox will
inherit the rowsource field data type and present them in the listbox as such. Unfortunately, my listbox must remain as a Value List rowsource type for this application.

Any ideas besides writing a custom function?

Thanks

[June7]

If listbox is set for Value List then how is the items list created?

Maybe you want to use Format() function.

[CJ_London]

by 'verbatim' I presume you mean the underlying value - so the underlying value for $100.00 is actually 100, for $100.50 is 100.5 - what you are seeing is a formatted view of the data. Yes/No fields are stored as -1 and 0, dates are also stored as a number (today is 43775).

Value lists are by their nature strings, so you cannot turn it into a variant

Your requirement is not too clear - do you mean a single list

100
$100
$100.50

or do you mean a list for 100, another for $100 and a third for $100.50?


In any event your link refers to the referenced value of a field - a field can be one of many datatypes, but all returned through one variable and it is that variable which is a variant.

So you can use the type property to determine whether the value is an integer, long, double, currency, date, string etc and then as June suggests, use the appropriate format parameter to display the data as required.

you can convert a number to a string just by adding a quote

myNum=100
myString="" & myNum=100
myNum=100.5
myString="" & myNum=100.5

however that will ignore currency

myNum=$100
myString="" & myNum=100
myNum=$100.5
myString="" & myNum=100.5

EDIT: this is what happens when you append a field to your valuelist anyway

[ironfelix717]

Maybe you want to use Format() function.

June7,
Format function will format the data into string perhaps, but this assumes you know the field's datatype to begin with.
You will have to accept that I can only use Value List in this instance.

Your requirement is not too clear - do you mean a single list

Ajax,
Your reply is all over the place. i don't think i could've been more clear.
I need the "verbatim" value, which is poor terminology indicating I need the field's FORMATTED value as a string.

So, it appears a custom function to do this "conversion" is necessary.