Solved Conditionally Omitting Blanks

[NavyBlueBolt]

I'm working with a query set to be a search engine of sorts. The search form has multiple unbound text fields (for partial searches) that send to the query in question and back onto a subform on the main form., I need to show blanks when all the fields are null and omit blanks when one of the fields are not null. Currently, any searches or null searches include blanks. I had tried the choose and iff functions, but I get the "expressions are too complex" dialogue box. Any suggestions on how to get the search engine I'm looking for?






I'm off for the weekend. stay safe

 

[The_Doc_Man]

I'm almost afraid to ask, but can you switch that to SQL VIEW and show us what gets built?

Not only that, but I'm not sure I understand the goal of the query anyway. You only expanded one column and everything in that column looks the same to me, so I don't know what you are trying to accomplish. I can't tell the difference between any of the rows that have text in them other than a couple of design rows that have blanks under Status_Activity.

 

[plog]

Don't do it that way. Check out the form filter property:

Form.Filter property (Access)

Office VBA reference topic

docs.microsoft.com

I'm guessing the query feeds data on your form. What you do is strip out all the criteria from your query and instead filter the form to show just the results you want. That way, you can dynamically build the filter string using just the criteria submitted. In the query you have to account for all criteria/null combinations. With the form.filter you just use the ones they want.

 

[arnelgp]

you only write the Criteria "once" and not every row (if you have same criteria).

 

[Pat Hartman]

If you are going to use Like * .. **, then you don't need to include OR is null for each condition. That will simplify the string.

Given that most of my apps use ODBC BE's, I never use filters for main forms. When i have a complex search form, I build the Where clause based on the options selected on the form. Most will be combos so they would always use the = operator. Only the partial string criteria would use LIKE.

Once the where clause is built, I use a dcount on the query. If only one record is returned, I open the main edit form. If multiple rows are returned, I open an intermediate list form where the user can double click on a row to open the main edit form.

Although using the Access filter technique isn't wrong, it is not efficient when the BE is ODBC. When your data is on the server, you want the database engine to do the heavy lifting. You do not want Access to suck down every row in the table and filter it locally. This is one of the major reasons that people who just convert Jet/ACE BE's to SQL Server are surprised by just how slow the app has become. So, rather than use two techniques, I use the strong technique and that is why I can convert any database I build from Jet/ACE to SQL Server in an afternoon with no changes. The time is all for testing rather than coding.

 

[NavyBlueBolt]

Oh, where to begin. I'm learning vba and access through the University of Youtube so be prepared if much of what I do doesn't make sense or seems redundant. I'm doing my best just to make this function. I haven't even begin to touch SQL.

When the user opens the search form, there is a subform for the query that I am working on. The text fields are loaded null so that all possible results will show (being that it passes two field criteria: latest entry and valid entry). The way it is currently made, you type in what you want to search and the Like function does the rest. The issue I'm bumping into is when searching, I don't want blanks for that entered field to show. Example: If I type Johnson in the Calibrated By field, I want only Johnson and Like Johnson to appear without nulls for Calibrated By. I only want nulls to show with all other records if the related text field is null. I'm sorry if this isn't clear.

@The_Doc_Man Attached is the SQL code for that query.

@plog I'll look into the filter proper when I get a free moment. It looks useful regardless of how this get solved so thank you for bringing it to my attention.

@arnelgp I tried cleaning that up to a single row, but it didn't run properly when I tried. There are some fields near the end that have criteria that are placed in different rows so I think that stopped it from working for me.

@Pat Hartman My boss stressed that I not use combo boxes (though it pains me to do so for the ID in the least). "It affects usability and disincentives the end user from using the program." There is no BE file for this; it's just the one database that holds everything sitting on a server that I have no control over. It wasn't until later that I learned how to grab data from other databases/files. That's when I made a backup database that imports the data from the working database. All done with vba. I'll need some time to figure out how you did your process to even respond to the rest of your advice. It's alot for me right now.

Thank you everyone for being so helpful and patient with me.

 

[Pat Hartman]

I don't want blanks for that entered field to show. Example: If I type Johnson in the Calibrated By field, I want only Johnson and Like Johnson to appear without nulls for Calibrated By. I only want nulls to show with all other records if the related text field is null. I'm sorry if this isn't clear.

