+ Reply to Thread
Results 1 to 9 of 9

How to get #Error easily in access database

  1. #1
    Forum Contributor
    Join Date
    08-18-2018
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    141

    How to get #Error easily in access database

    Is there anything I can do to get the #Error easily in access database? I got too much data and I can't find where the error is.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to get #Error easily in access database

    Where do you believe your error is located? In a Table? In a Query? Perhaps if you give us more information. I suspect that if you believe you have an error in your table entries, that you will need to run some VBA to isolate it. If in a table, do you know which field it is located in? Really need more information to understand your situation.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    08-18-2018
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    141

    Re: How to get #Error easily in access database

    Yes, that's the problem. I don't know where the error should be if it is in the Table or in a Query. However, when I tried to linked it in excel, it says that "This data source contains no visible tables". When I tried to open my query in the database, no error pop in so I suspect that my error is in my query? But I don't have an idea where the error should be, is it in my relationship, in my calculated field, etc.

    Before I have an error that pops in, "Enter value in Fieldname", so I suspect that this error is in my table.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: How to get #Error easily in access database

    Are you trying to query an empty table?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Contributor
    Join Date
    08-18-2018
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    141

    Re: How to get #Error easily in access database

    No, the table has data. And it is working yesterday. Then today, when I try to refresh the table, that is the error.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to get #Error easily in access database

    Do you have an NZ function in your query. I have found in the past if there are Access function in a query that are not in Excel, the queries do not appear when trying to link to Excel. If this is the case, you may be better served by exporting your query results to Excel from Access instead of trying to link them. Have you tried to load the query into Power Query. If you can do that, then you can link the Power Query to excel. Bit of workaround, but it keeps functionality.

  7. #7
    Forum Contributor
    Join Date
    08-18-2018
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    141

    Re: How to get #Error easily in access database

    Yes, I do have NZ function as well as IsNull function.

    How should I do this?
    you may be better served by exporting your query results to Excel from Access instead of trying to link them.
    and this
    Have you tried to load the query into Power Query. If you can do that, then you can link the Power Query to excel. Bit of workaround, but it keeps functionality.
    I haven't done this before but I think I can try it if i can't remove the NZ function. Or I need to change my code and do not use NZ function.

    Maybe it is the reason why I have an error, because when I try to run or view in datasheet in access, the query is working and seems no error at all. But when I try to link it in excel, "This data source contains no visible tables" occur.
    Last edited by dummy777; 08-12-2019 at 06:51 AM.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to get #Error easily in access database

    1. If you create your query in Access and it is open, then click in the ribbon on External Data. Click on Export and select Excel.
    2. If you are looking at the PQ option, then click on the links in my signature to learn about Power Query.

    The NZ function is not recognized by Excel and therefore the Query in Access is not able to run and be linked.

    If you want to automate the export using VBA, then look at this link

    http://accessblog.net/2006/07/export...cel-range.html

  9. #9
    Forum Contributor
    Join Date
    08-18-2018
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    141

    Re: How to get #Error easily in access database

    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Getting database error attempting to access User CP
    By 6StringJazzer in forum Suggestions for Improvement
    Replies: 17
    Last Post: 06-21-2018, 10:20 AM
  2. [SOLVED] Formula error in my database of access
    By mukeshbaviskar in forum Access Tables & Databases
    Replies: 14
    Last Post: 07-10-2013, 01:27 AM
  3. Replies: 2
    Last Post: 07-05-2013, 09:37 AM
  4. Automation error when opening Access database
    By GWB in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2010, 05:04 PM
  5. Error while populating a combobox from Access database
    By shivboy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-29-2006, 06:10 AM
  6. Replies: 2
    Last Post: 03-08-2006, 04:45 PM
  7. [SOLVED] sorting error:Access database
    By JT in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2005, 10:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1