Currency In Listbox

[james_IT]

Hi all

Having a stupid little problem - The currency and decimal places are not showing up my listbox. In the table that the listbox is based on the field is set as currency but its not showing up, any ideas?

Btw, i looked at other similar questions but someones posted a db that was supposed to show the currency like i want but it didnt show up on my comp but did on theirs...

Thanks

 

[boblarson]

Hi all

Having a stupid little problem - The currency and decimal places are not showing up my listbox. In the table that the listbox is based on the field is set as currency but its not showing up, any ideas?

Btw, i looked at other similar questions but someones posted a db that was supposed to show the currency like i want but it didnt show up on my comp but did on theirs...

Thanks

You need to use a query for the list box rowsource (not the table) and set the formatting in the query:

ANewFieldName:Format([YourFieldNameHere, "Currency")

 

[LPurvis]

Bear in mind that the string (text) which you subsequently retrieve from such a column will look like a numeric currency value - but isn't one. Of course there's every liklihood that any operations performed upon it would perform the necessarily implicit type casting (i.e. VBA will recognise "£23.50" as a currency type and convert - but it wouldn't hurt to perform the conversions yourself in subsequent use (i.e. coerce the type of the listbox value into a Currentcy type variable - make sure it's accepted. If you were working with more than one currency type, say dollars and pounds, you'd have to do the stripping of the non-numeric symbols yourself.

 

[boblarson]

True, and as long as the listbox isn't being used for values it matters not. But if it is then it would need to be converted back to real numerics to be used.

 

[james_IT]

Thanks for all your replies,

Im having trouble formatting the query with

ANewFieldName:Format([YourFieldNameHere, "Currency")

It keeps saying invalid syntax or invalid dot or !. Where should I be putting this code? The format box of that field properties? The criteria box of that field? Criteria box of a new field?

 

[LPurvis]

Since it's wakey time here I'll grab this for Bob, I'd imagine he's a few hours away yet ;-)
(I know I would have been anyway - when I was out over there.. Yawn? Never felt anything like it.).

Bob was suggesting a calculated field for your query.
So if you were in the query design grid view you'd enter the column he offered (in the format)
Alias: Expression
into a new column. (And possibly remove any use of the original currency value- though you could actually maintain that as another, hidden column - for easy reference)
Were you working in the query in SQL view then you'd just enter it as part of that statement...
SELECT IDField, Format([YourFieldNameHere, "Currency") FROM TableName

As I mentioned though - you could always have
SELECT IDField, Format([YourFieldNameHere, "Currency"), YourFieldNameHere FROM TableName

And make your listbox properties fit that (by hiding column 3, but being able to refer to it still).

Cheers.

 

[james_IT]

GENIUS!

Thanks

 

[LPurvis]

Bob threw the pass. I just happened to be stood here.

Glad you're sorted.