This query is scary in so many ways…
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, May 27, 2014 8:03 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Access 2010 query problem
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 (5) |
Tidak ada komentar:
Posting Komentar