Query Based Form: How do I ignore blank records?

[DesertTrip]

I created a Maintenance FORM, that allows users to update and correct data they already added to the data base.

I did this by creating a simple query, and then creating a form off that query.

How can I get the Maintenance Form to show only records that have data in certain (relevant) fields of the records?

Is this done in a criteria on the Query? I tried Like* to bring up only items with entries but it also brngs up zeros. would like this to work off of a field consisting of fixed numbers GREATER than zero.

Is this done maybe on the Form itself in design mode, properties?

 

[BVerlaan]

The criterum (in your query) Is Not Null should do the job.

A Null means: points to nowhere. A Null is not the same as an empty string, being "".

Good luck, Bart Verlaan.

 

[DesertTrip]

Thanks for the answer, but that didn't work there and now I see I am going to have to switch gears because the placement of criteria in my expression field is causing a parameter request to pop up.

So.....I am switching fields to bring up only records with text of any type. Typing Is Not Null in the criteria cell of neither fields worked.

Any other suggestions? help?

DT

 

[BVerlaan]

What type is the field where you add a criterium? Is it of type Integer or of another numeric type? If so, a criterium Like "* . . ." won't work. This one only works for fields of type alphanumeric (text). Try the criterium >0

Another thing I'm thinking of is: is more than one table part of your query? if so, use inner joins and no left or right joins.

By the way, what's the recordsource of your form. The query you're referring to? If so, my experience is that I myself understand better what's happening if I add criteria to the query and let the form only present the records the query selected. Working like this you can always test the query first.

Hope you're successful with these hints.

Greetings, Bart V.

 

[DesertTrip]

The query is based off of one main table.

The field itself is from a lookup table used in the main table.

The "data type" for this field is text, but the mask is ##\-### (5 numerals separated by a dash)

Some records don't have any entries in this field, and my query and form are only to assist with those that do.

I hope that helps.

 

[BVerlaan]

Do you mean that you have a calculated field in your query (being the recordsource of your form)? And that you try to add a criterium to this calculated field?
(I don't understand what you mean with 'The field itself is from a lookup table used in the main table')

If so, I'm afraid that the thing you want is impossible. Acctually, what I mean is: I've never been successful in adding a criterium to a calculated field. So my conclusion was when I tried: impossible.

Perhaps another reader of this thread has better ideas?

Bart V.

 

[DesertTrip]

I figured it out this morning....I used Between 1 and 60000.

This eliminated all records that had no entry.

I was shocked because it is a text field, but this Criteria in the Query worked anyways.

In answer to the question, you must forgive me as I am not very familiar with the terms of the functions I have been doing.

In my main table, there is a field for PROJECTS. That field is a Look Up Table. It is the text one with the numbered mask of ##\-####.

In my query, I was originally trying to add a criteria to an Expression that summed times for the projects. That would not accept the IS NOT NULL criteria. I then tried it on the PROJECTS field itself and again, for some strange reason, it didn't work there either.

But, the Between function did. (*scratches head in confusion*)

Sorry that I couldn't have been more exact in describing what it was I was working with. Also, thanks for trying to find a way for making this work. If anyone can STILL figure out a criteria for a query that will work in ignoring blank records by using the Expression field instead, that would be even more accurate as sometimes, the users might enter data/times without entering the Project code.

DT