Strange Listbox/Currency Format Bug

[Montez659]

Ahhh, it's good to be back from vacation! Now onto things that matter!

I have a listbox I am trying to enter currency values that are eventually dumped into a Junction table. I discovered the oddest thing today, as prior to today I have never had to enter a value greater than $999.99. But today I had to add a value of $2166.10, and when I did, the listbox displayed $2. I tried $3000, and the listbox displayed $3. $1000 displayed $1, but anything $999.99 and less displayed correctly.

To make sure I wasn't losing my mind, I set up a test form and recreated entering the value and the same thing would happen. What in the world would cause this?

Here is the snippet of code that add the items to the list box:

Me.lstBudgetItems.AddItem Me.cmbBudgSub.Value & ";" & a & ";" & FormatCurrency(Me.txtBudgAmount.Value)

a=just some text. If I were to remove the "Format Currency" part of the equation, the value displays correctly (i.e., one would see 2166.10) but alas, not as currency. Is this a bug or is this a setting or something that is quirky with Format Currency?

Thanks in advance!

 

[xenou]

Offhand, I'd wonder what exactly txtBudgAmount is? Is that a bound field? Is it for sure a number data type - currency, double?

According to this link, format currency gets a lot of parameters and follows regional settings (whatever they are):
http://office.microsoft.com/en-us/access-help/formatcurrency-function-HA001228840.aspx
So maybe something to do with your regional settings? Though I'd still look more closely at the datatype you are passing to the function, I guess, first.

You could also try using the format function instead. I actually have never used the FormatCurrency function myself - apparently it would be handy if your app was used in different regional settings though.

 

[Montez659]

You know, I used to use the format function back in Excel 2003 and it seemed to have lost the library when I switched to Office 2007 so in Excel I had to switch anything that was formatted for currency to FormatCurrency. I never did try it in Access though, so I will give that a whirl.

As far as the parameters, I took a look at those but didn't see anything that should cause it to act like it does.

txtBudAmount is a textbox with the format property set to currency. I was wondering if the comma that occurs when the value is above $1000 was throwing it off, but it shouldn't because I am reading the value of the textbox.

I don't know, I guess my best bet is to try the Format function and see if that works.

 

[Montez659]

Well, from what I see, the Format Function doesn't work either. If I use "Currency" or "$#,##0.00", they both display the first whole number if over $1000.

I checked into the regional settings further, but it seems that those pretty much dictate the currency symbol, decimal symbol and spaces, and negative amounts. Nothing that would dictate the actual value.

 

[Montez659]

Stupid Access! Lol! It was the dang comma. I noticed it when I added an extra column to my test listbox and noticed that for $1000, the $1 was in one column and the 000.00 was in the extra column.

How dumb is that - that MS can't recognize the value of the textbox but is looking at the format as well?

I don't know how many other people will ever come across this problem but beware.

Edit: And it only seems to have this problem in a listbox that has a value list. I noticed that other listboxes that I have that are bound to a query format perfect fine with numbers in the thousands and with a comma.

 

[Montez659]

Well, now that I think about it, I guess Access isn't that dumb. It makes some sense that if the FormatCurrency option was being applied to the value BEFORE actually inserting into the listbox, the comma would have an effect since that is what a value list uses as a delimiter. So I just removed the FormatCurrency and when with the regular Format function and formatted the value without the comma:

 Me.lstBudgetItems.AddItem Me.cmbBudgSub.Value & ";" & a & ";" & Format(Me.txtBudgAmount.Value, "$###0.00")

 

[xenou]

Yeah, now that you mention it that makes sense!