Functions in Queries Cause syntax errors: References?

[zoroaster]

I'm running Access 2007, and have been without issue for a couple of years. Over the last few days I've been experiencing a problem with writing queries which seems to be universal to any Access database I'm using - any query I write with a Function in the QBE grid causes an error: "The expression you entered contains invalid syntax" - I've tried checking the references via opening a VBA module and looking in Tools/References and the references I've got appear to be OK (Visual Basic for Applications; MS Office 12.0 Object Library; and Microsoft DAO 3.6 Object Library.
Any ideas?

Regards, Z

The road to hell is temporarily closed due to resurfacing with targets achieved

 

[dhookom]

Have you attempted to compile your code?
Doug Steele has a page Access Reference Problems that might provide some good assistance.

Duane
Hook'D on Access
MS Access MVP

 

[zoroaster]

Thanks for the respose Duane, but the problem wasn't related to any one database, but all of them - I've had our IT department reinstall Access on my machine to no avail.

However, I have now, after a bit more investigation, finally fixed this frustrating issue - what I'd done (sometime before Christmas - I'd simply not noticed until a couple of days ago) was to change the default List Seperator in Regional Settings from a Comma to a Pipe, so that I could export as CSV from Excel using a pipe as the default rather than a comma (since data often contains commas) - this has had the undesireable side-effect on the way Jet reads and interprets SQL, as it was substituting pipes for commas in the SQL, including all expressions and functions. I've now reverted to the Comma as the default list seperator and all is well!

Regards, Z

The road to hell is temporarily closed due to resurfacing with targets achieved

 

[dhookom]

Z,
Thanks for replying with your resolution. I will need to file this away.

Duane
Hook'D on Access
MS Access MVP