Text 2 Currency

B

bigbob

I have a form bound to a remote SQL table. Some idiot decided to store the
unit price data as text, so that 00012455 in fact means $124.55. I cannot
change the data type in the table because it doesn't belong to me. Creating
an unbound field to load and format the data in the OnCurrent event doesn't
work in datasheet view because the whole column displays the same value. Is
there a format function to display this ridiculous string as currency ?
 
B

Bruce M. Thompson

I have a form bound to a remote SQL table. Some idiot decided to store the
unit price data as text, so that 00012455 in fact means $124.55. I cannot
change the data type in the table because it doesn't belong to me. Creating
an unbound field to load and format the data in the OnCurrent event doesn't
work in datasheet view because the whole column displays the same value. Is
there a format function to display this ridiculous string as currency ?
Click to expand...

Have you tried, while the form is in design mode, setting the textbox's "Format"
property in the property sheet to "Currency"?
 
B

Bruce M. Thompson

Just to clarify - the textbox I refer to in my prior post should be that which
is bound to the "unit price" field in the form's recordsource, not an unbound
textbox.
 
J

Joe Holzhauer

You can cconvert the string to a single, then format it as currency:

Format(CSng(strAmount)/100, "Currency")

(I included the /100 because it appears the text doesn't include a decimal
point)

HTH,
Joe
 
J

John Vinson

I have a form bound to a remote SQL table. Some idiot decided to store the
unit price data as text, so that 00012455 in fact means $124.55. I cannot
change the data type in the table because it doesn't belong to me. Creating
an unbound field to load and format the data in the OnCurrent event doesn't
work in datasheet view because the whole column displays the same value. Is
there a format function to display this ridiculous string as currency ?
Click to expand...

If you don't want to update this field, you can base your form on a
Query with a calculated field:

ThePrice: CCur([textprice]) / 100.

John W. Vinson[MVP]
 
B

bigbob

Thanks John. Good answer.

John Vinson said:
I have a form bound to a remote SQL table. Some idiot decided to store the
unit price data as text, so that 00012455 in fact means $124.55. I cannot
change the data type in the table because it doesn't belong to me. Creating
an unbound field to load and format the data in the OnCurrent event doesn't
work in datasheet view because the whole column displays the same value. Is
there a format function to display this ridiculous string as currency ?
Click to expand...

If you don't want to update this field, you can base your form on a
Query with a calculated field:

ThePrice: CCur([textprice]) / 100.

John W. Vinson[MVP]
Click to expand...
 
Top