Quick Links ▼

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Access Training and help » Display error for records concatenated in query statement

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Display error for records concatenated in query statement

Resolved · High Priority · Version 2003

Reply Wed 16 Jun 2010, 15:38Delegate 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?

For upcoming training course dates see: Pricing & availability

Reply Fri 18 Jun 2010, 13:57Trainer 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

Reply Mon 21 Jun 2010, 10:01Delegate 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

Access articles

· Get A Head Start With Access 2010 Templates
· How To Show JPG Or GIF Images In Access 2003 Forms
· Innovation with Microsoft Access
· User-Proofing Your Access Database: Part 2
· How To Make Access 2010 Databases Compatible With Access 2007

Please browse our web site to find out more about
advanced micorosoft access training and other Microsoft training courses.

Access tip:

Choosing data types in Access

Not sure which data type to use for your Access fields? Here are some guidelines to help you choose a data type to assign to a field.

- The Text data type can accept up to 255 characters. For information that will be recorded in paragraphs, use Memo.

- Also use Text for numbers that aren't going to be used in calculations, e.g. phone numbers.

- Use the Currency data type for monetary amounts.

- Use Date/Time for dates.

- Most other numbers can use the Number data type, but the Field Size property may have to be altered.

- For fields that have only two alternatives (yes, no) use Yes/No data type. If there is likely to be a third entry option (e.g. maybe or don't know) use Text instead.

View all Access hints and tips