Anniversary date in a query

[penret]

SELECT CLIENT.PLAN_CODE, EMPLOYEE.[First Name], CLIENT.COMPANY, CLIENT.CONTACT, CLIENT.[CONSULTANT CONTACT], CLIENT.[CONSULTANT SALUTATION], CLIENT.STREET, CLIENT.CITY, CLIENT.STATE, CLIENT.ZIP, CLIENT.SALUTATION, EMPLOYEE.MI, EMPLOYEE.[Last Name], CLIENT.ANN, CLIENT.SPECIAL, CLIENT.[year of sale], CLIENT.STATUS, CLIENT.TYPE
FROM CLIENT INNER JOIN EMPLOYEE ON CLIENT.EE_CODE = EMPLOYEE.EE_Code
WHERE (((CLIENT.ANN) Like "12/31/**") AND ((CLIENT.SPECIAL) Is Null) AND ((CLIENT.[year of sale])<>2005) AND ((CLIENT.STATUS)="1") AND ((CLIENT.TYPE)<>"ps"))
ORDER BY EMPLOYEE.[First Name], CLIENT.COMPANY;

This is a mail merge query.
1. If Client Ann is like 12/31/** and Client Type is like PS then it runs fine.
2. If Ann is 12/31/** and Type is Not PS (as above)it has no records when merged.
3. If Ann is null and Type is Not PS it merges fine.

Thank you for any help

 

[lespaul]

so what's the problem?

Leslie

 

[penret]

Hi;

The problem is item 2. When Ann is 12/31/** and Type is NotPS then when it merges there are no records.

If I remove 12/31/** and keep NotPS I get all my records.

12/31/** and PS work just fine

Thanks, I hope this is clear enough

 

[lespaul]

with this clause:

WHERE (((CLIENT.ANN) Like "12/31/**") AND ((CLIENT.SPECIAL) Is Null) AND ((CLIENT.[year of sale])<>2005) AND ((CLIENT.STATUS)="1") AND ((CLIENT.TYPE)<>"ps"))

you are only going to get records where:

Client.Ann = 12/31/####
Client.Special = Null
Client.Yearofsale is not next year
Client.Status = 1
Client.Type Is NOT 'PS'

Do you want the PS client type? What records do you want to return?



Leslie

 

[PHV]

Have you tried Nz(CLIENT.TYPE)<>"ps" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[penret]

Thanks a bunch PH - that works perfectly.

Leslie: The two records I wanted were 1. all clients with an anniversary of 12/31 and are Profit Sharing and 2. All clients with an anniversary of 12/31 who are not Profit Sharing (e.g. 401K.) The Nz did the trick

Sorry if I was unclear, I appreciate yout help. Thanks