Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35

    Thanks

    I'll close this out this way. I work quite often in Excel, but have some Access skills.

    What I've been trying to understand are the differences between the two when it comes to number fields.

    In Excel, empty cells do not make a field text. In Access it does, even if the tables are all linked Excel files.

    The post was to understand what Access was doing in detail.

    Sorry if this isn't worth clarifying.

  2. #17
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    In Excel, empty cells do not make a field text.
    as previously stated in post#3, excel cells are all variants. Access, if not all databases have typed fields

  3. #18
    Join Date
    Apr 2017
    Posts
    1,673
    There is another Excel quirk you have to account for. When you have entered a numeric value into cells formatted as General or Date or Numeric/formatted as text, and after that you change the formatting for those cells, Excel behaves like the formatting was done. But really all entries are in old format. To make the format really apply to cells, you have to update every cell. To do this:
    a) Activate cell for editing double-clicking on it or activating the cell and pressing F2. And then confirm the editing pressing Enter. Repeat the process for every formatted cell;
    or
    b) When format was changed from Text to General or Date or Numeric, enter e.g. number 1 into some free cell out of formatted datarange, copy the cell, select the formatted datarange, and use PasteSpecial - Multiply on datarange. When done, clear the cell with entered number 1;
    or
    c) When format was changed from General or Date or Numeric to Text, enter into some free column the formula like = "" & A2, and copy it down. Then select the range with formula, copy it, and use PasteSpecial - Values to overwrite original data. When done, delete column with formulas.

  4. #19
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35
    Thanks. Generally, I don't have formatting issues in Excel. It is in Excel where I noticed some cells were being warned as numbers stored as text, but that's easy to "fix". I do appreciate the techniques; spend a great deal of time in Excel, and am always interested in new techniques to manage the data.

    The learning I've been looking for is in Access, specifically, what the app does that causes a numeric field in a table to end up as text in a query. I discover that while working with an Excel file that links to that query.

    The Access tables are linked Excel files, and the query simply combines them and adds some fields with lookup values. I don't know for sure whether Access "types" the fields as numeric or not (Access doesn't allow changing the formatting for linked Excel files, but that may not mean Access stores them as numeric). Regardless, somewhere between the linked Excel files and the query, Access has formatted a numeric field as text, and it's that "decision" I'm looking to understand.

    These are two test tables, with the fields specified as numeric in the linked Excel files. For Table 1, Access formats Num 1 as numeric and Num 2 as text. For Table 2, Access formats Num 1 and Num 2 as numeric

    What's the rule that Access is following? It's as if the "rule" is: a field is numeric if it has a number in the first few records. Is that it?

    Click image for larger version. 

Name:	2020-09-08_6-38-22.png 
Views:	13 
Size:	15.4 KB 
ID:	42939

  5. #20
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It's as if the "rule" is: a field is numeric if it has a number in the first few records. Is that it?
    no, you had it right before.

    The first few rows is the first 8 rows.

    if they are all blank, it will be treated as text
    if any of the cells has text, it will be treated as text
    if one or more of the cells has a number and the rest are blank, it will be treated as a number.


    and to be clear, it is not just access, it is any app that relies on data typing (typically databases)- which is why many of them require a .csv or .txt files as a document for import.

    you can change the 8 rows to something else by messing with the registry, google to find out more but there is a discussion here

    https://social.msdn.microsoft.com/Fo...cel-ace-driver

    I'm going to drop out of this thread now, your question has been answered numerous times and suggestions made as to how to over come them in a flexible manner.

  6. #21
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @ajax
    Just before you go, thanks for that link which has clarified the TypeGuessRows value for me.

    I'm also dropping out for exactly the same reasons.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-18-2018, 06:56 AM
  2. Replies: 11
    Last Post: 09-07-2017, 09:20 AM
  3. Replies: 3
    Last Post: 04-07-2016, 04:36 AM
  4. Excel files on sharepoint linked to access
    By Bertrand82 in forum Access
    Replies: 3
    Last Post: 01-31-2013, 11:57 PM
  5. Replies: 10
    Last Post: 12-28-2012, 02:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums