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.