How does Access determine linked table field formatting?

[Tom Carp]

Have an Access db used to combined a bunch of individual accounting journal Excel spreadsheets by creating a linked table to each. Those Excel files are structured and formatted the same. They all have fields like account, category, debit, credit, etc. that are formatted the same.



All the journal data is combined via SELECT/UNION queries. I noticed that the credit field in the final combined query is being formatted at text, while the debit field is formatted as numeric. I went through every linked table and found two that had the credit field formatted as text. I was able to work around the formatting issue by adding a credit entry in the credit field in the Excel files that weren't formatting as expected.

What's mysterious is that every Excel file is formatted exactly the same. In fact, in the two files with the credit field ending up as text, all the data in that field in Excel is formatted numeric, including the very first credit field in the first record in each.

I've assumed that how Access formats fields in linked tables comes from the Excel files. What's confusing is why two of the seventy journals that seem to have the right field formatting are turning out formatted as text when linked in Access.

Any ideas?

[Micron]

A guru once declared that Access determines the data type based on x number of Excel rows. IIRC that number is somewhere around 10, so your data or it's format in any number of rows within that range could be the difference.
If this is an issue, better to link the sheets and use append/edit queries on native Access tables that are basically copies of the sheets. Once created and formatted, the table data types will always be correct when retrieved from the tables. All that notwithstanding that sheets often make for poorly designed tables. To go further, I'd try to make the tables properly normalized rather than just reproduce sheet structures. If that isn't clear, best to research db normalization to see why sheets usually make lousy tables.

[CJ_London]

the rule is the first 8 records. The datatype (text/number/date/etc) is determined based on the majority datatype.

In the event of a draw, text 'wins'.
If there are values in the first 8 rows then nulls are ignored for the purposes of determining majority.
However if all 8 values for that column are all null, then the datatype is determined as text - which may be the reason for your problem

If your table is linked, look at the connection string in msysobjects. You will see a value of 2 is assigned to IMEX. This sets the 'majority rule'.

The other settings for IMEX are 0 and 1. If you could set it to 1, the rule changes to

if any value in the first 8 rows is text, ignore majority, the datatype is set to text
nulls are ignored but if all nulls then set as text

IMEX=0 also uses the majority rule. This enables you to edit excel to a certain extent.

[ArviLaanemets]

Additionally, having your data in Excel table formatted as text doesn't guarantee that they really are text there. In case the data earlier were text, and then the format was changed to numeric, the data is displayed like numbers (i.e. in un-aligned cell entries are displayed right-aligned instead of left-aligned), but they really remain text values. To convert data really, you have to edit every cell - e.g. using Paste Special to multiply the range of data with 1.

[Tom Carp]

Thank you all very much.

I have some homework to do, particularly on the alternative table design approach. Some of the terminology is new to me. Just fyi, I ended up using the Access linked table approach mainly to have something different than using Excel to merge data from 70 tables. Although there is some very minor selection criteria used in a couple queries, for the most part, data ends up in a query that contains all the records, or in a couple queries that have some scope limits (e.g. data type, time period). Those queries are connected to Excel files that do the final work.

As far as how Access is determining the field type, I was able to work around by putting a numeric entry in the credit field in the first record in the two spreadsheets that were causing the issue. Does the way Access determines format get set by the first record containing data? In other words, is Access looking for data vs. "looking" at Excel field formatting?

[Micron]

Does the way Access determines format get set by the first record containing data?

Answered in posts 2 and 3?

[CJ_London]

vs. "looking" at Excel field formatting?

format is just that - how the data appears to the eye. Access will import the actual data, so format is ignored

[Tom Carp]

Sorry, didn't phrase the question well, but I think you answered it.

Of the 70 linked tables, 2 have the Credit Amount field formatted non-numeric that should contain numeric data. My question was whether the Excel field formatting mattered to Access. Sounds like it doesn't; the data is what Access uses to decide on field formatting. Since it appears Access will look at 10 records or so to decide on field formatting, I was curious how Access would format a field with no data in the first 10 records, and was formatted numeric in Excel.

I was able to eliminate the formatting issue by entering "0.00" in the Credit Amount field in the first records of the Excel tables. What I have yet to figure out is why those two Excel files, when there are quite a few Excel files that don't have Credit Amount data in the first 10 records.

After a little more curiosity testing, I'll probably follow the suggestion to Paste Special x 1, although the data displays in Excel as you'd expect numeric data to display (aligned right), and the formatting for all the cells appears set correct.

[CJ_London]

although the data displays in Excel as you'd expect numeric data to display (aligned right), and the formatting for all the cells appears set correct.

Don't know how many times you have to be told, formatting is ignored. Importing from excel has it's issues because excel (and only excel) can treat text that looks like a number as a number because any cell can be any datatype. With databases, each column has a specified datatype.

Since it appears Access will look at 10 records or so

No - 8 records

I was curious how Access would format a field with no data in the first 10 records, and was formatted numeric in Excel.

Excel does not have the concept of null - instead it has empty - and the rule is as stated in post #3 - However if all 8 values for that column are all null, then the datatype is determined as text and again - format is irrelevant

[Tom Carp]

Just to close out the thread...

Isolating the issue to 2 of the 70 Excel spreadsheets helped. Turns out, those 2 were originally 1 that had gotten large enough to be split; which was done by creating a copy of the spreadsheet. Your suggestion about Paste Special x 1 would have worked, but I went back to the journal template, created 2 new blank journals, and then Copy/Paste all the fields except the one formatting as text. That one was Paste Values. Worked; the 2 now have the Credit field formatted numeric.

Thanks for your post.