UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Problems Building Search Form, Access 2016    
 
   
namron
post Mar 4 2018, 03:56 PM
Post#1



Posts: 79
Joined: 31-August 11
From: Lancashire,UK


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
Go to the top of the page
 
DanielPineault
post Mar 4 2018, 04:00 PM
Post#2


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



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

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
RJD
post Mar 4 2018, 04:22 PM
Post#3


UtterAccess VIP
Posts: 8,360
Joined: 25-October 10
From: Gulf South USA


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

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
GroverParkGeorge
post Mar 4 2018, 05:28 PM
Post#4


UA Admin
Posts: 32,821
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
Go to the top of the page
 
namron
post Mar 5 2018, 04:28 AM
Post#5



Posts: 79
Joined: 31-August 11
From: Lancashire,UK


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
Go to the top of the page
 
projecttoday
post Mar 5 2018, 06:50 AM
Post#6


UtterAccess VIP
Posts: 9,699
Joined: 10-February 04
From: South Charleston, WV


There is no attachment.


--------------------
Robert Crouser

My company's website
Go to the top of the page
 
namron
post Mar 5 2018, 07:24 AM
Post#7



Posts: 79
Joined: 31-August 11
From: Lancashire,UK


Apologies, file now attached

Attached File  ALM_Database_Sample.zip ( 55.65K )Number of downloads: 15
Go to the top of the page
 
DanielPineault
post Mar 5 2018, 07:31 AM
Post#8


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



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.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
projecttoday
post Mar 5 2018, 07:45 AM
Post#9


UtterAccess VIP
Posts: 9,699
Joined: 10-February 04
From: South Charleston, WV


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

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
GroverParkGeorge
post Mar 5 2018, 08:00 AM
Post#10


UA Admin
Posts: 32,821
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
Go to the top of the page
 
namron
post Mar 5 2018, 08:07 AM
Post#11



Posts: 79
Joined: 31-August 11
From: Lancashire,UK


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.
Go to the top of the page
 
GroverParkGeorge
post Mar 5 2018, 08:15 AM
Post#12


UA Admin
Posts: 32,821
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
Go to the top of the page
 
projecttoday
post Mar 5 2018, 08:19 AM
Post#13


UtterAccess VIP
Posts: 9,699
Joined: 10-February 04
From: South Charleston, WV


QUOTE
the data is actually fictitious though.


Good job!

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
namron
post Mar 5 2018, 09:37 AM
Post#14



Posts: 79
Joined: 31-August 11
From: Lancashire,UK


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.
Go to the top of the page
 
namron
post Mar 5 2018, 09:39 AM
Post#15



Posts: 79
Joined: 31-August 11
From: Lancashire,UK


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

Attached File  Query_screenshot.JPG ( 170.73K )Number of downloads: 14
Go to the top of the page
 
namron
post Mar 5 2018, 04:17 PM
Post#16



Posts: 79
Joined: 31-August 11
From: Lancashire,UK


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.
Go to the top of the page
 
RJD
post Mar 5 2018, 07:07 PM
Post#17


UtterAccess VIP
Posts: 8,360
Joined: 25-October 10
From: Gulf South USA


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

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
namron
post Mar 6 2018, 07:18 AM
Post#18



Posts: 79
Joined: 31-August 11
From: Lancashire,UK


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

Go to the top of the page
 
namron
post Mar 6 2018, 07:29 AM
Post#19



Posts: 79
Joined: 31-August 11
From: Lancashire,UK


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
Go to the top of the page
 
projecttoday
post Mar 6 2018, 07:54 AM
Post#20


UtterAccess VIP
Posts: 9,699
Joined: 10-February 04
From: South Charleston, WV


Glad you got it working.

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

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    21st June 2018 - 08:49 PM