Strange Behavior with SQL

[ogri]

Hi

I have a form in Access which I use to set off a report. If I do this first then it works fine (ie, dont use any other buttons on forms). If I use any of the other buttons then I get the following error message:-

Run-time error '3075':
Function isn't available in expression in query expression 'left(GAINReject.CRN,9) = UrgCusts.shortCRN'

The SQL is to pull data off from one table where the first 9 digits of a column are within another table

Any ideas? Especially as it CAN work fine if used first

All the best

Keith

 

[Craig0201]

Sounds like an old and traditional Access bug. Add a reference to and code library, delete it, close Access and then restart. It should then recognise Left() as a function.

Craig

 

[ogri]

Hi

Sorry, but you have lost me with that one. What do you mean

Thanks alot

All the best

Keith

 

[Craig0201]

Go into the code window, Tools, References. Add a reference, save it the reference and then reboot.

Also try exporting all your objects into a new mdb. Has also worked for me in the past.

Craig

 

[ogri]

Hi

That seems to have fixed it (with test runs). Lets see how it goes for a full run, but I think you ave solved it

Thanks alot

All the best

Keith