Modules & VBA :: Sorting And Filtering A Report With A Form
Jul 9, 2014
I'm having a bit of trouble with a piece of VBA code I have. What it's supposed to do is filter and sort a report using a form. But there are two lines, which both I need, in it that are not compatible with each other. When both are in and I apply the filter with only a sort, it gives me a "Run-time error '5': Invalid procedure call or argument."
But the code runs beautifully when only one of the pieces, which I've highlighted in red below, is in it. The debugger highlights strFilter = Left$(strFilter, lngLen) after I have received the error.
Code:
lngLen = Len(strFilter) - 5
If (lngLen <= 0) And ([cboSort1] = "Not Sorted") Then
MsgBox "Insert criteria before filtering.", vbInformation, "No Criteria"
Else
strFilter = Left$(strFilter, lngLen)
I am having a little trouble using a 'canned' Access database. Using the built in Asset Tracking database, I am trying to get specific information on the Assets report. The Assets report generally shows all assets, I've made a new report to show the data sorted by the phone extension number, also the room number (which is how our inventory is tracked).
This works well, except, I want to be able to view one extension/room at a time. To do this, I added a button on the 'Enter/View Emplyees' form to preview the report, and created a macro to get this info. However, it only shows whatever data is highlighted in the emplyees sub-form. How can I get all of the data assigned to each extension to appear on the report?
Tried to upload the file, but it's too large. Can email if requested.
I have a form with 3 combo boxes that filter another form/report. The first combo (cboByCategory) contains options from 2 different fields within the same table. Before I added this add'l piece of code, all 3 combos worked fine. I am not getting error messages, it just does not filter on the other 2 combo boxes - cboDiv and cboGender.
Code: Private Sub cmdModifyRecords_Click() On Error GoTo Err_cmdModifyRecords_Click Dim stDocName As String Dim strFilter As String Dim stLinkCriteria As String stDocName = "Modify_OpenItems"
I have a project table that I track the progress of each job in by entering dates when certain portions are done. What I would like to do is when I fill in the "Project Completed" or "Cancelled" columns, to have that record automatically filtered out of the table. I still want to be able to turn off the filter at the end of the year to see all the projects we did, but I don't want this project cluttering up my table once it is finished or cancelled. Right now, I copy and move these completed projects to a separate table, but others say this is confusing and they can't figure out which table to use.
I input everything in the table format and don't have any forms that I use.
I am trying to create a form where the user can select which group of data they want (records complete, records due, etc) and also how they want the report sorted (project name, due date, etc.)
I created an option group for the record selection part with a variable named myFilter being equal to the required filter (i.e. myFilter = "(([Assessment Details].[Completion Date]) <= Now())")
I then created a combo box for the record sort part with a variable named myOrder being equal to the required sort (i.e. myOrder = " ORDER BY [Project Description].Deadline")
Finally I created a variable named myCriteria which is equal to myFilter & myOrder. Then I try to open the report with DoCmd.OpenReport stDocName, acPreview, , myCriteria.
Now, when I run the form and try to get the report, I get the following error: Syntax error (missing operator) in query expression '((([Assessment Details].[Completion Date]) <= Now()) ORDER BY [Project Description].Deadline)'.
Is there any way to have the user be able to select the sort order and have it pass to the report?
Below is the working version of the code works for single report filtering. say a report called "rptduedate_census2" and i can filter out using form instead of hard coding query of that report (say lastname=form!frmsample!txtlastname). I dont want to hard code query and there are reasons for that.
I am using a Main report called "rptDueDates_Dept' and there are about 10 different subreports (see print screen). The main report is not bound to any query. It has many subereport. Each subreport is bound to some query and i need to be able to filter them before data appear on the main report. Am i making any sense, LOL.
How can I implement my working code below to do this. PLEASE PLEASE PLEASE HELP. I AM NEW TO ALL THIS. I have attached a print screen of the main report, just to show how i put subreports in their.
===== MY WORKING CODE ====== Private Sub cmdPrv_Click() Dim strFilter As String
strFilter = "[Mail_Census_Date] BETWEEN #" & txtStartDate & " # AND # " & txtEndDate & " # "
' Apply the filter and switch it on
If SysCmd(acSysCmdGetObjectState, acReport, "Report") <> acObjStateOpen Then DoCmd.OpenReport "rptduedate_census2", acViewPreview With Reports![rptduedate_census2] .Filter = strFilter .FilterOn = True End With DoCmd.Restore Exit Sub End If
I am making a Customer Contacts database. The people in this database are organized into 30 groups.
I have one main form, and 30 queries to filter the records displayed on the form
I made buttons on a form representing the different groups of contacts to view, and when you click on one of those buttons, the main form opens with the record source as the query of the group you clicked on.
Also on the main form, I have a button to open a report that displays address labels of all the people in the database
How do i code that button, so that the records displayed in that report will be the same as those records displayed on the form? How do i filter the records in the report so they will be the same as in the form?
I have a report with a table as the row source. I have command buttons that opens different forms and allows the user to choose criteria, the form then filters the report based on the chosen criteria in the form, but if I use the destination city form to filter the report by destination city, then filter the report using a different form, the destination city filter is lost, is there a way to filter the report with a form by say destination city, then further filter that dataset with another form for say location city.
I am using the following code to filter my form with a search box:
Code: Me.RecordSource = "qryCompanies" Me.Filter = "CompanyName Like '*" & Me.SearchTxt & "*' Or webpage Like '*" & Me.SearchTxt & "*' Or PriorName Like '*" & Me.SearchTxt & "*'" Me.FilterOn = True
[Code] ....
It works great with one minor issue: if I try to search for a name containing an apostrophe, I get a syntax error. So, it won't find Children's Hospital for example.
I'm using 4 combo-boxes in order to filter a tabular form in access.
One combo box is for a company name the second is for a report type and the third and forth is for choosing years. I used VBA in order to apply the filter:
Each line works by itself but I cant figure out how to combine them together, or how to use "AND" between each filter so the filtering will refer all of them when I run the code.
I'm attempting to filter my form with combo boxes. I've added the below code, but the if statement that checks if one of the combo boxes is null won't work. If the combo box is null or "", it's supposed to assign a wildcard to the variable. But it doesn't pass the test and goes straight to the Else statement. I've highlighted the if statement that keeps failing in red.
Code: Private Sub btn_Search_Click() 'create variables to store the combo box values Dim str_Country As String Dim str_Vendor As String Dim str_Survey As String
Hi all. I've created a database which contains information about stores. I want to have the forms automaticly sorted by the department number. I've tried to sort the table by department, but when I try to add a department, the sorting doesn't seem to affect the form at all.
Lets say I have department 1,2,3,6,7,8 in the form, and I add department 4, it will be the last post in the form. I want it to be the fourth, and so on..
I'd apreciate some help with this :) Thanks
Here's the database (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12934&stc=1&d=1142018915&PHPSESSID=f730b7f11f6983965698faeacbe5a1ee)
I have a form and subform where i have table data in the subform and 03 Combo box in the main form header. I need to do some filtering using combox box1 and out of that filtered records i need to do one more filtering using combobox2. then again another filtering by Combobox3. ( same way we are doing in Excel)
Subform is running on a query where i have following fields;
Vessel Voyage, POL POD MLO
by Combo box 1 i need to filter Voyage by Combo box 2 i need to filter POL ( out of the data filtered by above ) by combo box 3 i need to filter POD ( out of the data filtered by both above )
i have a calender of which show's records on date box's i m having a issue when i double click on a datebox it opens a form call update which is filtered on open to show only records for this date but on date boxs on 1st to the 11th i have add the code to do this but when the form open's the form is blank and shows no records but the code works fine for 12th to 31st which i can't understand why? as im using the same code that does dateboxs 12th to 31st on datebox 1st to 11th but doesnt work i'm pulling my hair out on this one !!
The aim of what I am doing is to create a monthly statement to give to our intermediaries that shows the commission they will receive each month for the deals they have referred. I have managed to create this report, HOWEVER I can't figure out how to filter out which month I need, so I a report for Jan, Feb Mar etc... The idea is that at the end of each month I need to run the report so only the latest month shows...
I have created a report from the information submitted on a form. When I pull up the report, I would like it to sort differently than it is. I am not sure what it is using to sort from but I want it to sort by a designated # I have assigned it.
Is there a way to make it so the access's record # will always match our companies record #?
Today I entered information in a form that should have gone in as record 96 but when I went back to look at the record in the report, it was record # 72. ??????? Now what do I do?
Is there a way to Sort records in a report by a Report total? I have a report that is created by a crosstab query that sums orders by Billing Client but the query also has a billing client qroup which can contain many Billing Clients. Since the report total (Counts) are by Billing Client I do not have the actually totals per client group in the query to sort by. I know how to do this in Crystal Reports by the report would needs to mulpiple workstation, which have the crystal reports engine but not the actually designer (In other works I don't know the code to print a crystal report from Access with only the crystal reports engine on the workstation).
I have a table that contains a text field [drawing number] and a report that displays the drawing numbers and I can not get the sorting correct.Each record has a drawing number like:
I am working on a access report for financial analysis.I created a form with a drop down list to select cost center from tables, so the report will only show the data from the selected cost center. My questions are:
1. I want the chosen cost center automatically shown at the Header of the report, as selected from the form;
2. The data was grouped in a particular sequence, such as: Revenue, Compensation, MM&S and Other Expense, not simply ascending or descending
3. There are some category data I do not want to show, for example, other expense, how can I hide it?
I have a calculated field in my report which is the sum of scores for students i.e. each student does 8 subjects and my calculated field tallies how many each student has passed. so the report shows a list of students and the number of passed subjects. Now I want to show only those who have passed 7 or more subjects. Can such a filter be done? It is obvious I can't use the query since the summary calculate field does not exist in the query.
I have a query where I ORDER BY ClientId, MatterId and Transdate. The result of the query is correct. All of the transdates are in ASC order within the Matter.
When I run the report for a MatterId the relative transactions are not sorted on transdate
Can there be something in the report that could be effecting the order of the transactions.
hi! i tried searching the forum for a possible solution to my problem. unfortunately, no luck so far.
Here's my problem. i'm working with a linked table in access with a date field (service_dt) formatted as a text. if i open the table, the date shows up as 1-Jan-2006. i use this table to populate a report.
im trying to filter the report so that it would show records that have a specific date range. here's what i write on the filter of the report: service_dt Between #01/01/2006# and #12/31/2006# unfortunately, its not filtering properly. i would appreciate any thoughts.
report i'm trying to preview. I have a form which will display a person's training record and on that form i'd like to have a button which, when activated will show the record as a report in Print Preview. However, i'd like to just preview the record i'm looking at and not all of that person's records.
In the production report that I made, I want to achieve 2 things.
1) I want the report to filter automatically to display only the current month's invoice. As you can see, all of the invoices are displayed from 2012-2014.
2) I also want to be able to filter the report based on the invoice number. I want this to be achieved by clicking the invoice button on the top part of the report.
I've created a Form where I enter all my data - then I created a Report to output this data, it includes two buttons to filter based on one category. That's working fantastically well for me.
But now I would like to filter on dates. For example, last 7 days, last 14 days etc - and also give the user the option of filtering between x and y dates. I've discovered this is easily done by right-clicking the date field in the report, but I'd prefer to embed these commands into buttons etc so it's easier to navigate.
