#error on text functions (97)

Topic

New Reply

Hello all,

I have a big database where I run a query to extract some text from a textfield. I use the expression code : left([Pcode]);4)
When running the query it is giving me a #ERROR result
When I try some other functions like MID() or RIGHT() it gives the same result. The LEN() function gives a good result.
So I thought that the table was corrupt, after building a new table and query the result was the same
Even repairing the database was no solution.

Finnaly a build a new database and linkt to the tables of the problemdatabase and created a new query. This time the result was good.
So I now create a new database and import all the tables form the problemdatabase and problem solved

… so why posting this problem…

I just want to know what the reson could be that those functions where not working.

Reply | Quote

Replies

Most probably, the old database was corrupt in a way that Compact and Repair Database couldn’t correct. Importing all tables into a new database is often (not always, unfortunately) a good way to recover the data from a problem database.

There is also a smal chance that there was a problem with references in the old database, but you’d probably have got error messages then, instead of just #ERROR.

Reply | Quote

Your function has an extra parenthesis in it. I think it should be:

left([Pcode];4)

Reply | Quote

Good catch!

Reply | Quote

sorry, that was a typo in the message, the function was correct in Access.

Reply | Quote