I need to set up a query that omits fields if NULL or empty..and thus far havent been able to do so or figure it out....youre help is greatly appreciated

thanks,

John

Have you tried IsNull ?

In your query criteria you could put something like:

If IsNull(FieldName) = False

This would only show fields which were Not Null.

Sfez

In query design view, type:

Is Not Null

In the criteria section under the field you want to check.

This will find only records which have data in that field.

What data type is the field?

the data is a hyperlink to a picture on my server

The answer to you question (in both posts) is NO

If a field is empty then the "Caption"(heading) will still appear in a query.

In a "single form" you can hide the control (visible=false) if it is "empty"

I think you could create a query with code that only selected fields that were "empty" but why you would want to I have no idea.

See my attached database, look at the queries and then open the form
cycle through the records and you will see that the FAX do not appear in all of them.


Hope this was of some help



John A

PS maybe you could explain what you are trying to do?

I would love to see your example but it is telling me that it is read only and when I check the properties it doesnt seem to be??? I am running access xp

If I understand what it is you are trying to do correctly, I think that what you are looking for is an SQL statement that excludes empty fields, correct?

In that case, you would use:

SELECT tbl.*
FROM tbl
WHERE [field_name] IS NOT NULL;

Give that a try and let me know if it works.

The sample was written in access 97, when you try and open it, you will be asked to either open it or convert it . The answer is up to you. If you just want to view it then just open it ( you will get a warning that it is read only) or you can convert it to xp.

The choice makes not difference, you would still be able to view. I do not have access xp, but i do have access 2000 and I both opened and converted it with this program.


John A


I have attached another copy saved in access 2000