Compile Error

[Norie]

Hi

Every time I try to use functions like Len, IsNull etc in an expression in a query I get Compile Error.

Access lists the functions in the Expression Builder and capitalizes them when I key them in.

Anybody got any ideas?

 

[Cbrine]

Norie,
You most likely already checked this, but do you have any missing references? I know I had some major problems with string formula's when I had a missing reference in the past. I'm not sure why?? Doesn't make a whole lot of sense, but removing the missing reference fixed it.

Cal

PS-I've used both formula's in expression in Access 97, without any issues, so they do work. I haven't tried them on Access 2003, which I am now on, but they should still work.

 

[SydneyGeek]

Adding to Cal's comments, if you have just upgraded an existing database to a higher Access version the references will need to be fixed: Tools | References in any module. You'll see <Missing> for one or more libraries. Remove the ticks, select newer versions from the list and you should be OK.
It's a known Access bug...

Denis

 

[Norie]

Denis

Tried your suggestion but no luck.

I also tried 'Repairing' MS Office and I'm still getting the Compile Error.

It's not a big deal as it is occuring with 2000 which I only use to help people on this forum when I am at home whereas I use 97 to help when I'm at work and there doesnt appear to be any problems there.

Still I can't quite understand why it is happening.

 

[SydneyGeek]

Norie, are these databases that you created in Access 2000, or have you sent them home and opened them there?
If they were created in Access 2000 then I'm stumped. I have only had this issue with upgraded databases.
BTW, the original post had <Missing> in single chevrons but they were interpreted as a tag and removed from the post...

Denis

 

[Norie]

Denis, Cal

I created a brand new database and imported the query causing the problem and the table it was based on.

Guess what - no error.

I did have some code in the original database - perhaps that was causing the problem.

Doesn't matter now anyway as I've deleted the original - I was only using for testing.