If statement in query

[ccordner]

I am trying to create one of the old mailmerge classics - the "Here are your details, please check and confirm".

I have a query which contains details of their name and address and that of their next of kin.

What I want to do is put something in the next of kin fields, so that if the address is blank (i.e. address of their next of kin is the same as theirs), it prints their address, but if not it puts their next of kin's address.

Ideally, it would be if all five address fields are blank.

Thanks
Chris.

 

[jasmith4]

So you have a table with at least twelve fields: five for a person's address, and five for the person's next-of-kin's address, right? Let me assume they're called Name, Addr1, Addr2, City, State, Zip, NOKName, NOKAddr1, NOKAddr2, NOKCity, NOKState and NOKZip -- that's in the table.

In the query the expression for the first address line would be:

=Iif(len(trim$([NOKAddr1])&trim$([NOKAddr2])&trim$([NOKCity])&trim$([NOKState])&trim$([NOKZip]))=0, [Addr1], [NOKAddr1])
​

The second address line would be:

=Iif(len(trim$([NOKAddr1])&trim$([NOKAddr2])&trim$([NOKCity])&trim$([NOKState])&trim$([NOKZip]))=0, [Addr2], [NOKAddr2])
​

and so on. The only difference is the names of the two fields at the end.

 

[ccordner]

Actually they are two tables, but I have tweaked that and it works great - thanks.

But I have another question.

That query is based on the "Staff" table (their details) and a corresponding table ("Next of Kin"). Each person has a Pay Number. This is the primary key in both tables and the link between them.

However, the query obviously only shows people for which there is a "Next of Kin" record. Is it possible to make it include people where there is no "Next of Kin" record (Where we haven't been given the information) or even a separate query for people where we have no "Next of Kin"?

Thanks
Chris

 

[Norie]

Chris

Can you post your query?

It's actually straightforward to do what you want.

All you need to do is set the join between the tables so that it shows all records from the staff table and only those that match from the other table.

 

[ccordner]

Thanks for that. Just had a look and found the join type!

I'm a bit new to Access: we did it at school years ago, but I'm much more comfortable with Excel and vba in Excel.

Chris

 

[Norie]

Chris

You might be able to do the first part in a similar way too.

That really depends on how the data is structured.

In particular how you deal with things when the 2 addresses are the same.

eg do you enter something like 'As Above' in the address field(s) the next of kin record

 

[ccordner]

I just left the address fields blank, where the address is the same.

To be honest it's a project I've just started, so virtually everything is being entered in datasheet view.

I haven't really set up the forms and such for it yet.

 

[Norie]

If the fields are just blank then you can probaby use Nz.

Something like this perhaps:

Next of Kin Address1:Nz(tblNextOfKin.Address1, tblStaff.Address1)

The basically says, if Adress1 in next of kin table is null use Address1 from the staff table.

Well that's what it's meant to say, triple check the syntax.

 

[ccordner]

That's what I was originally trying to do, but I thought I could encounter problems. If there are any blank lines in the Next of Kin address that weren't blank in their own address, you could end up with a mixture of two addresses!

Hence the check that all the fields are blank.

Thanks
Chris

 

[Norie]

Good point.

I suppose what you could do there is instead of checking each individual address field just check the first one.

So for the Address2 field:

Next of Kin Address2:Nz(tblNextOfKin.Address1, tblStaff.Address2)