Yet another issue with LIKE OR IS NULL (1 Viewer)

[GrandMasterTuck]

[SOLVED] Yet another issue with LIKE OR IS NULL

Hi everybody. I had a strange issue dealing with a search form I was trying to create, you can read about that here if you want:
http://www.access-programmers.co.uk/forums/showthread.php?t=276166

The issue was solved, but I've discovered that there are two problems with the solution I got.

1. The query, once written, cannot be re-opened, or Access freezes and crashes. I kept deleting it and recreating it (no small amount of work), and again, it works PERFECT, but I can't view it in Design or SQL view because it crashes. I tried to create another new one but with fewer fields, and THAT one opens, but there are literally HUNDREDS of rows (not columns) in the query, with all possible combinations of the code across a row. I have like ten columns in this table I'm querying, and I end up with every possible combination of that code, sometimes with the LIKE statement and other times with the NULL statement. For instance, in the first row, I have the LIKE part of the statement in the first column, and the IS NULL part in the second, third, fourth and so on. Then in the second row, I have the LIKE statement in the first AND the second, then the NULLS in the rest, and it goes on like that for THOUSANDS of rows. If I modify ANYTHING, Access freezes and then crashes, or gives me a QUERY TOO COMPLEX error.

2. The disappearing records issue crops up again if I try to link any tables together in the query. For instance, I have a CUSTOMER table, and an ADDRESS table (wherein each one customer may have several addresses on record) linked one to many. If I put fields from both tables in the query, records that showed up with the single table query stop showing up in the two-table query, and I think it's because that particular customer has no related records in the other table (no addresses on file).

This issue is a royal pain in the neck for me. Unfortunately I get like one day every like two months to look at this, and each time, I smash head first into this stupid search screen.

Is there a more simple way to create a frigging search screen, wherein the user can type anything they want into any of the fields that appear in any of the linked tables, and have the system return records that match EVERYTHING the user typed, but at the same time, if the user leaves a particular field blank, it returns everybody? What the heck am I doing wrong?

Let me know if you guys want to see the database (modified to remove any identifying info, of course). It's making me nuts, and I can't figure out why it won't work... Thanks again!

 

[plog]

Big picture: What are you trying to accomplish?

User opens a from, types in some fields and then what? What object are you trying to open or filter? The query itself? A Sub-form? A Report? What do you want to display the results in?

 

[GrandMasterTuck]

The search form has a subform that's showing a list of the Customer names that match the search results. The subform's CustName display field is clickable, and allows the user to open that customer's VIEW form.

I open the Search field and the subform shows me ALL customers in alphabetical order. I can then go to the search boxes, type something, and as soon as I tab out or hit ENTER, an EVENT fires that refreshes the subform's list, effectively narrowing down the search results. I can then either type some more into that or another search box to narrow further, or I can click a customer name in the subform to be taken to that customer's page.

All of this is done and working. It's just that I can't get that subform's list to work right. I'm either missing names when I open the form (because the query is ignoring records without related records) or no records display at all, or when I type, it's not narrowing the search. This search needs to be across multiple tables that are linked one-to-many with tblCustomers.

 

[jdraw]

Have you read through this tutorial?

 

[GrandMasterTuck]

I have not, jdraw. Didn't even know that existed. I shall review it now to see if I can find what I'm doing wrong. Thanks for that!

EDIT: I'll be damned. Right there:

For many reasons, the query approach is not really recommended:
You must declare your parameters, to avoid the problems described in this article: Calculated fields misinterpreted.
If you do declare parameters, you are likely to run into the bug described in this article: Parameter of type Text is evaluated wrongly.
The query will not read the value of a value in the control on your form until you tab out of the control (which updates its Value property.)
Bracketing of the ANDs and ORs is crucial (as explained above.)
It is less efficient to execute.
Access messes up the query if you switch to Design view:
Line-endings are lost.
Spurious brackets are added.
The ANDs and Ors in the WHERE clause is completely changed.
With the way Access changes the WHERE clause, you may run out of Criteria design rows.

