X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Update A Field In Access Table From An Excel Spreadsheet, Access 2016    
post Mar 31 2020, 01:01 PM

Posts: 167
Joined: 2-September 15

I have a linked excel spreadsheet in my Access database.
All fields show as text primarily because there are about 8 lines of headings that are all text.

I use that linked spreadsheet to create a table with a query.

The problem I have is that one field in Excel is a number with more than 2 decimal places.
Because the linked file shows all fields as Text, when I use cdbl(rate) it turns it into a rounded number and I loose the decimals.

Is there a better way to do this? I need the data to be continually updating hence the linked table.
The excel spreadsheet is also saved as a read only, so I cannot do anything to it.
I can only do things in Access.

Excel Column heading: Rate (text)
Data 5.6667

shows like this in Access linked table:
F97 shorttext 5.70

I need it to be the 5.6667

Go to the top of the page
post Mar 31 2020, 01:37 PM

UA Moderator
Posts: 78,098
Joined: 19-June 07
From: SunnySandyEggo

Hi. Not sure I completely follow what you're trying to do. Can you post a sample setup files to show us what you mean? Thanks.

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Apr 3 2020, 03:21 PM

Posts: 4
Joined: 30-October 18

Try using the FORMAT command instead of CBL

FORMAT(rate, "General Number")
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    27th May 2020 - 01:15 AM