Numeric SQL server field currency in Access 1

[cdck]

I have a table in a SQL Server 2000 database which is linked in an Access 2003 front end. The table has 3 fields, "Overhead" (numeric), "DateEffect" - (DateTime), and "ID" - (numeric, identity, key). I'm having an odd problem I've never experienced before.

When I open the table in SQL Server, the value in "Overhead" is 2.25, and shows correctly as numeric. When I open the same table through Access 2003, the value shows as $2.25, and the format in design view is listed as currency.

I have updated the linked tables twice, and even changed the field format to decimal on the server and updated the linked tables again, but it still comes up this way. What could be causing this error?

Cheryl dc Kern

 

[PHV]

So, you have a Currency field in your linked table: what is the problem ?
Use a query with the CDec function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cdck]

That's certainly one way around it, which I'll use if I must!

However, this table will be used in multiple Access front-ends for multiple purposes, many of which are not even imagined yet - I'm afraid that if I rely on a work-around rather then understanding the cause, I or other developers will have trouble with this down the road.

It would also be better to fix the root issue rather than having to use the CDec function on every query, form, and report that will use or edit this data.

Does anyone have any idea what can cause Access to read the format of a numeric field incorrectly as currency?

Cheryl dc Kern

 

[PHV]

I'd try to link a view instead of a table ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cdck]

I have the same problem with the view; Access sees the number as currency. I tried using the CDec function in the view, but SQL server doesn't recognize it.

Cheryl dc Kern

 

[cdck]

I just went into another table that also had numeric fields with decimal information, and was showing correctly in other front-ends. I saw that the precision was set to 15, with a scale of 5. I changed the settings on this field in this table, and now Access treats the data correctly.

I'm not sure I understand this effect, but I'm glad that I stumbled on it.

Thanks, PHV, for your suggestions. CDec will come in handy sometime, I'm sure, and I didn't realize that you could link views as well as tables, so I still learned from you!

Cheryl dc Kern

 

[JoeAtWork]

Access was just applying a formatting, which you can override in your individual forms and reports. It doesn't really affect the underlying number in SQL Server.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[cdck]

I've learned from searching online that the precision and scale of a number field are sometimes used to determine a format for the number; the precision and scale that I had used originally apparently happened to match the style associated with currency.

For anyone else who never really knew what these properties mean, precision is the total number of digits the field will hold, and scale is the total number of digits which can appear to the right of the decimal point.

Joe:

Thanks for that bit of explanation. But since I'm already storing a percentage as a decimal number/multiplier and formatting it back and forth depending on the application, I was desperate to avoid adding another level of confusion each time I came at the data with a new application.

Thanks again, everyone!



Cheryl dc Kern