No wonder this stupid query craps out on me. Guess it's back to the drawing board and trying it with code (which I'm not as good at...)

 

[jdraw]

For reference, you should have these links available when looking for Access examples and help:

Allen Browne
Martin Green
Datapig Videos

Good luck.

 

[GrandMasterTuck]

Okay... I got it to work with the main table, but I have no idea how to get this thing to work correctly with a second linked table. I have Customers and Orders. I tried to set the form to check the customers table for the search parameters, but when I tried to get the form to include some additional unbound fields for the columns in the associated table, it didn't work. So I tried hooking it to a simple query instead, in which my two tables were configured in that one to many relationship, and I'm right back to square one: I get multiple results for one Customer if they have more than one order, and I get ZERO results (the customer doesn't show up at all and cannot be found by searching) if they have no orders.

It seems like Allen Browne's code works great but only when you're dealing with ONE table, and I couldn't find anything on his site in reference to using this 'filter' trick with multiple tables that are linked.

EDIT: One problem solved... I changed the JOIN line in the query to list ALL records from the Customers table and ONLY records in the ADDRESS table where the records matched. This fixed the disappearing records (no Customer visible if he had no address) but I'm still getting the duplicate Customer records if the Customer has more than one address. Any ideas?

 

[plog]

What I heard is you are using a form to search for customers. If that's true, why do you need those other tables? Especially if you are LEFT JOINING them.

What data are you using from the other tables?

 

[jdraw]

I'm still getting the duplicate Customer records if the Customer has more than one address.

Can you show us:

a) a jpg of your relationships window,
b) the SQL of the query associated with the search?

Duplicates ==> exactly the same value, Is this what you mean?

 

[GrandMasterTuck]

Re: Yet another issue with LIKE OR IS NULL [SOLVED]

I know this thread is really old, but I wanted to post that I figured out how to get this query to work. Short answer: you can't get ONE query to do this for you, you have to write SEVERAL, one for each combination of the tables that may occur.

Okay *deep breath* I'll go into greater detail below (if you're interested):

THE NEED: I have a search page that has multiple fields you can type into. I want to be able to type values into the fields (or leave them blank) and then click a button and open a query that returns records to me that match everything I typed, but if I leave all the fields hooked to one particular table blank, I want to see ALL records for that table, as long as the records in other tables that these are attached to match my search parameters.

THE ISSUE: I could NOT get the query to fetch ALL records from a table if I left those fields blank for that table. For instance, if I have a PEOPLE table and a CARS table, and I put "JOHN" in the NAME field (referencing the PEOPLE.NAME field) and leave all the CARS fields blank, I want to see ALL people with JOHN in their name and ALL cars they have. Instead, I was only getting records where people named JOHN had cars, and anyone named JOHN with NO cars wasn't showing up.

THE SOLUTION: I had to write three queries: one for just people, one for just cars, and one for people and cars. The SEARCH button on my search form then performed an IF/THEN function to determine which of my search fields was left null, and if ALL search fields attached to a certain table were left null, the query that only looked at the OTHER table was used. So if I typed stuff in the fields hooked to the PEOPLE table but left the CARS fields blank, the query selected was the one with the PEOPLE table only. If I typed stuff in fields belonging to PEOPLE and in fields belonging to CARS, the query that looked at BOTH tables was used, and if I just typed in CARS fields and left PEOPLE fields blank, a third query that just looked at CARS came up.

It's inelegant and probably the long way around the problem, but it's solved either way, which has been a thorn in my side for months now. I figured out how to do it, so I figured I'd share, in case anyone else is as crazy as me to be willing to fix a stupid-weirdly programmed database for his boss like I was.

Thanks to everyone so much for all your help as I've stumbled through this problem.

 

[jdraw]

Can you post a copy of your database?

 

[gemma-the-husky]

