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.
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.
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
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.
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.
No, the table has data. And it is working yesterday. Then today, when I try to refresh the table, that is the error.
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.
Yes, I do have NZ function as well as IsNull function.
How should I do this?
and thisyou may be better served by exporting your query results to Excel from Access instead of trying to link them.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.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.
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.
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
Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks