IIF statement in a query

[Robert Neville]

I would like to create query with an expression, which places a
pointer to a default value if a field value is null or zero-length .
This query recreates Company and Address links in a relationship
table. Some companies do not have addresses, thus need a default
address reference, called NONE, so the phone and email information may
be place in the relationship table. This reference address has an
AddrID that equals 1.

My first expression failed. Let me know if you have any suggestion.
Here's my idea so far.

IIF(AddrID="",1,AddrID)

IIF(IsNull(AddrID),1,AddrID)

IIF(Nz(AddrID),1,AddrID)

 

[Brian Camire]

You might try:

Nz(AddrID, 1)

But, are you sure you want to do this? In a database, it's generally best
practice to leave a field Null if you don't know it's value, instead of
using "special" values.

 

[Robert Neville]

You might try:

Nz(AddrID, 1)

But, are you sure you want to do this? In a database, it's generally best
practice to leave a field Null if you don't know it's value, instead of
using "special" values.

Thanks for your reply. You brought up a relevant question about
whether a default pointer should be used. I have to give it more
thought. The approach stem from placing values in both index fields,
CompID and AddrID. The relationship requires both indexes for each
record to prevent duplicates. This relationship table, trelCompAddr
hold more information than the indexes; look below for details.

The larger picture entails creating numerous queries, which import
data from a flat file data repository into my main tables. This query
forms the basis to rebuild the link between Company and Address
records while placing phone information in the table as well.

My first attempt with your expression generated this error.

< Syntax error (comma) in query expression 'tblAddr.[Nz(AddrID],1)]' >

After removing the Addr table destination, it generated this error.

< Circular reference caused by alias 'AddrID' in query definitation's
Select list >

After removing the AddrID alias, it worked, yet generated a default
column heading. Let me know if you have more insights about my
situation. This select query forms the basis for my update query and
append query, which should have the appropriate column heading.

-----------------------------------------------------------------------------

TABLE
tblComp trelCompAddr tblAddr

FIELDS
CompID CompID AddrName
CompName AddrID Addr1
CompTypeID Addr2 City
Sector AddrTypeID State
Phone PostalCode
Phone2 Country
Fax2
Email

 

[Brian Camire]

Try:

Nz(tblAddr.AddrID,1)

instead of

tblAddr.[Nz(AddrID],1)]

If you post the SQL for your query I might be able to give you a more
detailed suggestion.

If your query includes more than one table with an AddrID field, you need to
specify in the expression the table (tblAddr in this case) whose field you
want to use.

For companies with no address, I would say you might handle this by simply
not having a related record for them in your trelCompAddr table. I guess
what you're saying is that then you would have no place to put phone
numbers.

A couple of workarounds might be:

1. Have a new table of "Company Phone Numbers" with a many-to-one relation
to tblComp, and a new table of "Company Phone Number Addresses" probably
with a one-to-one relation to "Company Phone Numbers" and a many-to-one
relation to tblrelCompAddr. This allows you to multiple specify phone
numbers for a company without having to associate them with an address.

2. Or, add a default phone number fields to tblComp. This allows you to
specify a default phone number for a company in case you don't have any
addresses for it.

Any other suggestions out there?

You might try:

Nz(AddrID, 1)

But, are you sure you want to do this? In a database, it's generally best
practice to leave a field Null if you don't know it's value, instead of
using "special" values.

Thanks for your reply. You brought up a relevant question about
whether a default pointer should be used. I have to give it more
thought. The approach stem from placing values in both index fields,
CompID and AddrID. The relationship requires both indexes for each
record to prevent duplicates. This relationship table, trelCompAddr
hold more information than the indexes; look below for details.

The larger picture entails creating numerous queries, which import
data from a flat file data repository into my main tables. This query
forms the basis to rebuild the link between Company and Address
records while placing phone information in the table as well.

My first attempt with your expression generated this error.

< Syntax error (comma) in query expression 'tblAddr.[Nz(AddrID],1)]' >

After removing the Addr table destination, it generated this error.

< Circular reference caused by alias 'AddrID' in query definitation's
Select list >

After removing the AddrID alias, it worked, yet generated a default
column heading. Let me know if you have more insights about my
situation. This select query forms the basis for my update query and
append query, which should have the appropriate column heading.

-------------------------------------------------------------------------- ---

TABLE
tblComp trelCompAddr tblAddr

FIELDS
CompID CompID AddrName
CompName AddrID Addr1
CompTypeID Addr2 City
Sector AddrTypeID State
Phone PostalCode
Phone2 Country
Fax2
Email