How to Stop Showing Decimal as String 1

[sabloomer]

Can anyone help me with what I think should be an easy question. I have a table that has two fields that are set to as Decimal with Length 17 and Scale 3. This data was imported from another SQL server. I have a view set up to sum these two fields. When I run the view it returns the desired results, but it formats them as text instead of a value. This is a big problem when I try to format the results on my report. Any suggestions?

Thanks,

sabloomer

PS. I tried converting the field to Numeric, but the same result

 

[cmmrfrds]

Is this an Access Project or an MDB with linked tables?

 

[sabloomer]

Access Project,

Thanks cmmrfrds

 

[cmmrfrds]

I believe what may be happening is that the precision on the sql server side is larger than can be represented on the access side. In those cases the mapping is to a string instead of a number. Do some research on mapping data types in OLEDB (used by access project) and sql server.

 

[sabloomer]

Thanks, I will look into that and let you know.

sabloomer

 

[sabloomer]

I looked at the OLEDB "Data Link Properties" and couldn't find anything that looked like it would help. This is going to drive me to drink! It doesn't seem like it should be that complicated to add a column of numbers in SQL Server. I modified the two fields in question to be "Numeric" with a Precision of 10 and a Scale of 3. That should give me numbers up to 1,000,000 with 3 decimal points. That doesn't seem that outrageous. I noticed that when I run the view from within SQL Server at it aligns my numbers to the left, which makes me think that it has been converted to a string.

I then tried to work around it my changing the Control Source of the field in my report to include the function val() to convert it back to a number, I get an error that the Control Source is Invalid!

Any help you can provide would be wonderful!

sabloomer

 

[cmmrfrds]

When you return the field to the report.

If you did a display of the field. Lets say in the OnCurrent Event and tab through some records.

Put in Debug.Print "your field = "; me.yourfield

Then do Control G to look in the immediate window. Does it display incorrect.