String function in query not working

[Stephen Bowyer]

I have an Access 97 database, which we use with a VB5 front end.
If I create a new query in the database, the string functions left, mid etc
do not work.
For example the query:
CalcField: Left([MyField],2) returns the correct number of records, but
CalcField contains only #Error for each record. MyField is a simple text
field, length 50. I've even stripped a copy of the database down to just
the one table with 20 records and 5 fields, but get the same result. This
type of query works fine in other databases.

We've been having corruption problems recently, and I'm wondering if this
could be a cause?

Any guidance appreciated,

Steve Bowyer

[Salad]

Stephen Bowyer wrote:

I have an Access 97 database, which we use with a VB5 front end.
If I create a new query in the database, the string functions left, mid etc
do not work.
For example the query:
CalcField: Left([MyField],2) returns the correct number of records, but
CalcField contains only #Error for each record. MyField is a simple text
field, length 50. I've even stripped a copy of the database down to just
the one table with 20 records and 5 fields, but get the same result. This
type of query works fine in other databases.

We've been having corruption problems recently, and I'm wondering if this
could be a cause?

Any guidance appreciated,

Steve Bowyer

Check your references. Open up your Code Window and select
Tools/References. Something may be missing.

[Terry Kreft]

I'm slightly confused as to where you are using this query, if you use
function calls in an Access query and then try to use that query from VB5 it
will not work.

If you are saying that the query does not work from within the Access
database then

1) look at your references
To do this: open a module, got to the Tools\Refences menu item and check
that none are marked as MISSING, if they are then you'll need to take steps
to correct this situation

2) possibly your database is corrupt in which case you can do a compact and
repair in order to try and correct the problem.
Do this on a copy of the database just in case further damage is caused

3) if the above fails then look at creating a new db and importing
everything from your old database.

4) if all else fails then look at reinstalling Access.

--
Terry Kreft
MVP Microsoft Access
"Stephen Bowyer" <st************@nospamdsl.pipex.net> wrote in message
news:40***********************@news.dial.pipex.com ...

I have an Access 97 database, which we use with a VB5 front end.
If I create a new query in the database, the string functions left, mid etc do not work.
For example the query:
CalcField: Left([MyField],2) returns the correct number of records, but
CalcField contains only #Error for each record. MyField is a simple text
field, length 50. I've even stripped a copy of the database down to just
the one table with 20 records and 5 fields, but get the same result. This
type of query works fine in other databases.

We've been having corruption problems recently, and I'm wondering if this
could be a cause?

Any guidance appreciated,

Steve Bowyer