Display error for records concatenated in query statement

Resolved · High Priority · Version 2003

Wed 16 Jun 2010, 15:38 Delegate Pasquale said...

Pasquale has attended:
Access Introduction course
Access Intermediate course
Access Advanced course

Display error for records concatenated in query statement

Hello,

I've set-up a query that is meant to display a set of address fields as one record from my contact info table.

The contact info table includes the following field: address1, address2, city and postcode.

The majority of records have information for address1, city and postcode, and a few have the same information plus the address2 field.

I've written the following expression to display the address:

Address: [contact info.address1]+", "+[contact info.address2]+", "+[contact info.city]+" "+[contact info.postcode]

When I run the query the only records that appear are the ones who's address1, address2, city and postcode fields contain information, the other records appear blank.

How can I change the expression so that all Address records are displayed?

Fri 18 Jun 2010, 13:57 Trainer Newson said...

RE: Display error for records concatenated in query statement

I'm guessing here that you want to view all the blank records in the tables

Make a copy of that query and try typing the word

Null in the criteria box for the address

That should display blanks.

If it doesn't get back to me

Thanks

Mon 21 Jun 2010, 10:01 Delegate Pasquale said...

RE: Display error for records concatenated in query statement

Hi,

I've tried placing null in the criteria box, but that hasn't worked, the query doesn't list any records.

I've tried the follwing code in the criteria box:

("address2"="address2") Or ("address2"=" ") as well as

("address2"="address2") Or ("address2"="null")

however, both only display the addresses that have text in the address1, address2, city and postcode fields, those with blank fields in the address2 fields are not displayed.

Regards