quote:

---

Originally posted by Topaz

"What do you mean it works best when using OR? Do you get the correct results?" when using or it is not looking at the records in the way i want it to. hope i explain it better below

or(IDStatus LIKE '')
or (NOT (Task LIKE '%x%'))
or (ExpressEmail LIKE '%@%')
or (WebSite NOT LIKE '')
or (Unsubscribe = 0)
or (Artwork LIKE '')

by using the 'or part' i mean that this criteria doenst want to be AND. eg a contact where idstatus not like '' but the expressemail like '%@%'

or

even simplier - i am trying to find a record where the website field is not blank and the idstatus field is not blank and the task field doesnt contain 'x' and the expressemail field contains '@' and artwork is not blank and unsubscribe is equal to one

but sometimes a record can match the first two lines of the red part and not the rest, but i still want to bring them up in the results. At the same time the black part is a definate must match criteria.

---

SELECT *
FROM dbo.wce_contact
WHERE (Mail1Date IS NOT NULL)
AND (Mail2Date IS NULL)
AND (Mail3Date IS NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)
AND ((IDStatus LIKE '')
or (NOT (Task LIKE '%x%'))
or (ExpressEmail LIKE '%@%')
or (WebSite NOT LIKE '')
or (Unsubscribe = 0)
or (Artwork LIKE ''))

quote:

---

Originally posted by Topaz

that just seems to bring up the results where the red part matches our database and then does the black bit aswell.

hth? im trying to find records in our database that MUST match the black part and can match ANY line of the red part as long as it matches the black part. becasue at the moment it looks like it is doing the black and then doing the red parts seperately

---

quote:

---

Originally posted by Topaz

ill have to post some sample data up tomorrow...not at work right now.

for instance that query brings up records that has something in the idstatus (so not '') or has something in the expressemail field where it doesnt have an '@'

thanks for your help so far!

---

quote:

---

Originally posted by Topaz

in fact this might be easier to understand.

I need a query that will find records that match any line that is in red but at the same time it must match the black part of the qry exactly.

---