Does not compute I think you are going to have to give us a set of data and show us what you mean.

 

[NavyBlueBolt]

Does not compute I think you are going to have to give us a set of data and show us what you mean.

I need all records to show, nulls included when the matching unbound textbox is null. Knowing that, how do you omit null values in a specific field when the correlating text field is not null?

 

[Pat Hartman]

Does not compute
A query returns x rows and y columns. The selection criteria controls the number of rows that will be returned but the selected columns are fixed. I don't know what "omit null values in a specific field" means. Are you saying that you don't the column to be returned in the query?

 

[NavyBlueBolt]

Does not compute
A query returns x rows and y columns. The selection criteria controls the number of rows that will be returned but the selected columns are fixed. I don't know what "omit null values in a specific field" means. Are you saying that you don't the column to be returned in the query?

I do want the column. I want the criteria to read as:
iif( [calibrated_by_txtbx] <> null, like "*" & [calibrated_by_txtbx] & "*", like "*" & [calibrated_by_txtbx] & "*" or is null)
I tried putting something like that in, but to no avail.

 

[Minty]

I am confused as well as @Pat Hartman but I think you are trying to do this;

like "*" & [calibrated_by_txtbx] & "*" or [calibrated_by_txtbx] is null

To include everything if there is nothing selected in the combo box.

Edit: And if the combo's only list actual values you can get rid of the Like "*" wildcards completely as you will only be searching for exact values?

 

[NavyBlueBolt]

I am confused as well as @Pat Hartman but I think you are trying to do this;

like "*" & [calibrated_by_txtbx] & "*" or [calibrated_by_txtbx] is null

To include everything if there is nothing selected in the combo box.

Edit: And if the combo's only list actual values you can get rid of the Like "*" wildcards completely as you will only be searching for exact values?

That's about what I'm looking for! I got the combo box for ID's set. I can apply this to a text box. The "is null" is to include null values in the search, right? Wouldn't this line just show everything regardless of what's in the box?

 

[Minty]

That's about what I'm looking for! I got the combo box for ID's set. I can apply this to a text box. The "is null" is to include null values in the search, right? Wouldn't this line just show everything regardless of what's in the box?

No - It only applies when the combo IS Null. That evaluates to True so the criteria are ignored if null or applied if not.
That was also the reason for my suggestion of removing the Like parts of the criteria statements. If the combos can only show real data values, then the Like portion becomes redundant.

This will also speed up how it runs. Wildcards at the beginning of criteria render the index useless, slowing down searching.

 

[NavyBlueBolt]

No - It only applies when the combo IS Null. That evaluates to True so the criteria are ignored if null or applied if not.
That was also the reason for my suggestion of removing the Like parts of the criteria statements. If the combos can only show real data values, then the Like portion becomes redundant.

This will also speed up how it runs. Wildcards at the beginning of criteria render the index useless, slowing down searching.

I plugged it in and it works. Thank you so much for your help.

May the deities of the programming bless your codes. .

 

[Pat Hartman]

By way of explanation:

Comparing null to any other value returns null so
If fieldA <> FieldB will return null (assumed by access to be False) when one of the two values is Null. That's why your original attempt of:
[calibrated_by_txtbx] <> null
didn't work.
If fieldA = FieldB will return false even if both fields are null.

When you want to test a field for null, use the IsNull() function in VBA and use the Is Null predicate in SQL.
If IsNull(FieldaA) Then
OR
Where FieldA Is Null

Also keep in mind that "" which is a Zero Length String (ZLS) is not null so using either IsNull() or "Is Null" on a field that contains a ZLS will return false. To avoid this issue completely, I always set the AllowZLS property to false when I define a text field in a table. This used to be the default but now it is not so be careful. That means when I look at the MiddleName field and see nothing, I know it is Null rather than "blank". It also means that I can never store a bland LastName. LastName is required and since ZLS is a valid character, if you allow ZLS, then you can store "blanks" in required fields which is really poor practice. Excel treats them the same but they are different characters and so relational databases treat them differently.

ZLS are not special characters so they work the same way that other values do in compares and you don't need to create special code to handle them.

Merry Christmas. Just killing time until the pies come out of the oven.