"WHERE NOT" 2

[PWD]

Good morning, I have a table where events are recorded that show up to 3 referral fields. I want to only select the records where at least one referral has been made. I had it in mind that I could wrap "WHERE Field1 Is Null And Field2 Is Null And Field3 Is Null" with a NOT() but that doesn't and still shows all records, even those where all those fields appear to be blank. Any ideas please?

Many thanks,
DÇ$

 

[PHV]

What about this ?
WHERE Trim(Field1 & Field2 & Field3 & "")<>""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWD]

Hi there, unfortunately this just gave the one record where all the fields were completed. (It turns out there are 4 fields but this one was also the only one with 3 completed.) I like the idea of "Trim" as I've noticed some spaces in some of the "blank" fields.

Many thanks,
DÇ$

 

[PHV]

this just gave the one record where all the fields were completed
Really ?
What is the exact WHERE clause you tested ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWD]

Here's what I put in. It's a relatively small sample (280) and likely that most people would only be referred to one or two places out of the possible maximum of 4.

WHERE (((Trim(([dbo_cpa_autism_kpi_webform].[ref_to_during_au001_userdesc]) And ([dbo_cpa_autism_kpi_webform].[ref_to_during_aut1_userdesc]) And ([dbo_cpa_autism_kpi_webform].[ref_to_during_aut3_userdesc]) And ([dbo_cpa_autism_kpi_webform].[ref_to_during_aut4_userdesc]) And ""))<>""));

I wanted to produce cleaner data that didn't show records where there had been no referral at all.

Many thanks,
DÇ$

 

[SkipVought]

There were no ANDs in PHV's code!

 

[PWD]

Duh, spot on Skip. Many thanks to you both!!

Many thanks,
DÇ$