what I would do is add a field to your query, consisting of the fields you want to search

nz(search1,"") & "-" & nz(search2,"") & "-" & nz(search3,"")

put this as a field on your form. Then either use the binoculars to search this field, or add a searchstring field to your form, and search the field in code for the searchstring

(effectively "coding" the binocular function)

 

[GrandMasterTuck]

Can you post a copy of your database?

Unfortunately that would be more trouble than it's worth. It has a LOT of data in it right now (like almost two gigs... i'm not joking... this thing has been in use a loooooooong time) and it's a split database with lots of VB code, so even if I were to copy the remote tables and paste them as local and then strip all the data out of them (to preserve the privacy of the employer's records), the database likely wouldn't work for you due to the strange security measures built into it. Seriously, this thing was built by some kid who had like 60% knowledge with Access (as opposed to the 90-99%ers I run into in this forum) and he did some really funky stuff with programming. It's been my job to 'clean it up' and get some broken functions to work correctly. One of the tasks was fixing this horrid search screen, and I finally figured out how to accomplish that with the help of everyone here.

Was there a particular question you had about the old system that I could answer for you? I apologize that I couldn't upload it, but believe me when I say it's just way too much of a hassle.

what I would do is add a field to your query, consisting of the fields you want to search

nz(search1,"") & "-" & nz(search2,"") & "-" & nz(search3,"")

put this as a field on your form. Then either use the binoculars to search this field, or add a searchstring field to your form, and search the field in code for the searchstring

(effectively "coding" the binocular function)

It sounds like you're saying to code this field that concantenates the values of all the other fields, and place it on the same form, and then search based upon that field? I never thought of doing that, and it's not a bad idea. Have you any links to a tutorial that demonstrates coding a search field that allows the user to separate search terms with a comma (for example) and the resulting function will search for the presence of all words in your search string regardless of position in relation to each other? That would be handy in several places, I think... Thanks!

 

[fvdb]

i use a query with the fields on the form , correct me if i'm wrong but you have a 3 search fields?

code i used in the criteria of the query

like "*" & formcontrol & "*"

if the control is blank then it not apply filter on that column

 

[GrandMasterTuck]

i use a query with the fields on the form , correct me if i'm wrong but you have a 3 search fields?

code i used in the criteria of the query

like "*" & formcontrol & "*"

if the control is blank then it not apply filter on that column

Yeah, that's the search criteria I use in the query for each field, and it works exactly as you said. But my issue was that when I have TWO tables in that query and I leave all the search fields that are linked to ONE of those tables blank, it returns NO records for that table instead of ALL records due to the fact that it's linked to another table.

 

[fvdb]

Yeah, that's the search criteria I use in the query for each field, and it works exactly as you said. But my issue was that when I have TWO tables in that query and I leave all the search fields that are linked to ONE of those tables blank, it returns NO records for that table instead of ALL records due to the fact that it's linked to another table.

Think there is a joining problem then because i have some querys with 3/4 tables and not have any issues about it. How the query reacts when you put yourself the criteria in?

 

[GrandMasterTuck]

Think there is a joining problem then because i have some querys with 3/4 tables and not have any issues about it. How the query reacts when you put yourself the criteria in?

Yep, probably a join issue. I tried several different types of joins in several other threads on this topic (check out my started threads history and you'll see how long I've been fighting with this).

Let's say I have a PEOPLE table, a CARS table and a PETS table, and all three are linked with one-to-many relationships (people can have more than one car and more than one pet, but pets and cars only have ONE owner).

If I want to search for people named John with cats, I put JOHN in the Name field on my search form, and I put CATS in the PetType field on the search form. The query I was using had all three tables linked, so if there was a 'John' in the table with a cat and two cars, I'd get two records (one for each car) and if there was a different John in the table with zero cars, he wouldn't show up at all. Because of the way the query was structured.

