[X]My Assistant

[namron]

Hi
I'm having difficulties building a search form which I can't understand.

I'm just searching one table. I have a search form with 4 unbound text boxes named to match the fields in the table.
I have built a query and in the Criteria box for the fields I want to search I have for example:

Like "*" & [Forms]![SearchFirst]![FirstName] & "*" where the name of the search form is SearchFirst

This works fine with one field in the query and also when I add a second field to the query.

When I add a third field to the query I am no longer able to search on the first or second fields, but can search on the 3rd field!

I have spent hours on this, checking and rechecking the code and it all seems correct.

Any ideas what could be causing this?

Thanks
Norman

[DanielPineault]

We'd need to see your complete code/SQL Statement or a sample of your db.




You should review https://www.devhut.net/2016/06/10/ms-access...-is-left-empty/




Also, I don't know if this will help you, but have you seen http://allenbrowne.com/AppFindAsUType.html

[RJD]

Hi Norman: Just a wild guess, but are you using an = criteria on the third field, rather than Like?

But as Daniel requested, we'll need to see your SQL to help you diagnose this (and possibly your table as well)... There are alternative ways to write the criteria, based on what your query and table look like.

HTH
Joe

[GroverParkGeorge]

Here's another sample accdb on my website with multiple searches that can be completed with " AND" or with " OR " between one or more filter fields based on controls on the form. See if it helps illustrate some of the possibilities.

[namron]

Thanks for the responses, I'll have a look at the suggested links to further my understanding and look at further possibilities.

As far as my original problem is concerned, I just wanted to check my method is correct so I've now got a sample db with just one table and a search form.

The good new is that my search works (almost) fine using 5 possible fields to search on. The thing that's baffling me now is that it isn't returning any results for records entered after a certain point!

If I add new records into the table they also come up as 'not found'? However those records can be found using a simple query or the built in search.

So could anybody explain why my search form won't find new records? Copy sample db attached, you'll see that the last 3 records in the table don't come up in the search using the search form.

Thanks a lot

Norman

[projecttoday]

There is no attachment.

[namron]

Apologies, file now attached

 ALM_Database_Sample.zip ( 55.65K )Number of downloads: 25

[DanielPineault]

It's because you've set up your search criteria as "AND" so they all have to be present. My original link demonstrates the way to handle this and also avoid the use of LIKE *...* which should be avoided. Please take the time to review the link provided above. Also, you should switch the First Name and Other Names to combo boxes, perhaps the Ref controls as well.

[projecttoday]

This database has names and addresses in it. Not good.

[GroverParkGeorge]

The link to my demo DOES offer both types of searches. You can toggle between " AND " searches and " OR " searches.

Take a look. It can't hurt to see if it helps.

[namron]

Hi

Sorry, haven't submitted a file before, the data is actually fictitious though.

Thanks Daniel for your reply, I will review your previous link, although I'm not sure why using 'AND' is a problem when usually only searching one field at a time and the ones not searched will produce a blank. Why would it work with some records and not later ones?

I'd thought of using combo boxes on the search form but thought it may not be a good idea as there will eventually be 8000+ records, so the combo boxes would be very long.

[GroverParkGeorge]

It DOESN'T work if there are conflicting searches, i.e. put the first name of any individual in your table into that control and the Other name of a different individual in that control. It will NOT return either because there is no one whose name matches.

I don't know if there is a problem getting to the sample database on my site, but I really did put in a toggle that allows you search both ways, using " AND" as well as using " OR ". You have to change the syntax depending on which is chosen.

[projecttoday]

the data is actually fictitious though.

Good job!

[namron]

Thanks George, yes I can see that. I'll check out your sample db when I get some time.

Daniel, I've now reviewed your link. Sorry if I seem a bit dim, should the statement "=Forms!YourFormName.YourComboBoxName OR Forms!YourFormName.YourComboBoxName IS NULL" be entered into the 'criteria' section of the Query Builder in full or should the first part go in 'criteria' and the second part in the 'or' row below?

I did it the first way and now when I open my query in design view it looks very strange. Having said that the query/search now works correctly for all records! Thank you.

[namron]

I meant to attach a screenshot of the query design view. Now attached.

 Query_screenshot.JPG ( 170.73K )Number of downloads: 22

[namron]

OK, I now have my search form working, thanks to the method suggested by Daniel. It does produce an absolutely enormous SQL statement, though maybe I'm not entering the criteria in the Query Builder correctly? But at least it works.

I've also tried to adopt Daniel's suggestion of using combo boxes on the search form instead of text boxes, but when I use them the search doesn't find any records?

Any further guidance on how to get the search form to work using combo boxes would be welcome.

I've had a look at George's sample database. Unfortunately I'm still fairly inexperienced and can't follow how it works or how I would adapt it.

Thanks for the suggestions provided so far.

[RJD]

Hi: Here's another way to write the criteria where Access will not "help" you by rearranging the WHERE clause, making it nearly impossible to decipher ...

CODE

SELECT tblClients.FirstName, tblClients.OtherNames, tblClients.DOB, tblClients.NASSRef, tblClients.PortRef
FROM tblClients
WHERE
FirstName=Nz([Forms]![frmSearch]![FirstName],[FirstName]) AND
OtherNames=Nz([Forms]![frmSearch]![OtherNames],[OtherNames]) AND
DOB=Nz([Forms]![frmSearch]![DOB],[DOB]) AND
NASSRef=Nz([Forms]![frmSearch]![NASSRef],[NASSRef]) AND
PortRef=Nz([Forms]![frmSearch]![PortRef],[PortRef]);

Note that it is NOT a good idea to name the control on the form the same name as the field to which you want to compare. I suggest something like txtFirstName as a name for the control, for example.

As far as the comboboxes, we'll have to see the db to see what you have done. I suspect, however, that when you created the combobox row source, you used an ID (or some other field) to start, even though you might only see in the combobox the field value (e.g a name) you want to select on. But, as I said, we'd have to see how you built the combobox to figure that out and offer a solution.

HTH
Joe

[namron]

Hi Joe

I've replaced the existing SQL in the query with the code you provided and it works great, thanks so much.

I've changed all the control names, prefixing them with txt.

I've also tried deleting the FirstName text box and replacing it with a combo box called cboFirstName. I've amended the SQL statement relating to that control to:
(((tblClients.FirstName)=Nz([Forms]![frmSearch]![cboFirstName],[FirstName])

I created the combo box using the wizard and it's unbound, it's Row Source is shown as SELECT [tblClients].[ALM_ID], [tblClients].[FirstName] FROM tblClients ORDER BY [FirstName];

Running a search using the combo box produces no results.

Is this enough information for you to see where I'm going wrong with the combo box?

Thanks a lot.
Norman

[namron]

Hi

I've now sorted the combo box issue. I re-read Joe's previous reply and changed the 'bound' column in the combo box control to 2 instead of 1 and it now works!

Thanks again for everybody's guidance. I can always rely on this forum to help me when I get stuck!

Cheers

Norman

[projecttoday]

Glad you got it working.

Can you recap as to what distinguishes this from other search forms? Maybe you could post the corrected version.