I have a query which lists data for 86 different units. I need to get the query to
return the last entry for each position on each unit, based on turn date. There should be 16 positions for each unit. Then I need to be able to create a report based on the query for just one unit or for all 86 units. I have attached a zip file of how far I have got. I've been stuck on this for ages, so I'm hoping someone can help!
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code:
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I have a continuous form based on table "INCOMES" that shows all the payments received, which mediums can be (field "PMNT_MEDIUM"):
- check
- transfer
- taxes
- cash
Table "INCOMES" is filled using another form, but in this particular form I just want to show "check", "transfer" and "cash" (not "taxes") so that I can track all the cash incomes.
Note: taxes are loaded because they appear in my invoices and I need them there to reach the invoice total amount.So my form has a search bar which allows me to search by PMNT_MEDIUM listing all "checks", all "cash" or all "transfer". I can also search by payment number (meaning: check number). To that end I have a "search" button that applies the filter. And I have another button that "cleans" the filtering by "putting a "" in the search-bar and then calling the "on click" of the search button".
What I need is, no matter if I click over the "search" or "clean" button, it NEVER shows me the "taxes".Search button, on click code:
If IsNumeric(Me.busq_chq_med) Then
Me.Filter = "[PMNT_MEDIUM_NUMB] =" & Me.SEARCH_BAR
Else
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*'"
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*' or [INVOICE] like'" & Me.SEARCH_BAR & "*'"
End If
Me.FilterOn = True
Clean filter button, on click code:
[SEARCH_BAR] = ""
Call [Search button]_click
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*'"
Me.FilterOn = True
I have a form that I use a filter on to make my where statement for the query I have. The form has 3 drop downs: County, City, and State. Everything works great except when you only choose a state you should get the totals for just the state and instead you get a report by city. Here is my sql:
SELECT Sum(IIf([Homeless]="yes",1,0)) AS HomelessCnt, Sum(IIf([NFresident]="yes",1,0)) AS NFresidentCnt, Sum(IIf([Self]="yes",1,0)) AS SelfCnt, Sum(IIf([StaffBoard]="yes",1,0)) AS StaffBoardCnt, Sum(IIf([FamilyFriend]="yes",1,0)) AS FamilyFriendCnt, Sum(IIf([CommLarge]="yes",1,0)) AS CommLargeCnt, Sum(IIf([ServeProvide]="yes",1,0)) AS ServeProvideCnt, Sum(IIf([Physical]="yes",1,0)) AS PhysicalCnt, Sum(IIf([MentalEmo]="yes",1,0)) AS MentalEmoCnt, Sum(IIf([Cognitive]="yes",1,0)) AS CognitiveCnt, Sum(IIf([Vision]="yes",1,0)) AS VisionCnt, Sum(IIf([Hearing]="yes",1,0)) AS HearingCnt, Sum(IIf([Multiple]="yes",1,0)) AS MultipleCnt, Sum(IIf([AdvocacyServe]="yes",1,0)) AS AdvocacyServeCnt, Sum(IIf([AssistDevServe]="yes",1,0)) AS AssistDevServeCnt, Sum(IIf([ChildServe]="yes",1,0)) AS ChildServeCnt, Sum(IIf([CommServ]="yes",1,0)) AS CommServCnt, Sum(IIf([FamilyServe]="yes",1,0)) AS FamilyServeCnt, Sum(IIf([HousingServe]="yes",1,0)) AS HousingServeCnt, Sum(IIf([ILSkillServe]="yes",1,0)) AS ILSkillServeCnt, Sum(IIf([InformationServe]="yes",1,0)) AS InformationServeCnt, Sum(IIf([MentalRest]="yes",1,0)) AS MentalRestCnt, Sum(IIf([MobilityServe]="yes",1,0)) AS MobilityServeCnt, Sum(IIf([PeerServe]="yes",1,0)) AS PeerServeCnt, Sum(IIf([PersonalServe]="yes",1,0)) AS PersonalServeCnt, Sum(IIf([PhysicalRest]="yes",1,0)) AS PhysicalRestCnt, Sum(IIf([PreventiveServe]="yes",1,0)) AS PreventiveServeCnt, Sum(IIf([Prostheses]="yes",1,0)) AS ProsthesesCnt, Sum(IIf([RecServe]="yes",1,0)) AS RecServeCnt, Sum(IIf([RehabTech]="yes",1,0)) AS RehabTechCnt, Sum(IIf([CounselServe]="yes",1,0)) AS CounselServeCnt, Sum(IIf([Therapeutic]="yes",1,0)) AS TherapeuticCnt, Sum(IIf([TransportServe]="yes",1,0)) AS TransportServeCnt, Sum(IIf([YouthServe]="yes",1,0)) AS YouthServeCnt, Sum(IIf([VocationServe]="yes",1,0)) AS VocationServeCnt, Sum(IIf([OtherServe]="yes",1,0)) AS OtherServeCnt, Sum(IIf([Newsletter]="yes",1,0)) AS NewsletterCnt, Sum(IIf([EventFlyer]="yes",1,0)) AS EventFlyerCnt, Sum(IIf([VFIBrochure]="yes",1,0)) AS VFIBrochureCnt, Sum(IIf([WaiverInfo]="yes",1,0)) AS WaiverInfoCnt, Sum(IIf([Survey]="yes",1,0)) AS SurveyCnt, Sum(IIf([Other]="yes",1,0)) AS OtherCnt, Sum(IIf([Advocacy]="yes",1,0)) AS AdvocacyCnt, Sum(IIf([SkillTraining]="yes",1,0)) AS SkillTrainingCnt, Sum(IIf([PeerSupport]="yes",1,0)) AS PeerSupportCnt, Sum(IIf([Deinstitutionalization]="yes",1,0)) AS DeinstitutionalizationCnt, Sum(IIf([ReferralPAS]="yes",1,0)) AS ReferralPASCnt, Sum(IIf([ReferralVFI]="yes",1,0)) AS ReferralVFICnt, Avg(PersonalInfo.MinSpent) AS MinSpentAvg, Avg(PersonalInfo.CallerAge) AS CallerAgeAvg, PersonalInfo.CallerCounty, PersonalInfo.CallerCity, PersonalInfo.CallerState
FROM PersonalInfo
GROUP BY PersonalInfo.CallerCounty, PersonalInfo.CallerCity, PersonalInfo.CallerState;
I have tried to take out the group by and PersonalInfo.CallerCounty, ect. Any ideas would be greatly appreciated.
Thanks
I am having trouble with the query producing the correct Dates.
I have the Log Date Field and for the criteria I have:
Between Nz([Forms]![frmDate]![txtStart],[LogDate]) And Nz([Forms]![frmDate]![txtEnd],[LogDate])
When I run the query it asks for Forms!FrmDate!txtStart I enter 10/1/05
It then asks for Forms!FrmDate!txtEnd I enter 10/31/05
The results produced are wrong. It shows me records fro 10/1/05 to 10/29/05 it is not including 10/31/05;
Also for Start if I enter 10/26/05 and End I enter 10/26/05 It gives me 0 records when there are 3 records for that date.
Attached is the db. I'd really appreciate it if someone can take a look at it.
I have two seperate tables that display a part number (Tables A and B). Both tables have part numbers while Table A has a segment of the part number and Table B has the full part number (ex. (A) 2501 ; (B) PUTCO-SYSTEM-2501 ). I need table A to have the full descriptions just like table B has. Is there anyway I can link the tables and run a query to get this? Thanks.
View 1 Replies View RelatedHey All,
I have asked previous questions and searched extensively on this topic and I am still without a solution.
Im trying to filter a range in a queries field. Here is the way my query is organized.
All this data deals with forecasting for my company...
Version - The current version of the forecast (represents a 2 year period techniqually)
Plant - One of our cement plants
CK Type - Clinker Type, main ingredant for cement
All of these are for place keeping, it is the way they need to be inputed into another tool.
Layout:
Version / Plant / CK Type / *Version (current) / *Version (next) / *Verison(after next) / etc.
* - Actual numbers eg 200909 for september, 2009
The other verison fields are for the actual data entry through a keystroke. They continue out to 2014 for the time being.
What I need to do is dynamically filter a range starting with the current version and ending with the version 2 years out. So I would be filtering the field.
Is this possible? Everything I have tried leads me to believe that it is not. Perhaps its in my design?
I know a formula that will get me the proper output for the version:
Version = 200711,200712,200801, etc.
iif(Version(right,2)=12,Version+89,Version+1) , but I cannot get this to populate the fields.
This is all I need to complete my database.
Thanks
I have a form that is showing data from 1 table. That table has 12 different fields on it and I want to be able to filter based on selections I make in a combo box in the header of the form. The filter string must be dynamic enough to allow filtering based on 1 criteria selected, or multiple criteria selected. For example:
If I have values in filter fields 3, 5, and 9 I'd want the filter string to be created as follows:
"...WHERE field3 = field3filter.value AND field5 = field5filter.value AND field9 = field9filter.value"
If I have values in only field 7, I'd want th efilter string to be created as follows:
"...WHERE field7 = field7filter.value"
And so on and so on.
I have created some filters before but all of the different VBA syntaxes I'm using seem to come up short.
How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
View 3 Replies View Relatedi have a table with 20,000 records. one column includes names. another column includes multiple dates the person visited a doctor, and another column includes dates the person visited a hospital. i am only interested in the last date the person visited a doctor and the last date the person visited a hospital (if they had any visits to the doctor or hospital). I am also interested in members who have had no visits to the doctor or hospital. any suggestions on how i can filter the data to only return these records? thanks
Name Date Visited DoctorDate Visited Hospital
Nigel01/01/1980012/31/1995
05/20/199906/06/2005
03//02/200405/06/2004
For example, I only want to return
Nigel, 03/02/2004 (Date Visited Doctor), and 06/06/2005 (Date Visited Hospital). thank you. i really appreciate any help you can offer.
Hello, I have been working on my database for sometime - searching this forum for answers, thanks to all the experienced people for their sharing of knowledge.
My question is this: My database will be over several years of data, on my splash screen (switchboard) I would like to have a combo box of with choices for years. When a user chooses a year, the entire database is filtered. Say if the choice was for 2005, then anything with a binderdate of 01/01/05 to 12/31/05 will be filtered.
This will need to be done across several tables, queries, forms, and reports.
Once the choice is made for that year - any form the user is on will be filtered for that specific year.
I am still fairly new at access programming, so any code snips or working examples would be greatly appreciated.
Hi have a query which looks up a bunch of values across 2 tables and i have a parameter which prompts user to input a date and i then want all records matching that date to be returned but it just returns all records: The sql for the query is a follows:
SELECT Tbl_Customers.Customer, Tbl_Customers.[Telephone Number], Tbl_Customers.DateCalled, Tbl_Products.Sales_ISP, Tbl_Products.Sales_IBP, Tbl_Products.Sales_ITSM, Tbl_Products.Sales_Datasure, Tbl_Products.Sales_BusEmailPlus, Tbl_Products.Sales_IntTrade, Tbl_Products.Sales_IntTradePay, Tbl_Products.Sales_Mobile, Tbl_Products.Sales_Fusion, Tbl_Products.Sales_BBV, Tbl_Products.Sales_BBVPlus, Tbl_Products.Sales_Other, Tbl_Products.Sales_OtherCheck
FROM Tbl_Customers LEFT JOIN Tbl_Products ON Tbl_Customers.ID = Tbl_Products.ID_Custref
WHERE (((Tbl_Customers.DateCalled)=[Please select date]) AND ((Tbl_Products.Sales_ISP)=Yes)) OR (((Tbl_Products.Sales_IBP)=Yes)) OR (((Tbl_Products.Sales_ITSM)=Yes)) OR (((Tbl_Products.Sales_Datasure)=Yes)) OR (((Tbl_Products.Sales_BusEmailPlus)=Yes)) OR (((Tbl_Products.Sales_IntTrade)=Yes)) OR (((Tbl_Products.Sales_IntTradePay)=Yes)) OR (((Tbl_Products.Sales_Mobile)=Yes)) OR (((Tbl_Products.Sales_Fusion)=Yes)) OR (((Tbl_Products.Sales_BBV)=Yes)) OR (((Tbl_Products.Sales_BBVPlus)=Yes)) OR (((Tbl_Products.Sales_OtherCheck)=Yes));
As you can see it filters by looking for any check box with a value of yes and then hopefully by date -
I just cant figure why it wont filter on date
Any help please
HI there,
This sounds simple, but I'm not sure what the coding is in SQL. Perhaps someone can help..
I have a query which has criteria set by a form.
The First sorts the data in the query by SalesPerson from the selection made in a Forms combo box.
The second sets the date the query filter starts at and finishes.
So far so good.
However, I have an entry in the combo box for "ALL" sales people so that the user can see all the results for all sales people.
SELECT tblSalesinfo.Sales_reference_autoID, tblSalesinfo.CustomerID, tblSalesinfo.Completed, tblSalesinfo.[Sales CO], tblSalesinfo.Date, tblSalesinfo.Cust_Ref, tblSalesinfo.Customer_Name, tblSalesinfo.Description, tblSalesinfo.CTN, tblSalesinfo.Sold_£_Phone, tblSalesinfo.Sold_£_Accessories, tblSalesinfo.Sold_£_Other, tblSalesinfo.Labour_£_Charge, tblSalesinfo.Total_Sales, tblSalesinfo.Invoice_Number, tblSalesinfo.Delivery_Note, tblSalesinfo.Tariff, tblSalesinfo.Commission_due_amount, tblSalesinfo.Other_value, tblSalesinfo.Total_deal_Value, tblSalesinfo.Cost_A, tblSalesinfo.Cost_B, tblSalesinfo.Cost_C, tblSalesinfo.Cost_D, tblSalesinfo.Total_Cost, tblSalesinfo.Total_Deal_Balance, tblSalesinfo.[Margin%], tblSalesinfo.Sales_Commission_Due, tblSalesinfo.[Recovered Stock]
FROM tblSalespersons INNER JOIN tblSalesinfo ON tblSalespersons.SalesID = tblSalesinfo.[Sales CO]
WHERE (((tblSalesinfo.[Sales CO])=[Forms]![frmDisplayResults]![SalesCO]) AND ((tblSalesinfo.Date) Between [Forms]![frmDisplayResults]![StartDate] And [Forms]![frmDisplayResults]![EndDate]));
Can someone help with the code to say that if [SalesCO] = "ALL" it does not filter the results by Salesperson and shows the query for the date range only.
Many thanks!
I've searched the forums, and i cant find an answer there.
What i want to do is filter a query field by the current system date, but only using the dateparts Day an Month (no Year).
How would i go about it?
I've searched the forums, and i cant find an answer there.
What i want to do is filter a query field by the current system date, but only using the dateparts Day an Month (no Year).
How would i go about it?
I've searched the forums, and i cant find an answer there.
What i want to do is filter a query field by the current system date, but only using the dateparts Day an Month (no Year).
How would i go about it?
Good Morning
I am attempting to apply a filter to a query i have setup. I only want the user to be able to search for specific words within two fields (technical and behvaioural) i have setup. I know i have to use a WHERE expression but am confused as how to exactly setup the expression... any idea's???
Hi,
I am having a few issues with a query that is used to filter results in a form, the SQL follows
SELECT tblMoisture.SampleName
FROM tblSampleSubmission INNER JOIN tblMoisture ON tblSampleSubmission.SampleName = tblMoisture.SampleName
WHERE (((tblSampleSubmission.SubmissionNumber)=[Forms]![frmH2OSelect]![cboSelectH2O]));
It seems to work fine when I run as is, but when it is called from the form, frmH2OSelect (using a macro, open form then apply filter) it asks for tblSampleSubmission.SubmissionNumber, even though the form is still active.
I sthere something wrong with relationships between the 2 tables? It should a one-to-one relationship between SampleName. tblMoisture does not contain the field SubmissionNumber, but if I include it in this table (not very normalised) it works fine.
Any help would be appreciated.
I am trying to get my filter query to work properly, but its not quite there yet!
Here's the Query Criteria
Like "*" & [forms]![myform].[Grp] & "*" And Like "*" & [forms]![myform].[Grp2] & "*" And <>"MICK"
Basically i want to have my query filter records with a value in Grp, Grp2 or a combination of both, whilst always filtering out MICK
The options for Grp and Grp2 are Steve, Mark, Shaz ,Bev
The query works with one name in either Grp or Grp2, but if Grp and Grp2 are filled, i get no output whatsoever! Any ideas? Thanks
Hi. Im struggling to work out how to form a suitable WHERE clause in a query.
What I wish to return are records where a date stored in a field called "started" falls into the current month.
E.g. if the current month is January 08 (which I believe it is!) all records where the month and year part of "Started" equals January and 2008 respectively.
In a separate query (and I think this is a little trickier), to return all records "started" during the previous month. E.g. (assuming Jan 08 again) where Month is December and Year is 2007.
Many thanks.
Hey i have this really annoying bug, theres several tick boxes that im using to filter records but if i tick more than one i get a syntax query error? any idea what this could be? i have had several people look at the code and tell me theres nothing wrong.
Option Compare Database
Option Explicit
Const strSQL = "SELECT * FROM issues"
Private Sub filter_Click()
'Variable to hold filtered SQL string
Dim strFilterSQL As String
If check_os98 = True Or check_osnt = True Or check_os2k = True Or check_osnt = True Or check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strSQL & " WHERE "
End If
If (check_os98 = True) Then
strFilterSQL = strFilterSQL & "os98 = True"
If check_osnt = True Or check_os2k = True Or check_osnt = True Or check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_osnt = True) Then
strFilterSQL = strFilterSQL & "osnt = True"
If check_os2k = True Or check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_os2k = True) Then
strFilterSQL = strFilterSQL & "os2k = True"
If check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_osxp = True) Then
strFilterSQL = strFilterSQL & "osxp = True"
If check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_fxpda = True) Then
strFilterSQL = strFilterSQL & "fxpda = True"
If check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_fxpc = True) Then
strFilterSQL = strFilterSQL & "fxpc = True"
If check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_fxas = True) Then
strFilterSQL = strFilterSQL & "fxas = True"
If check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_fxrs = True) Then
strFilterSQL = strFilterSQL & "fxrs = True"
End If
strFilterSQL = strFilterSQL & ";"
Me.RecordSource = strFilterSQL
Me.Requery
End Sub
Can't seem to locate a similar situation in the forum for this problem. I have a form based on "qryProjects". I have another form "Menu" which has cboType, lstStatus and lstSubtype. I thought that I could select criteria from each and, with a command button, have the form open up filtered with the three criteria. In qryProjects pane I have as the criteria for each of the three fields:
[Forms]![Menu].[lstStatus] - Value list
Bound column = 1
[Forms]![Menu].[cboType]
SQL in cboType = SELECT DISTINCT [SubType].[Type] FROM SubType;
Bound column = 1
[Forms]![Menu].[lstSubtype] = this is filtered on the After Update of cboType
SELECT [SubTypeID], [Subtype] FROM Subtype;
Bound column = 1
I have them on separate lines in the query. When I run the query, it only filters on cboType and not the others. If I place the criteria all on the same criteria line, I get nothing.
Is my criteria in the wrong place? I was originally trying to make the form completely "unbound" and build a SQL string to pull the records but don't know if that is possible. My purpose is to eliminate macros that I was using as it took over 2 minutes to open the form with the BE residing on a shared network drive.
Would appreciate any ideas/suggestions.
Thanks,
Toni
In my database I want to create a report that identifies if some is available or not every day. I have a general table(Tbl_General) with names and contact info, in a second table(Tbl_Recall) I have 3 fields Location StartDate EndDate. I want to show every person from the general table and only records from the Recall table where the start date is before today and the end date is after today.
The queries I write exclude some records if I limit the dates and the reports I build show all records in the recall table.
Basically what I am looking for is a way to set the criteria of a specific field in a query from VBA.
The reason why:
I have one very large query that includes lots of fields from many tables. I want to make a "search form" that sets one or multiple fields to have criteria to narrow it down.
You could select the field, say, serial number, from a drop down on the form, then enter a serial number and click a button that would refresh the form, showing the new query output.
Then you change from "serial number" to "location" in the dropdown, and type in a location, and it would remove the criteria from the serial number field and add one to the location field. When you click the button it will refresh and show the new query output (all the items in the specified location).
Basically I am trying to create one form to search the output of a query in multiple ways. I would like to keep it to one form and not have to make multiple versions of the query with different criteria.
So I have a Form with 3 fields. 2 Date fields (Beginning & Ending) as well as a combo box (CName). There is a button (Filter).I want to fill in both dates and select from the dropdown box. Onceeach field has a value then it will send a msgbox saying there were "# of records" between the 2 dates by CName
View 14 Replies View RelatedI'd be grateful if someone could offer some help with this to a frustrated Access novice : )
I have a query that filters records according to the value of an unbound text box in my form (basically search on last name)
I want to introduce wildcards into the filtering process but cannot get it to work.
Here's what I've done:
In the criteria field I had
[Forms]![MAIN]![Text440]
refering to the unbound text box on the form already mentioned.
This worked fine, but the following:
Like "*[Forms]![MAIN]![Text440]*" does not.
Nor does various combinations of brackets such as:
Like "*([Forms]![MAIN]![Text440])*"
In fact it now doesn't return anything, even if given a positive match. I have trawled help and the forums but to no avail. I have also tried the % character instead of *
I am using Access2000...