SQL tablefield = real, view field = integer when linked to Access (1 Viewer)

[Zakraket]

I have a table in SQL tblOrdOutput which holds an integer field. This field needs to contain decimals from now on, so I changed the field to "real" on SQL server. When I link the table directly, the field displays as numeric/single in Access.
There is a view that read info from this table directly, combining it with some other selects), like "select * from tblOrdOutput", this view is also linked to Access (to be used in some form)

So, in the Access linked table the field is now single. In the view the field remains Integer and displays decimal numbers as integer (so 1,1 becomes 1.0). The form is based on the view, so as soon as you put a decimal number in the field it's changed to integer after input.

I tried:
- refreshing the links
- reconnecting the links
- creating another linked table to the same view

Nothing makes the field in the linked view change to single in Access

The strange thing is, there is already another decimal field in the same table which is also set as real, and THIS field displays as single in both the linked table and the linked view.
The only difference between the fields is that the first allows NULL and the second doens't. Changing this also doesn't help.

Anyone?

Maybe I should add that this Access application has been converted from ADP to ACCDB last year (because O365). I converted it, but I did not develop the ADP (it's been in use as an ADP for some 13 years now, since last year as an accdb)

 

[Minty]

Try using Float instead. I've had some strange things happening with Real...
However, as a quick workaround - in your View, if you multiply the result by 1.0 it will force the datatype to a non-integer type.

 

[Zakraket]

I also tried numeric(5,2) which also didn't do the trick

Float does not seem to help either

And I tried your trick with the 1.0 but then the view is not updatable anymore which it needs to be since it's a datasource for a continuous (sub)form (or I'm forced to rebuild the form just for this)
It does show as a double now

edit: I changed to view back to just the field, now the linked view still shows "double precision" as type, and I can enter a decimal. Also when I reconnect the linked view, the field stays a double
Even making a new linked table now works

Thanks for putting me on the right track

 

[Minty]

Have you tried Casting it as Decimal(10,4) in the view???
I'm not sure that would help with the editing though...

Finally, create a query in access based on the view, and see if that helps.

 

[Isaac]

I try to stick with decimal(19,4) as it seems to work for 'everything'. I have never used float or real and wouldn't recommend them unless you know you need them for some rare and specific purpose

SQL: Newbie Mistake #1: Using float instead of decimal - The Bit Bucket (greglow.com)

 

[Zakraket]

I normally use decimal(18,3), but this program is not made by me. I'm supporting it.

The problem is fixed by doing the field * 1,0 trick in the view, then refresh the link. After that, the view can be changed back to normal

 

[Isaac]

I normally use decimal(18,3), but this program is not made by me. I'm supporting it.

The problem is fixed by doing the field * 1,0 trick in the view, then refresh the link. After that, the view can be changed back to normal

Ok, understood. We have to do that sometimes and not our choice - me too.

Thanks for posting the solution #1 for Minty suggestion

 

[Minty]

I normally use decimal(18,3), but this program is not made by me. I'm supporting it.

The problem is fixed by doing the field * 1,0 trick in the view, then refresh the link. After that, the view can be changed back to normal

Be careful if you ever relink the view it may revert to thinking it is an integer field again, just a heads up.