How to hardcode " and " into a query field?

[Divit11]

Hello All,

I'm just beginning to work with Access 2000 and VBA. I've been reading a lot of your threads and have been able to incorporate some of your solutions into my first address book project. Do you remember those early successes? Well, I wanted to do a mail merge using MS Word 2000 and my new address DB. I thought I’d be real smart and create fields for: abFirstName, abLastName and abSpouse in my tblAddressBook table. I then created a query to be selected in Word because I only wanted a few records to be considered.

In my Word document I wanted to have a greeting: Dear <<abFirstName>> and <<abSpouse>>,

Here is the problem: when a record has no value in the abSpouse field the mail merge goes ahead and prints:

Dear Firstname and ,

Short of “fat fingering” my database to add the prefix “ and “ & spouse name is there a preferred method to get the “ and “ to be omitted when abSpouse field is null? I also don’t want to violate the rules of normalization and create another field, abGreeting, in which the first name and spouse’s names would be repeated.

Thanks in advance.

Divit11

 

[pbaldy]

I use this type of thing:

Greeting: [abFirstName] & (" and " + [abSpouse])

 

[neileg]

I think this will give you what you want

Greeting: IIf(IsNull([abSpouse]),[abFirstName],[AbfirstName] & " and " & [abSpouse])

 

[Divit11]

Paul and Neil, thank you for your solutions. The SQL syntax was totally foreign to me but I was able to focus on adding that to an additional field in my select query.

You could have told me all that but then I would not have had the benefit of finding further explanations and samples in one of the Microsoft Press reference "Running Microsoft Access 2000". Your solutions helped me narrow down and focus on a chapter that led to a more complete explanation.

Now, anyone have a good SQL reference book for noobies?

Divit11

 

[neileg]

A good place to start is your own queries. Having constructed a working query in the QBE grid, take a look at the SQL view and try and follow what is happening.

Having said that, I'm an SQL duffer and still struggle with simple stuff!