Eliminating blank fields in a query

[ledmark]

I've got a query set up to look for date, patient, and reminder fields with a criteria of [Enter Patient's Name]. So when I run it all the records meeting these fields for the patient come up but not all of them have reminders.

How would I write the criteria for the reminders so that only the fields with reminders come up? - not all the records including the ones with reminders?

Thanks for any help.

Laura

 

[Brianwarnock]

What indicates a reminder? E.G. If the field is Null if there is no reminder then criteria Is Not Null will eliminate those.

Brian

 

[just_a_guy]

The problem with Nulls and character strings is that, it is
difference to tell between Null and an empty string (i.e. "").
If a user only keys in one blank, in the field, it may look empty
to the user but it may not be empty to the database (unless
you add a TRIM function as part of your imput validation).

The solution is to kill two birds with one stone.
The NZ function does this. It can be used for both
character strings and numbers.

For character strings the "Where" condition
is
where TRIM(NZ(your_column, "")) <> ""

This does several things:
1. It replaces null values in your_column with
and "empty" (zero length) string.
2. It eliminates leading and trailing blanks,
If the column contains only blanks (one or more)
It ends up reducing the column to an empty string.
3. Therefore you only see columns where "your_column"
contains "visible" data.

Hope this helps.

 

[ledmark]

Thank you both so much for your help. I'll give it a try.

Laura