Going mad!!!

[news.cable.ntlworld.com]

I have a database query using three fields from a Table: Title (Mr, Mrs,
Miss, etc), Firstnames (Michael David, or just Michael, etc) and Surname.
I've produced a query which, if there is data in Title, selects the Title,
then a space, then the first name of Firstnames, then a space, then the
Surname. If the Title is missing, it should use just the first name of the
Firstnames.

The expression I've used is:

Titletest: IIf(IsNull([Title]),IIf(InStr([Firstnames],"
")=0,[Firstnames],Left([Firstnames],InStr([Firstnames]," ")-1)),[Title] & "
" & [Surname])

When I try to run this, it comes up with the error message: 'Undefined
function 'Left' in expression.

But if I create a new test database, make a table with just those three
fields create a query and use exactly the same expression, it works
perfectly.

The table in the existing database seems fine, there is no syntax error on
fieldnames, etc. Even if I copy the query across from the test database, it
still comes up with the same error message.

What is going on here? What am I doing wrong?

Cheers

James
PS Using MS Access 2003 SP2

 

[Wayne Morgan]

Check for bad references. The one that's bad may not be the library that
holds the Left() function.

http://www.allenbrowne.com/ser-38.html

 

[Fred Bloggs]

I've tried that, and in the References box, it shows 'Missing: Microsoft
Common Dialog Control 6.0 (SP3).
I cannot locate it on my PC. Where do I obtain this from?

Cheers
James

Check for bad references. The one that's bad may not be the library that
holds the Left() function.

http://www.allenbrowne.com/ser-38.html

--
Wayne Morgan
MS Access MVP

I have a database query using three fields from a Table: Title (Mr, Mrs,
Miss, etc), Firstnames (Michael David, or just Michael, etc) and Surname.
I've produced a query which, if there is data in Title, selects the Title,
then a space, then the first name of Firstnames, then a space, then the
Surname. If the Title is missing, it should use just the first name of the
Firstnames.

The expression I've used is:

Titletest: IIf(IsNull([Title]),IIf(InStr([Firstnames],"
")=0,[Firstnames],Left([Firstnames],InStr([Firstnames]," ")-1)),[Title] &
" " & [Surname])

When I try to run this, it comes up with the error message: 'Undefined
function 'Left' in expression.

But if I create a new test database, make a table with just those three
fields create a query and use exactly the same expression, it works
perfectly.

The table in the existing database seems fine, there is no syntax error
on fieldnames, etc. Even if I copy the query across from the test
database, it still comes up with the same error message.

What is going on here? What am I doing wrong?

Cheers

James
PS Using MS Access 2003 SP2

 

[Wayne Morgan]

If you don't need it, just uncheck the reference. This problem is the
advantage of using the API calls instead of the Common Dialog. If the
version gets changed, the reference breaks. For information on using API
calls instead, see these articles.

http://www.mvps.org/access/api/api0001.htm
http://www.mvps.org/access/api/api0002.htm