Format Currency and SQL

[johnryals]

I've created a view in SQL2005 which has numbers.
I've created a link to the view in Access2000.
I've created a form in Access with the value from the view.
I've changed the format of the value to currency, however it continues to show the value non-formatted.

Why?

[nico5038]

Where did you change the format ?
How are your Regional Settings for currency set ?

The format needs to be set in the (bound) field control on the properties page "Format".
Sometimes the Start/Settings/ControlPanel/Regional Settings give trouble when not having the same numeric separator for decimals for Currency and Numbers. Check that they are equal !

Nic;o)

[johnryals]

It set correctly. The odd thing was when I used a table in access, it worked fine, but when I switched to a line to a SQL view, it stopped formating.

[johnryals]

I figured it out. You have to go into the Control Source and use =Format([thevariable],"$###,###,###")

[nico5038]

Strange, the form control's format should "rule".

Can you try to change the view field into a currency field ?

Nic;o)

[nico5038]

Good find, but still odd that the control's property doesn't work...

Nic;o)

[johnryals]

It worked once. I tried another field and I got #error. It must not reconize the variable as number.

Do you know what the view command for currency is?

[nico5038]

From: http://www.downcastsystems.com/devce...p?ArticleID=51

SELECT '$' + convert(varchar,cast(Price as money),1) as Price FROM YourTable

Nic;o)

[johnryals]

Thank you.
It works now. It carried through to the access form.
Option 0 gave me $123456.00
I used the option -1 to give me $123,456.00
Do you know what the option for $123,456 is?

[nico5038]

When you don't have to update the field you could use for that an access query based on the view with:

select format([yourfield],"$##,###,###") as FormattedPrice ....

Otherwise you should do this formatting in MS SQL.

Nic;o)