Not only that, but a less complex query is more efficient all the way around! Yay! John is the best isn't he?
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, May 27, 2014 11:28 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Access 2010 query problem
Thanks John! I haven't undertaken the code writing yet but I eliminated search fields that aren't required fields on the input form which fixed my query problem. That will get me by until I figure out the code stuff.
Jessica
On Tuesday, May 27, 2014 11:17 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jessica-
If any of the search fields are left blank on the form, your query will not return rows that have a Null value in those fields. That could be why you think some rows are missing in your result set. (There's a way to restate this query to solve this problem, but it will fail with "query too complex" because you have more than three filter fields.)
When you have a "query by form" that is this complex, it's much better to write code behind your filter form to examine what the user entered and build a dynamic filter using only the filter fields that have values. Your query doesn't need to have a WHERE clause at all, but it should be bound to a form or report that you can open from code and use the WhereCondition parameter in either the OpenForm or OpenReport command.
You can find samples of code to do this on my website - the Links tab, then download Contacts2000.zip. Take a look at the fdlgContactSearch form and the VBA code behind it.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On May 27, 2014, at 5:03 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Good Morning Bill and John!
My search form is constructed with unbound text boxes ( [frmSearch]) that will be used to collect stats and monitor activity. This is the SQL from the query (this is from a db I'm building for a law enforcement agency to simplify the Use of Force reporting the officers are required to do) :
SELECT [Use of Force].ID, [Use of Force].LawIncidentNumber, [Use of Force].Nature, [Use of Force].IncDate, [Use of Force].[Subject'sName], [Use of Force].Gender, [Use of Force].DOB, [Use of Force].ReasonForForce, [Use of Force].Explain, [Use of Force].SubjectInjured, [Use of Force].SubjectInjury, [Use of Force].WhereTreated, [Use of Force].Unconcious, [Use of Force].ChemicalImpairment, [Use of Force].Explainimpairment, [Use of Force].OfficersPresent, [Use of Force].SubjectsInvolved, [Use of Force].OfficerInjured, [Use of Force].OfficerInjury, [Use of Force].WhereOfficerTreated, [Use of Force].LevelofResistance, [Use of Force].ExplainResistance, [Use of Force].CommandsGiven, [Use of Force].JointLockTechniqueUsed, [Use of Force].[MACH Hold Used], [Use of Force].[Pressure Point Location].Value, [Use of Force].[Pressure Point Location], [Use of Force].HandStrikeLocation, [Use of Force].[Leg/FootStrikeLocation], [Use of Force].LevelofNeckRestraintUsed, [Use of Force].LocationofStrike, [Use of Force].NumberofStrikesNeeded, [Use of Force].Effective, [Use of Force].TaserNumUsed, [Use of Force].NumberofApplications, [Use of Force].LocationofUse, [Use of Force].CartridgeSerialNumber1, [Use of Force].CartridgeSerialNumber2, [Use of Force].Front, [Use of Force].Back, [Use of Force].Describetechniques, [Use of Force].DateofReport, Officers.Officer, Shifts.Shift, IIf(Month([DOB])>Month([IncDate]),DateDiff("yyyy",[DOB],[IncDate])-1,IIf(Month([DOB])=Month([IncDate]) And Day([DOB])>Day([IncDate]),DateDiff("yyyy",[DOB],[IncDate])-1,DateDiff("yyyy",[DOB],[IncDate]))) AS Age
FROM (Shifts INNER JOIN [Use of Force] ON Shifts.Shift = [Use of Force].Shift) INNER JOIN Officers ON [Use of Force].[Reporting Officer] = Officers.Officer
WHERE ((([Use of Force].ID) Like "*" & [Forms]![frmSearch]![Report ID] & "*") AND (([Use of Force].LawIncidentNumber) Like "*" & [Forms]![frmSearch]![Law incident number] & "*") AND (([Use of Force].Nature) Like "*" & [Forms]![frmSearch]![IncNature] & "*") AND (([Use of Force].IncDate) Between [Forms]![frmSearch]![From] And [Forms]![frmSearch]![To]) AND (([Use of Force].[Subject'sName]) Like "*" & [Forms]![frmSearch]![SubjectsName] & "*") AND (([Use of Force].Gender) Like "*" & [Forms]![frmSearch]![Gender] & "*") AND ((Officers.Officer) Like "*" & [Forms]![frmSearch]![ReportingOfficer] & "*") AND ((Shifts.Shift) Like "*" & [Forms]![frmSearch]![Shift] & "*") AND (([Use of Force].[MACH Hold Used].Value) Like "*" & [Forms]![frmSearch]![MACHHold] & "*"));
This is my first database and I'm guessing I've made it far more difficult than necessary...
Thanks!
Jessica
On Monday, May 26, 2014 3:40 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jessica-
It would also be helpful to know how your "search" form is constructed - which fields you are trying to filter and what you have on the search form in the way of either combo boxes or text boxes to provide the filters.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On May 26, 2014, at 12:10 AM, 'Bill Mosca' wrmosca@comcast.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Welcome, Jessica.
The best way for us to help you is for you to give us the SQL for the query. Open the query in SQL view. Copy it and paste it here.
Regards,
Bill Mosca,
Founder, MS_Access_Professionals
That'll do IT http://thatlldoit.com
MS Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My Nothing-to-do-with Access blog
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, May 23, 2014 2:40 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Access 2010 query problem
Hello All, this is my first post to this group and I'm very new to Access (so layman terms please!).
I have created a database for my employer and I'm having trouble with a query. I created a search form based on a query with several fields from which I can query. All works well until suddenly a random record won't show up in the query through my search form. I can get all of my records with any other query. I'm still testing out the database by adding fake records. For example: records 1-9 search up just fine, 10 won't come up in this search form query, but 11 and 12 will.
Any ideas about why this is happening?
Thank you!
Jessica
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Posted by: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (13) |
Tidak ada komentar:
Posting Komentar