expesssion in query

  • Thread starter Thread starter Todd
  • Start date Start date
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.
 
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])
 
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
 
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.
Click to expand...


You can use either:
IIf(table1.Name Is Null, table2.Description, table1.Name)
or
Nz(table1.Name, table2.Description)
 
Sorry about that guys. My field name isn't really NAME (bad example).
Thanks for the help!!
 
Back
Top