whats happenning to my query? (97 sr2)

[fatherjack]

Hi, I have a database that i have been developing / using for sometime now. I have just rebuilt my PC and when i try out a query i get the error message
"Function isnt available in expressions in query expression"

the part of the query that causes problems is
mydata=left([fieldname],5)

WHY ?

I have tried re-installing, service packing, re-service packing. Other databases are fine, ie the LEFT expression works as expected.

[Shane Sargent]

Check the references to see if any are missing in that db.

[Caz]

I think this is one of the things that happens when one of the references is not checked. Module / Tools / References.

Unfortunately it slips my mind as to which one it is. I'd try either Microsoft Access or Microsoft DAO, but it may be another.

[fatherjack]

how do i check the references of a query?

[Shane Sargent]

Caz hit it below. Type Alt-F11 to bring up the VBA IDE, then head to Tools -> References, and see if any are flagged as missing. Lemme know how it turns out.

[fatherjack]

OK I feel stupid now, the modules reference ADO 2.5 and Access 8. What next ?

also, why do the module references affect the query ?
surely it is possible to have a db with no modules, how would you get the query to work then ?

[Shane Sargent]

The references are for the database as a whole, not for a particular collection of objects like queries, modules, tables, etc. I'm betting that if you find the reference to Visual Basic for Applications, you'll find your problem. The Left() function is a VBA function; no pointer to the VBA library means that Access has no idea what Left() is whether it's being used in a query, module, or what have you.

Post back and let us know how it's working out. And dont' worry, this exact same thing happens to everyone and the the first time it does, you wrack your brain and end up looking, well, like your pic! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

[fatherjack]

ok guys, thanks for the help. i ended up removing all references and then re referencing them. i think it was the different version of ado(?) 2.1 v 2.5 anyway problem solved

thank you <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>