Multi Field Search Criteria Code with Null Fields (1 Viewer)

Jefftosh

New member
Local time
Today, 11:59
Joined
Jul 19, 2022
Messages
8
Hi Folks ,

I will say right away that I am not familiar with Access other than knowing what it is.
I have picked up a bit, googling, and will do my best to use the correct terminology.
My friend runs a cemetery. He has been using Paradox from Corel to manage around 15000 records of current and future residents. His drive with said software was failing but i did manage to open Paradox and export the data in every format it offered (III, IV, V and .CSV) before it completely crapped out. The only database software he has in hand is a 2010 version of Office with Access. Access accepted the .csv and now I have the table in Access. He has to be able to search almost every field and combine search these fields as well. I made a form and a corresponding query. For the criteria I watched a video from 2011 using Access 2010 and straight up copied the criteria he used in his query to combine search.
Code:
Like "*" & [Forms]![SearchForm]![FirstName] & "*"
If there is a field in the table that is empty in a record, the query does not return that record.
So if I search by FirstName it only returns the records that have entries in all the fields not just FirstName.
In his video all of his fields in the table had a value in them. Mine do not. Some fields like YearOfDeath are empty.
Could someone suggest a bit of code that might work when some of the fields are empty or perhaps a better solution? My googling in this dept. frankly has not taken me far.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:59
Joined
May 7, 2009
Messages
19,175
you are missing some "quote" character in your Expression, should be:

Like "'*" & [Forms]![SearchForm]![FirstName] & "*'"
 

Jefftosh

New member
Local time
Today, 11:59
Joined
Jul 19, 2022
Messages
8
you are missing some "quote" character in your Expression, should be:

Like "'*" & [Forms]![SearchForm]![FirstName] & "*'"
I tried this just now and unfortunately the query doesn't work at all with the apostrophes. Were they supposed to be Quotation marks? Edit: No that didn't work either. I don't get a syntax error doing as you suggested but the query searching any field returns no records at all.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:59
Joined
Feb 28, 2001
Messages
27,005
No, they weren't. the idea is that you enclose a string in (double) quotes - but then, if you have to quote something inside THAT string, you use apostrophes - which ASCII standards have overlapped with single quotes. Can we see the whole query?

As to "multi-field" queries, take a look in the "Similar Threads" section below for the title that starts with SQL. You might have to dig around to follow threads introduced in that thread, but it has been done.

The method that many of us use is to test whether the control on the form is empty, in which case we DON'T search on it. Which means we build a dynamic query with a variable number of elements in the WHERE clause. There is a lot more to it than that, but you might find some of it by using the SEARCH facility for "multi-field searches."
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:59
Joined
May 7, 2009
Messages
19,175
here is a demo. see Query1.
 

Attachments

  • search.accdb
    584 KB · Views: 113

Jefftosh

New member
Local time
Today, 11:59
Joined
Jul 19, 2022
Messages
8
No, they weren't. the idea is that you enclose a string in (double) quotes - but then, if you have to quote something inside THAT string, you use apostrophes - which ASCII standards have overlapped with single quotes. Can we see the whole query?

As to "multi-field" queries, take a look in the "Similar Threads" section below for the title that starts with SQL. You might have to dig around to follow threads introduced in that thread, but it has been done.

The method that many of us use is to test whether the control on the form is empty, in which case we DON'T search on it. Which means we build a dynamic query with a variable number of elements in the WHERE clause. There is a lot more to it than that, but you might find some of it by using the SEARCH facility for "multi-field searches."
Thank you for the hints. I will read the SQL link you suggested.
 

Jefftosh

New member
Local time
Today, 11:59
Joined
Jul 19, 2022
Messages
8
here is a demo. see Query1.
Thank you! Wow. You whipped that up in minutes! So I see you have used Or. I have around 10 columns(Fields?) that can be searched. I will try using OR for the other entries and see how that goes :)(y) Edit: I'll see if I can put together a mini version of what I'm doing so you can see what I'm very likely explaining poorly.
 
Last edited:

Jefftosh

New member
Local time
Today, 11:59
Joined
Jul 19, 2022
Messages
8
So the idea was to be able to enter in one or several of the fields in the form and get back all records that match in the table. If you search on the form for Smith you will see that only 2 of them show up and the 2 that are missing do not have the Spouse Name field filled in. A client may come in and say "Is block A lot 15 on the east side taken? So we would search only those 3 fields on the form and get a specific record. It was kind of working this way except if any of those fields were missing in a record, it would not be included in the query results.
 

Attachments

  • Test.accdb
    416 KB · Views: 113

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:59
Joined
May 7, 2009
Messages
19,175
see Query1 first then your Original query.
 

Attachments

  • Test2.accdb
    1.2 MB · Views: 123

Jefftosh

New member
Local time
Today, 11:59
Joined
Jul 19, 2022
Messages
8
see Query1 first then your Original query.
Good morning! Thank you so much for your efforts on my behalf. Your code works. I am completely ignorant in SQL but I think I understand enough of what you did to try and apply this idea to the main database. You sir are a true champ and I can't thank you enough for your prompt and accurate replies.
 

Jefftosh

New member
Local time
Today, 11:59
Joined
Jul 19, 2022
Messages
8
I got it working! Just hafta make it pretty. There is no way I could have completed this in a timely fashion without the awesome help I received here. At 3AM no less! You guys rock!!!
 

Users who are viewing this thread

Top Bottom