Solved How to get around Null values in a form pulling from a Query

[Bean Machine]

Hi!

I have a form that searches my database for specific fields and returns what you have chosen from a combobox in the form of a query. I have been using this line of code and it has worked for almost everything and returned proper values: Like "*" & [Forms]![Database Search]![cbx_Item] & "*". This line of code is in the query portion and connects it to what is typed in the database search form. The only issue with this is the fact that null values will not function properly. There are certain fields that are suggested to be filled but not necessary and these are the fields that are causing me problems. Effectively I have a button tied to running a query and when clicked it is supposed to show you data associated with what you had input in the combobox (so if someone wants to search by name they can type in a name and run the query). I hope this is enough information. Thanks in advance for any help offered!

 

[vba_php]

Like "*" & [Forms]![Database Search]![cbx_Item] & "*". This line of code is in the query portion and connects it to what is typed in the database search form. The only issue with this is the fact that null values will not function properly. There are certain fields that are suggested to be filled but not necessary and these are the fields that are causing me problems.

this can be solved, most of the time by doing something like this:

like "*" & [Forms]![Database Search]![cbx_Item] & "*" OR  [Forms]![Database Search]![cbx_Item] IS NULL

I believe there is even another method available to produce even different results than that gives you, but I can't remember what this is at the moment. and my FAQ here on the forum from years ago doesn't cover that other method either.

 

[Bean Machine]

You are an absolute gentleman and a scholar. I have been trying to useso many different methods and the IsNull() function for the longest time now to no avail. I didn't realize how cut and dry it was. Thank you ever so much!

 

[The_Doc_Man]

Bean Machine,

The only question is this: How do the null values get there? If a user can enter data into a form but could skip over a field, provide a default value of either zero (for numbers) or a zero-length string (for text) or pick an impossible date (for dates.) I always picked 1-Jan-1900 for the "impossible" date because internally it isn't zero (or null) but is easy to test.

If this data gets imported from another source then when you do the import, you might wish to "condition" the data set by doing the import, running update queries on things that typically turn up as problems later, and then proceeding. Or run a query directly against your main database tables to eliminate the nulls.

The problem with nulls in the DB is that you can't recover them by comparison because nothing ever matches a null (including another null). That is why there is a specific ISNULL() function and that is why there is a syntax that Adam showed you for IS NULL. However, you say you have trouble. Can you be more specific? Like... got an error message? If so which one? Or... don't get all the records you want? Condition your data. Or... some other aberrant behavior? Please specify.

 

[vba_php]

no problem. =) for future reference Bean, isNull(), isEmpty() and IS NULL are all very good and you can use them in other things you do.

 

[Bean Machine]

no problem. =) for future reference Bean, isNull(), isEmpty() and IS NULL are all very good and you can use them in other things you do.

Actually another quick question if you wouldn't mind! I have multiple fields that can be null when running the query, there is also a combobox for the staff member who signed out an item (this field is usually going to remain null as items are typically signed out by students). After adding that line of code to the Staff Sign Out field it appears I've run into some problems as it results in all results being shown no matter which staff member I decide to choose. I don't know if this was descriptive enough for you but I'm very confused. Thanks again for your gracious amount of help!

 

[vba_php]

Actually another quick question if you wouldn't mind! I have multiple fields that can be null when running the query, there is also a combobox for the staff member who signed out an item (this field is usually going to remain null as items are typically signed out by students). After adding that line of code to the Staff Sign Out field it appears I've run into some problems as it results in all results being shown no matter which staff member I decide to choose. I don't know if this was descriptive enough for you but I'm very confused. Thanks again for your gracious amount of help!

this is what I was talking about when I said this:

I believe there is even another method available to produce even different results than that gives you, but I can't remember what this is at the moment. and my FAQ here on the forum from years ago doesn't cover that other method either.

I don't believe that FAQ of mine is still on this board but I do remember writing it years ago. if you want to upload what you've got and send a description of the problem, someone will surely take a look for you. I would point you to the search I just ran, but Jon has the search URL's set up in such a way that they expire after ''x'' minutes I believe. I've seen it used before. If you want to try though, give it a go. here's the url:

https://www.access-programmers.co.u...[nodes][1]=63&c[users]=ajetrumpet&o=relevance

if it is indeed on a timeout script, it will work for some time yet. the FAQ where I got the intial answer I posted for you was in this thread: https://www.access-programmers.co.u...y-by-combo-box-query-by-form-controls.183254/