Access can't find field, so it doesn't convert


Legacy 386498

Well, this is interesting...
First I checked all the macros as that seemed easiest. When I tried to view queries in design view it wouldn't let me, not that I would have wanted to search every one for a single word. More on that in a moment. Then I tried to output the recordsource of the queries to the documenter and it wouldn't let me because the version was too old.

So I wrote a sub in another db to connect to your db and delve into the sql of each of the queries and look for "description " and it found none. Then I modified the search to find "description" and it found lots. I copied and pasted all into a Word document and did a search for "description" and it stopped at every "description_perte" except there was one that looked like
VBA Code:
So why didn't Word or my code find that instance of "description"? Because it is not - it is "descriptionGROUPBY" with a line wrap in between. I'm surprised this query runs at all because I do not think that Access 2016 would let that slip by, but perhaps that is only true for vba code and not Access sql. If I could alter the design and save it, I would open that query in sql view, insert a space before GROUP BY and test it but I am not allowed to edit the design. You will have to try that.
I wasn't able to convert the database after inserting a space before GROUP BY.

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.


Well-known Member
Jun 3, 2015
I can't open either of those 2 queries as they are reported as being corrupt, however, if they have anything to do with a report named e_tbord_test then it/they may not be.
Make a copy of your db.
go to start>run and enter msaccess /decompile
access should open; right click on this new copy, hold shift key and select open from the shortcut menu you just opened
db should open - do nothing except close access completely
reopen same db (EDIT -SHIFT CLICK AGAIN. DO NOT LET ANY CODE RUN), double click on a module and in the vb editor, click 'compile' and you'll start getting errors. First one is likely
Member already exists in an object module from which this object module derives click OK and the offending code should be highlighted (Dim total53) and the report I mentioned above should be highlighted in the objects pane. If you do ctrl+F (find) and look for the highlighted code you'll find that it's used in several places, some of them incorrectly. Mainly though, the error likely means that there is also an object in the report by the same name. You cannot have an object on a form/report that has the same name as a variable you declare.

There is no gentle way to tell you that your db is a complete mess and you should probably start over. Whoever rebuilds it must not do the same thing as what you have here. There are FAR too many objects that I can only surmise are almost the same because their names are very similar. Especially tables! As many as 6 tables for one thing, having the same fields but the table name has a different number at the end.

I'm afraid I can spend no more time on this, but I wish you luck in solving your issues.

Watch MrExcel Video

Forum statistics

Latest member
Florence Thomas

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...