Query Criteria with blank and non-blank text records (1 Viewer)

rcurtice

New member
Local time
Today, 12:02
Joined
Mar 13, 2007
Messages
3
OK, I have been searching around on the fourm for over an hour now...I give up. If this is some where else, I am sorry.

I have a database of maintenance data. There are several columns that are usually filled in, some records have some columns blank. They are formated text because they hold letters and numbers (see pic).

I am using a form to query the table...no problem. The form has text boxes the user filter down the data

The problem comes are with the results of the query. Any record that has a blank column is not retuned. I am using "Like" so that the user can enter in partial codes. I know "like" won't return "null" records.... Help!

Things I have tried:
1)IIF(form field is blank, return table field, else use like command to filter) - returns nothing!
2)Like "*" & [Forms]![Fleetwide_data_Request]![MAL_CD] & "*" returns all records without blanks (i.e. missing data)

what else can I do?


Thanks
 

Attachments

  • access.JPG
    access.JPG
    30.6 KB · Views: 1,052
unfortuately that didn't work, I got all records that are not blank. I need all records that are blank and not blank while the form field is empty.
 
I even tried replacing all the blanks with "NA" but those won't even show up... what am I doing wrong?
 
try using this as a criteria in the query field you are selecting:

<>""

(Means not equal to nothing) displays all nonblank records. If you use ="" then it will display all records.
 
try using this as a criteria in the query field you are selecting:

<>""

(Means not equal to nothing) displays all nonblank records. If you use ="" then it will display all records.
Not the whole story.

A blank text field may be a zero length string i.e "" or it may be a Null. So you need to cover both options. blah, blah OR Nz([Forms]![myForm]![myControl],"") = ""
 

Users who are viewing this thread

Back
Top Bottom