Every Techie needs to read something.

Thursday, August 03, 2006

Microsoft Access error: Compile error . in query expression

Sometimes you just can’t win. I went through writing this super duper module in Visual Basic, which does all sorts of fancy stuff. I made sure that it can handle any error, and keep right on going. I am worried that if Microsoft sees this module, they will try to buy me, and copyright my brain. Ok, it isn’t quite that good, but you get my point.

Now when I open any of my queries, I receive the error:
“Compile error . in query expression ‘val(nz([proj_name],0))’”

It seems that the error is with any of the built in functions. If I remove the val statement, I receive it for the nz statement.

Of course, being a modern information worker, I used half of Google’s bandwidth to search for an answer, and I found some really great ones, just none that helped me. Generally, this type of error deals with a missing reference in the visual basic editor. Allen Browne has a terrific page about Solving Problems with Library References (thanks for that page Allen).

He also points to another incredible resource, Doug Steele’s Access Reference Problems

In my case, none of these steps worked for me. I did manage to narrow my problem down to one single statement. It seems that when I use the call statement to run a sub procedure, I get that error. The really neat thing is that I don’t even have to touch the module, all I have to do is have that call statement in there, and magically, none of my queries work.

I even tried converting the sub into a function, and I still get the errors if I have any code in my module that invokes the function.
I have made a post to the very helpful people that read the Microsoft Newsgroup Server. Unfortunately, I posted to Microsoft.public.access.modulescoding, and it doesn’t look like Google carries it.


Update: My post on the newsgroup was answered by Allen Browne. What a helpful guy. He had me choose compile in my vb module, and fix all of the errors, after that, it worked like a charm.

20 comments:

Anonymous said...

Thanks! The solution that you needed was my solution as well. The only error was in a module that had only the error in it??? All of it was botched...

Mike Scott said...

Great! I'm glad that it helped.

Thanks for the comment.

Anonymous said...

Very nice article. This error may come due to database corruption. So you can go for jet compact utility or access repair utility to fix this error.

Anonymous said...

thank you SO MUCH!!! had the same problem before and ended up having to revert to a backup copy of my database..

this time i found your site and fixed it in about five seconds. you are my hero.

-Steph

Mike Scott said...

No problem Steph, I'm glad I could help.

Southern said...

THANK YOU!

I was loosing my mind with this problem and now everything works fine!

Thanks again

Mike Scott said...

No problem, I was in the same place when I was looking for it. I'm just glad it helped.

Anonymous said...

I had a mdb that had functions in queries and compile errors in it for a couple of months. Everything was fine until recently when I received this error. Gotta love MS Access! Thanks for the solution

Mike Scott said...

You're welcome! Glad it helped.

Anonymous said...

I undid a lot of my AM work trying to figure out this issue, and thanks to google, it led me to this location for finding a solution! Like you, I had to 'compile' in vba to finally determine the culprit. Much thanks!

Felix Filicibus said...

The solutions presented here worked for me indeed. But it would be worth a dime to know what causes the broken references w/o the MISSING label

Mike Scott said...

I'm glad it helped you out Felix! I agree that it would be nice to know why it happened. If you ever figure that out, let me know and I'll update the post.

Anonymous said...

my replace() function used in a query failed after I added a file rename module. removed module and poof, replace() function works again. now trying to figure out how to make both work in the same database.

Anonymous said...

Thank You So much, It helped me a lot, very nice article

Anonymous said...

Thank you so much! Struggling for hours with an old database someone left some bad code in years ago. Thank you for posting

Anonymous said...

Mike, you are a genius and Microsoft really should invest in you and your brain. Thanks

Mike Scott said...

Thanks, but I can't take credit for the fix, I only found it somewhere else. I'm still amazed that this is still helping people. It was an old solution when I found it, now it's another 6 years old:-)

Best of Luck!

Anonymous said...

This fix worked for me to. Thanks

Anonymous said...

Finally, an answer that actually helps me.

In my case I also had to update some external calls to PtrSafe for the 32/64 thing. Nothing like debugging two problems at once (although these two were pretty clearly delineated).

You saved me giving up and making a whole new app in C#. Thank you. Even if you didn't figure out the fix, your post here came up in my searching before anything remotely useful.

Mike Scott said...

It blows my mind that this post is still helping people 14 years later :-)
Glad it helped you out!