What I did was this, I have several queries: People only, Cars only, Pets only, People and Pets, People and Cars, Pets and Cars, People and Pets and Cars. (bare with me... I know it sounds complicated) When I click the SEARCH button, the button does the following:

1. If any people field is NOT null but ALL pets and ALL cars fields are null, use People query.
2. If any people field is NOT null AND any Pet field is NOT null but ALL Cars fields are null, use People and Pets.
3. If any people field is NOT null AND any Car field is NOT null but ALL Pets fields are null, use People and Cars.
4. If any people field is NOT null AND any Pet field is NOT null AND any Car field is NOT null, use People and Pets and Cars.
5. If ALL people fields are null but any Pet field is NOT null and ALL Cars fields are null, use Pets only.
6. If ALL people fields are null and ALL Pet fields are null but any Car field is NOT null, use cars only.
7. If ALL people fields are null but any Pet field is NOT null and any Car field is NOT null, use Pets and Cars.

So it's sort of a query overload, but it works perfectly now. I'm sure there's an easier way to accomplish this with a complicatedly written query, but I'm just not that good a coder. What I've done works for me, so I run with it.

And you know what's awesome [sarcasm]? I have FIVE tables linked to each other. That works out to be a crapton of queries, one for every possible combination of those tables. Yikes. But again... it works. So, whatever! haha

 

[fvdb]

At the end it worked and that's what we need. I'm also not a complicated coder a find a way to get somethings worked with more querys, espicially if i need a Max or one of that kinds one. SQL is not my thing so try to solve with queries ...

 

[gemma-the-husky]

Unfortunately that would be more trouble than it's worth. It has a LOT of data in it right now (like almost two gigs... i'm not joking... this thing has been in use a loooooooong time) and it's a split database with lots of VB code, so even if I were to copy the remote tables and paste them as local and then strip all the data out of them (to preserve the privacy of the employer's records), the database likely wouldn't work for you due to the strange security measures built into it. Seriously, this thing was built by some kid who had like 60% knowledge with Access (as opposed to the 90-99%ers I run into in this forum) and he did some really funky stuff with programming. It's been my job to 'clean it up' and get some broken functions to work correctly. One of the tasks was fixing this horrid search screen, and I finally figured out how to accomplish that with the help of everyone here.

Was there a particular question you had about the old system that I could answer for you? I apologize that I couldn't upload it, but believe me when I say it's just way too much of a hassle.


It sounds like you're saying to code this field that concantenates the values of all the other fields, and place it on the same form, and then search based upon that field? I never thought of doing that, and it's not a bad idea. Have you any links to a tutorial that demonstrates coding a search field that allows the user to separate search terms with a comma (for example) and the resulting function will search for the presence of all words in your search string regardless of position in relation to each other? That would be handy in several places, I think... Thanks!

bit in bold.

yes. I would add the concatenation to the query, which gets around the nulls problem without causing any complications.

the coding is a simple one line.

given a text control "mysearchstring", and buttons "find first" and "find next", and the concatenated bound control "searchfield", then enter the searchtext into control mysearchstring, and click a button

the findfirst is this

 searchfield.setfocus
 DoCmd.FindRecord mysearchstring, acAnywhere, False, acSearchAll, False, acCurrent, True

findnext is very similar

 searchfield.setfocus
 DoCmd.FindRecord mysearchstring, acAnywhere, False, acSearchAll, False, acCurrent, false

 

[GrandMasterTuck]

I like this solution, it seems simple, but I think it might not give me the result I'm looking for, because when users make use of this search form, they may very well get a list of results, all of which match the search criteria. For instance, using that vehicle example, a user might go in and search for color GREEN and make CHEVY, and they'll get a list of records that have a vehicle with GREEN as the color and CHEVY as the make, and they can print this list of records. I think your searchstring field example will only work if the result is a single record, right? Or it will return the very FIRST example in the table that matches the search results, and I'd have to have a searchNext button to go to the next matching record. But this won't work, because I need to be able to view all the records that match and output them to a report that uses that query as its data source.