We help IT Professionals succeed at work.

Pass Through Query to SQL Server displays currency when it isn't currency

snyperj
snyperj asked
on
Using a pass through query in access I am pulling data from a SQL Server view.  

On the access side, it is converting several fields to currency automatically when it displays the data.  It also formats it to two decimal places.

The view in SQL shows all values to four decimal places which is how I would like it to display in Access.

So in other words, a value in the view is 0.8169 but Access displays it as $0.82

Is there anything I can do to have the Access display retain the sql view format?   Thanks.
Comment
Watch Question

Change the Decimal places from Auto to 4.

Hope this helps,
Daniel

Author

Commented:
Change it where?

Commented:
danishani means in the "edit" of the table, for each field changing the decimals property to 4 instead of "auto" that sets the decimals automatically depending on the datatype it believes it is
so you can also change the property of the datatype to say it is decimal, float, single whatever you might need
what i don't have right now is access installed here so I cannot test if that works ok on a passthrough table from ms sql, but I think it does, just open the table in design mode and change the properties
Yup but then I realized you are u using SQL server, try to change the Format of the Field in your Form or Query and set Decimal places to 4.

Would that help?

Author

Commented:
I can't edit the table in SQL server and a pass through query in access has no interface from which to edit format that I am aware of (thus the question.)

I guess I was looking for some help with applying some formatting to the view on the SQL side that may force Access to interpret the values differently than it is, which as currency.  Currency they are not.  

I know I can probably create a new query in access with the pass through as the underlying record source and apply formatting from there.... I was just thinking that there was a better way.
Thats what I suggested, either in SQL view, select the Field and then Property Sheet, Format, Currency, Decimal places 4.
Or use the Format() function, see below thread:

http://office.microsoft.com/en-us/access-help/format-property-number-and-currency-data-types-HA001232740.aspx