qurey and null values

[Ydrasil72683]

Hello

Looking to get some help with a query I am doing I am using Like "*" & [forms]![myform]![myformfield] & "*" Or Is Null in my Criteria field of the query, to try and get the form to display records that have blank fields but it will only display fields that have all the fields filled out. Is it possible to use criteria to force it to display the records with blank fields ? Any and all help is appericated.

 

[pr2-eugin]

I think you have all criteria under AND logic, but if you want it to work for different fields you have to change your AND to OR.

But this might not sort your problem, could be helpful if we could see the Query you are working with.

 

[Ydrasil72683]

This what your looking for ?

 

[pr2-eugin]

Kinda, but in the next line.. Check the attachment..

 

[Ydrasil72683]

Okay that brings up all the data but it doesn't sort it by what I put in the form. Any ideas ?

 

[boblarson]

Okay that brings up all the data but it doesn't sort it by what I put in the form. Any ideas ?

What do you mean by "it doesn't sort it by what I put in the form?" I see no sort order applied to that query at all. So, how do you think it would be sorting?

 

[Ydrasil72683]

Well what should be happening is the user enters data into the form, then this data is used by the query to sort the data and display the search results. Instead of doing this it is displaying all the data

 

[boblarson]

Well what should be happening is the user enters data into the form, then this data is used by the query to sort the data and display the search results. Instead of doing this it is displaying all the data

You are misunderstanding what your form is doing then. The form that you have only is providing the CRITERIA of the query. There is no sort order being applied. And, there won't be unless you set it manually OR you use a querydef to change the sort order. If the query being opened is being used as a recordset for a form, you can get by without using a querydef but you would need to set the form's sort order. The code to set the Form's sort order would be something like:

Forms!YourFormNameHere.OrderBy = "[FieldNameHere], [Field2NameHere] Desc"
Forms!YourFormNameHere.OrderByOn = True

 

[Ydrasil72683]

I think I should explain this better when somebody inserts say "K" for the Block on the form, the query should display all the lockers in K block, or for example if I put in 1001 in locker Number I should get the record that has 1001 has a locker number. but instead of that I am getting all the records in the table. I am not sorting the fields, I am trying to sort the records by the criteria that I place in the form.
So would the above code still work with that and if so where would I place said code ?again ty for any and all help

 

[vbaInet]

It should have been:

Like "*" & [forms]![myform]![myformfield] & "*" Or [COLOR=Red][B][forms]![myform]![myformfield][/B][/COLOR] Is Null

 

[Ydrasil72683]

Unforunately that didnt work anybody else have some ideas

 

[vbaInet]

In that case your fields don't contain Null, they are zero-length strings or your table is inner joined to another table that's restricting it from displaying Null values.

Upload a stripped down version of your db so we can see how it's set up.

 

[boblarson]

Just to give you an idea of what I'm currently working on - a search form and setting the Order of data (the search form's final physical layout isn't complete and there are a few things in it to make it easier for me while creating it, but here's a bit of the form and some of the code to build the where clause for opening the report. You could do it similarly which then, if you don't try to do it all in your single query, it can actually be easier, even though it doesn't look like it):