IIf error message

[Dudley]

I get an error message that there is an extra ) in Access 2000 query

District: IIf(NotIsNull([Thoroughfare]),"Thoroughfare=" & ([Thoroughfare]))

I cannot see what is wrong with it

Thanks for any help.
Dudley

 

[Ken Snell [MVP]]

Try this:

District: IIf(Not IsNull([Thoroughfare]),"Thoroughfare=" & ([Thoroughfare]))


However, it'll be better if you use this:

District: "Thoroughfare=" + [Thoroughfare]

 

[Dudley]

Thanks very much for your help. There is something odd going on. When I run
the query, it deletes the table designation and gives a message that the
field is in more than one table. When I put it back, it then runs but gives
an error message. I have copied and pasted both your alternatives. The first
gives the same message that a ) is missing, and the second one a syntax
error. Is it worth trying re-installing Office?

Dudley

Try this:

District: IIf(Not IsNull([Thoroughfare]),"Thoroughfare=" & ([Thoroughfare]))


However, it'll be better if you use this:

District: "Thoroughfare=" + [Thoroughfare]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I get an error message that there is an extra ) in Access 2000 query

District: IIf(NotIsNull([Thoroughfare]),"Thoroughfare=" &
([Thoroughfare]))

I cannot see what is wrong with it

Thanks for any help.
Dudley

 

[Ken Snell [MVP]]

Change the query view to SQL View (top left button on Toolbar), and copy all
the text that you see in that window. That is the SQL statement for the
query. Paste that text into a reply here so that we can see the query that
you're trying to use.

If Thoroughfare field is in more than one of the tables in your query, then
you'll need to qualify the field:

District: "Thoroughfare=" + [NameOfTable].[Thoroughfare]

Also be sure that you do not have a field named District in the tables in
your query.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks very much for your help. There is something odd going on. When I
run
the query, it deletes the table designation and gives a message that the
field is in more than one table. When I put it back, it then runs but
gives
an error message. I have copied and pasted both your alternatives. The
first
gives the same message that a ) is missing, and the second one a syntax
error. Is it worth trying re-installing Office?

Dudley

Try this:

District: IIf(Not IsNull([Thoroughfare]),"Thoroughfare=" &
([Thoroughfare]))


However, it'll be better if you use this:

District: "Thoroughfare=" + [Thoroughfare]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I get an error message that there is an extra ) in Access 2000 query

District: IIf(NotIsNull([Thoroughfare]),"Thoroughfare=" &
([Thoroughfare]))

I cannot see what is wrong with it

Thanks for any help.
Dudley

 

[Dudley]

Your new version works fine. My error was to think that it was sufficient to
designate the table on the table line. I have given the same names to fields
in different tables as I thought it would make it easier to copy addresses
between tables, but maybe this was a mistake.

Thanks again for all your help.
Dudley

Change the query view to SQL View (top left button on Toolbar), and copy all
the text that you see in that window. That is the SQL statement for the
query. Paste that text into a reply here so that we can see the query that
you're trying to use.

If Thoroughfare field is in more than one of the tables in your query, then
you'll need to qualify the field:

District: "Thoroughfare=" + [NameOfTable].[Thoroughfare]

Also be sure that you do not have a field named District in the tables in
your query.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks very much for your help. There is something odd going on. When I
run
the query, it deletes the table designation and gives a message that the
field is in more than one table. When I put it back, it then runs but
gives
an error message. I have copied and pasted both your alternatives. The
first
gives the same message that a ) is missing, and the second one a syntax
error. Is it worth trying re-installing Office?

Dudley

Try this:

District: IIf(Not IsNull([Thoroughfare]),"Thoroughfare=" &
([Thoroughfare]))


However, it'll be better if you use this:

District: "Thoroughfare=" + [Thoroughfare]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




I get an error message that there is an extra ) in Access 2000 query

District: IIf(NotIsNull([Thoroughfare]),"Thoroughfare=" &
([Thoroughfare]))

I cannot see what is wrong with it

Thanks for any help.
Dudley

 

[Ken Snell [MVP]]

Naming fields in different tables with same field name is not necessarily a
mistake, so long as you know when you need to fully qualify the table
reference and when you don't.

For your IIf example, and for any other expressions that you might use in a
calculated field, the "Table:" row is not used by ACCESS for identifying the
table from which a field in the expression comes. You must qualify the field
inside the expression.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Your new version works fine. My error was to think that it was sufficient
to
designate the table on the table line. I have given the same names to
fields
in different tables as I thought it would make it easier to copy addresses
between tables, but maybe this was a mistake.

Thanks again for all your help.
Dudley

Change the query view to SQL View (top left button on Toolbar), and copy
all
the text that you see in that window. That is the SQL statement for the
query. Paste that text into a reply here so that we can see the query
that
you're trying to use.

If Thoroughfare field is in more than one of the tables in your query,
then
you'll need to qualify the field:

District: "Thoroughfare=" + [NameOfTable].[Thoroughfare]

Also be sure that you do not have a field named District in the tables in
your query.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks very much for your help. There is something odd going on. When I
run
the query, it deletes the table designation and gives a message that
the
field is in more than one table. When I put it back, it then runs but
gives
an error message. I have copied and pasted both your alternatives. The
first
gives the same message that a ) is missing, and the second one a syntax
error. Is it worth trying re-installing Office?

Dudley

:

Try this:

District: IIf(Not IsNull([Thoroughfare]),"Thoroughfare=" &
([Thoroughfare]))


However, it'll be better if you use this:

District: "Thoroughfare=" + [Thoroughfare]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




I get an error message that there is an extra ) in Access 2000 query

District: IIf(NotIsNull([Thoroughfare]),"Thoroughfare=" &
([Thoroughfare]))

I cannot see what is wrong with it

Thanks for any help.
Dudley