ADO2.6 currency formatting

dgulliver · October 26th, 2006, 06:29 AM

Hi,

I have a fabricated recordset and I'm having problem formatting the correct figures. I have defined my fields like

Code:

.Append "Debit Credit", adChar, 6
.Append "Currency", adChar, 3
.Append "Amount", adCurrency

Populating

Code:

rsAgentPostings.AddNew
rsAgentPostings("Debit Credit") = "Credit"           
rsAgentPostings("Currency") = "GBP"           
rsAgentPostings("Amount") = Format("1507.5000000", "###,###,##0.00")
rsAgentPostings.update

Even though I have specified the formatting when I check the value in rsAgentPostings("Amount") its 1507.5 instead of 1,507.50.

I changed the datatype to adChar and the length to 50 and it formats correctly, but in the grid we use (UltraGrid) its left justified when i force it to right justify the value disapears - bah. So I have changed the datatype back too adCurrency.

So my question is why isnt the field formating correctly? ie 1507.5 instead of 1,507.50

Thanks in advance
Darren

BrianWren · October 31st, 2006, 07:09 PM

The machinery that stores and retrieves the valuesâthe database engineâstores the real value. Just as there are no zeros to the left of 1507.5 (eg, 0001507.5), there are none to the right. Just as 1Ã10^1 + 0Ã10^1 + 5Ã10^2 + 1Ã10^3 is the same as 1Ã10^1 + 0Ã10^1 + 5Ã10^2 + 1Ã10^3 + 0Ã10^4, 5Ã10^-1 + 0Ã10^-2 = 5Ã10^-1. There is no meaning to storing the zeros.

The rest is just display instructions. (Not much help, I know, but that's the reason.)

woodyz · October 31st, 2006, 07:20 PM

If you want to store a formatted amount, you will need to have a datatype that stores a string. As Brian is implying, a column with a data type of adCurrency is storing the amount only, not the formatting.

Woody Z http://www.learntoprogramnow.com