Going mad even more!!!

[Fred Bloggs]

Now I've tried creating a new table called 'Nametest' and a new query using
exactly the same parameters, and it gives the error message:
Extra ) in query expression
'Nametest.IIf(IsNull([Title]),IIf(InStr([Firstnames],"

")=0,[Firstnames],Left([Firstnames],InStr([Firstnames]," ")-1)),[Title] &
"
" & [Surname]).

I'm losing it!
James

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

 

[Douglas J. Steele]

You've got one too many parentheses after InStr([Firstnames]," ")-1))

Of course, the Nametest. at the beginning is going to cause an error as soon
as you correct the parenthesis...

If you've only got one table in the query, drop the Nametest reference. If
you've got more than one, then each field in your expression may need the
reference: you can't short circuit like that:

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


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Now I've tried creating a new table called 'Nametest' and a new query
using exactly the same parameters, and it gives the error message:
Extra ) in query expression
'Nametest.IIf(IsNull([Title]),IIf(InStr([Firstnames],"

")=0,[Firstnames],Left([Firstnames],InStr([Firstnames]," ")-1)),[Title] &
"
" & [Surname]).

I'm losing it!
James

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

 

[Fred Bloggs]

Only the error message showed the Nametest., not my query, which has only
the one table.

I did a check on References and found Common Dialog Control 6.0 (SP3).
Having reinstalled it, the query now works okay.

But if I take off the parenthesis as you suggest, I get a warning message
which says:

"The expression you entered has a function containing the wrong number of
arguments."

Comments?
James

You've got one too many parentheses after InStr([Firstnames]," ")-1))

Of course, the Nametest. at the beginning is going to cause an error as
soon as you correct the parenthesis...

If you've only got one table in the query, drop the Nametest reference. If
you've got more than one, then each field in your expression may need the
reference: you can't short circuit like that:

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


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Now I've tried creating a new table called 'Nametest' and a new query
using exactly the same parameters, and it gives the error message:
Extra ) in query expression
'Nametest.IIf(IsNull([Title]),IIf(InStr([Firstnames],"

")=0,[Firstnames],Left([Firstnames],InStr([Firstnames]," ")-1)),[Title]
& "
" & [Surname]).

I'm losing it!
James

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