expesssion in query


T

Todd

I've got a query that uses two tables (table1 and table2 let's say). In
table1 there is a field called NAME and table2 has a field called
DESCRIPTION. What expression can I put in a blank field within the query
that will show the data from table1 NAME but if table1 field NAME is blank it
will show the table2 field DESCRIPTION data? I assume it would be an IF THEN
statement or something but I'm not sure how to write it.
 
Ad

Advertisements

R

RonaldoOneNil

First of all, NAME is not a good field name to use because it is a reserved
word.
The syntax you need is an IIF statement in your query column.
NameofColumn: iif(isnull([Name]),[Description],[Name])
 
J

Jeff Boyce

Todd

I may be taking your post too literally, if so, ignore the first following
paragraph:

Access treats the word "Name" (?and maybe "Description") as a reserved word.
If your field is truly named "Name", you may not get what you expect.
Change the fieldname to something more expressive/meaningful.

Try the following in the query design window, in a new "field":

NewField: IIF(Nz([NAME],"")="",[DESCRIPTION],[NAME])

Note the use of the Nz() function -- a "blank field" may mean a Null, or may
mean a zero-length string ("").

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

Marshall Barton

Todd said:
I've got a query that uses two tables (table1 and table2 let's say). In
table1 there is a field called NAME and table2 has a field called
DESCRIPTION. What expression can I put in a blank field within the query
that will show the data from table1 NAME but if table1 field NAME is blank it
will show the table2 field DESCRIPTION data? I assume it would be an IF THEN
statement or something but I'm not sure how to write it.


You can use either:
IIf(table1.Name Is Null, table2.Description, table1.Name)
or
Nz(table1.Name, table2.Description)
 
Ad

Advertisements

T

Todd

Sorry about that guys. My field name isn't really NAME (bad example).
Thanks for the help!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top