Report Sorting From Form Selection
Sep 27, 2004
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?
Thanks in advance.
View Replies
ADVERTISEMENT
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)
[Code] ....
View 14 Replies
View Related
Oct 25, 2005
I have set up a query to give me details of employees names, the business they work for and where they work.
If I run it without criteria I get all of my records, as you would expect.
However, I want to be able to select parameters from combo boxes and then run a specific report of employees by location. I did have this working by accident a while ago but have changed quite a lot since then.
I have set up an unbound form with three combo boxes - Business, Business Unit and Location. Then a button for preview and print report linking to my report template.
I have refernced the controls in the relevant columns of my query as: [Forms]![FormName]![Control].
When I run the query I get nothing back and I don't understand why.
View 9 Replies
View Related
Apr 19, 2005
Hello all.
I would like to place a "Print Report" button on my primary form that allows a Filter By Selection (OR Filter By Form) first, then when selecting the button will view or print my already-created primary report but only for the records that were selected by the filter.
I looked and looked, but am not sure even what to search for in the forum. I know this has to be simple.
Form is called PrimaryForm.
Report is called PrimaryReport.
Thank you.
Tom
View 3 Replies
View Related
Mar 10, 2006
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)
View 5 Replies
View Related
Oct 27, 2014
I am designing a nomination form (web database so no vba macros can be used).
The form has 3 combo boxes: cboStaffName, cboLevel, cboNominee.
The form is bound to the tblSubmit table where the submissions are populated.
I used a select statement:SELECT Staff_List.Staff_Name FROM Staff_List; to populate the combo box for the Staff Name selection.
This is the select statement to populate the job level combobox:
SELECT Staff_List.Level, Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Staff_Name)=[forms]![frmtest]![cboStaffName]));
The select statement to populate the combobox for the nominee combobox:
SELECT Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Level)=[forms]![frmtest]![cboLevel]));
The problem is that the staff_name in the first combobox is still found in the nominee combobox which should not be because a staff cannot nominate self. There is a field in the Staff_List called YesNo that should be activated for each staff that is selected so that the select statement on the nominee combobox can be updated accordingly to remove items with the field "Active"
How to get the checkbox selected for each corresponding staff.
Sample of the database has been attached.
View 4 Replies
View Related
Feb 9, 2005
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?
View 2 Replies
View Related
Jul 30, 2005
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).
View 2 Replies
View Related
Feb 15, 2012
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:
379-02-0002
379-02-0002-3
379-02-0002-5
379-02-0002-9
379-02-0002-11
The report sorts them like this:
379-02-0002-11
379-02-0002
379-02-0002-3
379-02-0002-5
379-02-0002-9
What I want is this:
379-02-0002
379-02-0002-3
379-02-0002-5
379-02-0002-9
379-02-0002-11
Is there a way to get the sort order the way I want it?
View 6 Replies
View Related
Oct 22, 2004
Hi everyone,
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.
Thanks,
Brian
View 1 Replies
View Related
Aug 18, 2015
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?
View 12 Replies
View Related
Jul 27, 2014
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.
View 3 Replies
View Related
Jun 29, 2006
Ok, I am a noob at reports and did a search on this. My searching DID NOT HELP ME! I thought I would make that clear :D Maybe someone can tell me what to look at for this issue.
I need to take one report, allow the users to easily use a dropdown or listbox on a form to Sort and Group many fields of data. To recap: The user will click on name in the list and it will sort report by name. Also I need them to be able to select up to three sort criterias.
EX: Sort by Name then LastName then Company
Please guide me to the answer. Thanks.
View 3 Replies
View Related
May 15, 2013
Access 2010. I have a table with the following fields:
- From
- To
- TypeOfWork (to be chosen from a combo-box)
- Activity (text field to be filled in freely)
In a table i have a complete day with times (from -> to), the type of work between those times and the activity performed between those times a bit like this:
FROM TO TYPE OF WORK ACTIVITY
00:00 - 11:00 Welding Welding clamps
11:00 - 13:00 Welding Welding anodes
13:00 - 15:00 Cleaning Cleaning pipes
15:00 - 18:00 Cleaning Cleaning floor
I would like it to show in the report like this:
FROM.....TO.......TYPE OF WORK.....ACTIVITY
00:00 - 13:00......Welding............00:00 Welding clamps
...............................................11: 00 Welding anodes
13:00 - 18:00......Cleaning...........13:00 Cleaning pipes
...............................................15: 00 Cleaning floor
So it should sort on "From", then group by "TypeofWork" and repeat the "From" field(I think...). But I now have tried every combination of sorting and grouping I could think of and nothing works!
View 13 Replies
View Related
Dec 2, 2014
I'm trying to group data in a report from single table using grouping and sorting and I want the percentile of every record over group total. I'm using a query to fetch data from table, however I'm unable to get percentage of every single record over group total.
I want to display the report as attached image in single report. I'm unable to get data in "Perc" field. It's populating wrong values.
View 3 Replies
View Related
Jul 1, 2013
I'm trying to make a report that a untrained user can use to review the relevant data from the database I'm creating. All of the work I've done so far has been directly in the tables . Here's a quick outline of my general data organization:
Department:
DepartmentID(PK)
DepartmentName
Employee:
EmployeeID(PK)
EmployeeName
DepartmentID(FK)
Needs_Work (check box)
[code]....
Basically, I want the user to select a department to view using a combo box or option group (which I'm not sure of how to do in a report). Then, I want to view the employees within that department sorted first by whether or not the Needs_Work check box is selected, and then by how many of their competencies are unchecked.
View 2 Replies
View Related
Jul 25, 2013
I have some requirements whereby there are many reports that are needed to be given to the employer. I had an idea the other day and I wanted to run it by you guys. The reports can be categorized in a hierarchy (like everything else in this world of course):
=> Type
=> SubType
=> Name
Here are some examples of what I have currently:
Types => Finance, Customer, Services
SubTypes => Invoicing (Finance), Proposals (Finance), Customer List (Customer), Recent Customers (Customer), Service Types (Services)
Name => Print Invoice (Invoicing), Print Proposal (Proposals), etc, etc...
What I was thinking of doing is creating a few tables with relationships based on this, then one form from a query (that joins all the tables together) that serves as the report generating form. I would drive the cascading combo boxes by a bunch of hidden fields on the form itself.I think this would be much better than just writing a bunch of SQL statements behind combos on an unbound form. Wouldn't it?
View 6 Replies
View Related
Feb 10, 2006
Dear all, is it possible to run a query (or ideally a report) based on a selection from a drop down list in a form? (in accsess 97). The reson being is i have a big list of of codes for different absence reasons & at present the user has to type in the correct code to display the data in a report based on the code they enter, however its not easy for users to remember which code to type in to run a report.
Idealy what i would like is a drop down list with the absence codes & the absence desciption next to each code, then when the user selects a code from the list it displays the corresponding data in the report?
Is this possible?, any help would be much appreciated, thanks.
View 4 Replies
View Related
Jun 30, 2005
Hi guys . I created a form that has 3 combo boxes. Their name are Project Number, year and weekno . It has also a button that on click action supposed to generate a report baced on my combo box criteria and then loads that report for me. could any expert show me an example on how to do this. I have difficulty finding an example in google since i did not exactly what is called this method of generating report in access 2000.Thanks
View 2 Replies
View Related
Jun 16, 2015
I am trying to get a report based on a selection from a combo box. The issue is many fold. Firstly I have been playing around with it so much that I have messed up the syntax and can't remember what I had. Here is what I have at the moment (this is based on a button):
DoCmd.OpenReport "RepClassrooms", acViewPreview, , " Class = '" & Me.cmboRepClass & "'"
The second issue is that it isn't filtering on the combobox - it gives all the data from the database based on all the entries in the combobox but this is probably down to the syntax again.
View 2 Replies
View Related
Oct 19, 2015
I'm having difficulties with building a printing option for specific goods selected on the list.
I have a form which contains a subform. On the main form user is able to select supplier and based on the supplier can choose article that he's interested in. On the subform user gets the list of all the goods that are connected with that specific article.
I want be able now to select specific goods from subform list (using checkbox value) and based on the selection print a separate label for each goods.
View 4 Replies
View Related
Dec 10, 2013
I am using access 2010...and i have a form with a combobox on it...and in that combobox shows a list of employees names. When i currently select the name of the person that i want, it creates their own individual report of their workload.What I want to do is to be able to select that person and it generates their individual report and then attaches it in an email to that individual.
View 9 Replies
View Related
May 9, 2013
I have some code that was written by someone else. I am trying to duplicate this in another database but, can't seem to get it to work. I also don't understand how this code is working as as there is no reference to the combo box in the code behind the print button. But depending on what you select in the combo box the report opens and filters on that selection. The following code is behind the print button. There is a separate combo box, that does not seem to have a connection to the print button. It just has a query behind it for the items to select from.
Public Sub BTNPrint_Project__Job__Numbers_Click()
On Error GoTo Err_BTNPrint_Project__Job__Numbers_Click
Dim stDocName As String
stDocName = "RPTProject Number (Job) Book Year"
DoCmd.OpenReport stDocName, acPreview
Exit_BTNPrint_Project__Job__Numbers_Clic:
Exit Sub
Err_BTNPrint_Project__Job__Numbers_Click:
MsgBox Err.Description
Resume Exit_BTNPrint_Project__Job__Numbers_Clic
View 14 Replies
View Related
Apr 18, 2006
Hi
I need to print a specific form based on the selection made on a combo box.
I have been able to set it up to open the relevant form when the user selects an option from the combo box, but am unable to find a way to be able to print the specific report based on the selection.
I have a command button which prints a report based on the data in my current form, but would like it to also print the specific form based on the combo box selection.
Is this possible, and if so how is it possible?
Thanks in advance,
me
View 5 Replies
View Related
Jan 29, 2014
I am struggling to pull out a report based on the combo box selection. I have NOI database and want to pull out a report for a specific customer that how many NOIs are generated by this customer..
I have a separate table of customer and another table of NCRs and the customer information comes from customer table.
I have tried the below statement but gives me an error message:
code for Macro that pulls the report in click event:
[CustomerName]=[forms]![CustName]![combocust1]
do i need to mention the report as well in some where in report ?
View 14 Replies
View Related
Sep 24, 2013
I am trying to run a report based on a combobox selection. I have three different reports, each for the three different items in the combo box. I just don't know how to code it so when I run the report, it picks up the name in the combo box and opens the correct report. This is what my code looks like for the button that will run the report:
Private Sub Command7_Click()
DoCmd.OpenReport ("Signers Authorized for Check Writing"), acViewPreview, , WhereCondition = [BTrans] = "Check Writing"
DoCmd.OpenReport ("Signers Authorized for Stop Payment"), acViewPreview, , WhereCondition = [BTrans] = "Stop Payments"
DoCmd.OpenReport ("Signers Authorized for Wires"), acViewPreview, , WhereCondition = [BTrans] = "Wires"
End Sub
The problem is, that as soon as I hit the Run Report button, all three reports open up, so it is not reading what is in the combo box. The quotes, "Check Writing" , "Stop Payments" and "Wires" are the actual selections in the combobox and [BTrans] is the name of the combobox.
View 4 Replies
View Related
