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] & "*"));
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-
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
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
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
__._,_.___
Posted by: Jessica Hoback <hobackjessica@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar