+ Reply to Thread
Results 1 to 8 of 8

How to get #Error easily in access database

  1. #1
    Registered User
    Join Date
    08-18-2018
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    45

    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
    MS-Off Ver
    MS Office 2019
    Posts
    15,665

    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.

  3. #3
    Registered User
    Join Date
    08-18-2018
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    45

    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
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,365

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    08-18-2018
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    45

    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
    MS-Off Ver
    MS Office 2019
    Posts
    15,665

    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
    Registered User
    Join Date
    08-18-2018
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    45

    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
    MS-Off Ver
    MS Office 2019
    Posts
    15,665

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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