Querying a Field with Null Entries

[Guest]

I have a table setup as follows:

CustomerID(key).....CustomerAbbreviation.....CustomerFullName

Obviously, the CustomerID field is fully populated, and so is the
CustomerAbbreviation field. CustomerFullName is NOT fully populated, only 75%
have entries.

When I use CustomerFullName in a query, can I automatically return the
CustomerAbbreviation, IF and only IF, the CustomerFullName comes back as
null? It would be even more helpful if an asterisk or other symbol preceded
the CustomerAbbreviation is this instance, to help me see where the
CustomerFullName is null so I can do something about it.

 

[tina]

in the first blank column in your query design view, enter the following
expression in the first row, as

FinalName: IIf(CustomerFullName Is Null, "*" & CustomerAbbreviation,
CustomerFullName)

all of the above goes on one line, of course.

hth

 

[Guest]

Hi Rich,

Try this SQL (structured query language) statement. Create a new query. In
query design view, click on View > SQL View. Copy the SQL statement shown
below and paste it into the SQL window. Run the query.

SELECT CustomerID, CustomerAbbreviation,
IIf(IsNull([CustomerFullName]),"**" & [CustomerAbbreviation] &
"**",[CustomerFullName]) AS Customer
FROM tblCustomers;


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have a table setup as follows:

CustomerID(key).....CustomerAbbreviation.....CustomerFullName

Obviously, the CustomerID field is fully populated, and so is the
CustomerAbbreviation field. CustomerFullName is NOT fully populated, only 75%
have entries.

When I use CustomerFullName in a query, can I automatically return the
CustomerAbbreviation, IF and only IF, the CustomerFullName comes back as
null? It would be even more helpful if an asterisk or other symbol preceded
the CustomerAbbreviation is this instance, to help me see where the
CustomerFullName is null so I can do something about it.

 

[Van T. Dinh]

Both Tina and Tom are correct. However, I prefer to use:

FinalName: IIf(Len(Trim$([CustomerFullName] & "")) = 0,
"*" & [CustomerAbbreviation], [CustomerFullName])

since the [CustomerFullName] is a Text Field and it is possible (depending
on how you specified the Field's Required and AllowZeroLengthString
Property) that you have empty String or white-space characters instead of
Null as the Field values. They all show up as blank when you view the data.

The above will pick up the [CustomerFullName] if it is Null, an empty String
or white spaces ...

 

[Guest]

Thanks to all of you for the help.

I am having difficulty making this work because it's a multi-table query and
both tables are related by the same numerical value, which is CustomerID. In
the customers table, CustomerID is the primary key.

When I enter the expression into a query, Access finds nothing wrong with
the expression but the query values return blank (null).

 

[Guest]

Please disregard my previous reply, I have everything working now. Thanks
again!

 

[Guest]

Hi Rich,

Your new reply just arrived as I was going to attempt to offer further help.
So, thanks for letting us know that you got it working.

Van: Thank You for your version as well.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thanks to all of you for the help.

I am having difficulty making this work because it's a multi-table query and
both tables are related by the same numerical value, which is CustomerID. In
the customers table, CustomerID is the primary key.

When I enter the expression into a query, Access finds nothing wrong with
the expression but the query values return blank (null).

 

[tina]

you're welcome