Form For User To Create Custon Reports From Multiple Criteria
Jan 4, 2006
I am trying to put together an form to be used in an academic administration database.
Ideally, I would like to create a form that will allow a user to select criteria and get a list of all students fitting that criteria.
For example:
Show me all a) all Economics majors with b) 30-50 Credits who c) have not taken English 101.
All of this data could be compiled into one query, but I would like to create a form that will allow a user to select what data he or she would like to see.
(Maybe they only want to see graduates)
I hope this makes sense.
And I hope someone can help point me in the right direction.
I need to create a query that has multiple criteria selected from a form For example. I will always have the customer name but then the user might also choose any one or more of the following, start date, end date, number etc
I have tried to create a select query and added the customer form element to the criteria but I am not sure how to do the rest because it will always have at least one more criteria but I am not sure which one and it may have up to 5 more criteria.
I have a user form with six different dropdown boxes. I would like to create a query that gets its criteria from users selecting values from one or more of these boxes.
I have tried:
[forms]![frmName]![comboboxname] or [forms]![formName]![comboboxname] is null
in the corresponding query fields and get a "query is too complex" error message, even when only selecting one criteria.
This works great to return a report if the user selects something from the combo box. How do I adapt this so that the user can also leave the combo box blank and filter the report to return all records?Additionally, what if I want to have the user filter between dates selected on the form; i.e. between 'txtStart' and 'txtEnd'
I have a normalized database with (let's say) the following tables:
tblProjects tblStaff tblProjectPhase tblOffice
I need a way to allow the end user to be able to create custom reports that show only projects in certain phases (let's say planning and construction) AND that are from certain Offices (let's say Office 1 and 3 but not 2) AND that were started between a certain date range.
What I'm envisioning is a checkbox-style form that has a 'create report' button at the bottom.
I managed to create a report by user using a combo box. When an user selects a name from the combo box, it generates a report showing all the records by that name.
However, now I need to be able to generate a report as above but between 2 dates. How??
I am having two sets of criteria. 1) by user 2) between 2 dates
I have list of candidates shown in report. I need each candidate to have a unique number based on user input. And this number increases by 1 for the rest of the candidates in the report.
Example is below:
If user enters 1132 in the user input box then the numbers shown in report should be as below:
Name Assignment Number
Felicity Thomas 1132 Andrew Sen 1133 Andy Luker 1134
Similarly if number entered is 2345 then the numbers shown in the report will be:
Name Assignment Number Felicity Thomas 2345 Andrew Sen 2346 Andy Luker 2347
I have a query that prompts the user to display certain/all of the members in my member table. The problem is, using a parameter query in the memberID field only allows the user to send the message to one member, not multiple members. "1 or 2 or 3" or "1,3" obviously don't work. I've tried creating a form with a list box to select the members (which I;ve done) but obviously you need code and stuff to get it to work which I think will be too complicated. Is there a simpiler way or is the form the only thing that will work? Thanks Kris
Alright I've been picking my brain for the past hour trying to figure this one out...
I have 3 tables... 1) Retailers-RetailerID, RetailerName, address etc. (500 entries) 2) Distributors- DistributorID, DistName (10 entries) 3) Uses- RetailerID, DistributorID
Sample data is as follows for 'Uses' table: RetailerID DistributorID 1 1 1 3 2 1 2 6 3 4 3 5 4 8
I am trying to create a search form based on a query which will return retailer information for those retailer who use multiple distributors.
For Example...There will be 2 text boxes and a user can enter a distributorID in these text boxes. If only 1 distributorID is entered, it will display records for that one.
I have tried the following but I have been unsuccessful:
SELECT Uses.RetailerID, Retailers.Address, Count(Uses.RetailerID) AS CountOfDistributorID FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID WHERE Uses.DistributorID = 1 GROUP BY Uses.RetailerID, Retailers.Address HAVING Count(Uses.RetailerID) > 1
Is this possible to accomplish with the way I designed this database? If so, can I get a little advice? I know this is a long question that can't be answered in full but I would just need to basics in order to be on my way. Thanks in advance!
I created a form that allows the user to choose the criteria that they want to see on a report using =Forms!formname!controlname in my query. It works great but I want to also allow the user to choose nothing and return all records instead of limiting them to choose just one type of record. Is this possible? Before I created the form my query had the [Enter parameter] on one criteria line and [Enter parameter] Is Null on the next criteria line and that was working great for my use but I need to create a simple form for other users.
I am having trouble creating a query where I am trying to count number of records for different fields for a particular criteria, and combine the results into a single table.
My table is in the form, TimeandDate,WS127m_Avg,WS82m_Avg....
I want to count those records where a 9999 is reported, and report by month. For a single field I can do this OK using,
SELECT DateSerial(Year([TimeandDate]),Month([TimeandDate]),1) AS [Month], Count(WS127m_Avg) AS 9999s FROM CL_AllData WHERE (((WS127m_Avg)=9999)) GROUP BY DateSerial(Year([TimeandDate]),Month([TimeandDate]),1);
I can't figure out how to report an additional field (WS82m_Avg) at the same time, checking for the same criteria in that field (i.e. WS82m_Avg = 9999).
creating a DLookup expression for an unbound control on a report. This report displays data collected from the users from a table called tblaudit completed. This table contains (among others) three fields labeled below
Brand Form Area Changed
I also have another reference table labeled refevalareas. The fields in this table are as follows
BrandName FormName AreaName PointValue
I would like to create an unbound control on my report that "prints" the point value depending on the Brand, Form, and Area displayed for each record. Though the field names are different the data collected and displayed in each table is consistent. I haven't worked much with Lookups and the logic is challenging for me to grasp but I think I have the basic idea which is shown below..
I have a list of projects and I need to display their status (Red or Green) in a text box.
My fields are [PercentComplete],[StartDate],[EndDate],[ReportDate]
There are two ways a project could have a red status.
1. [PercentComplete] < 100 AND [ReportDate] < [EndDate] OR 2. [PercentComplete]<100 AND IsNull([EndDate])
There is one way it could have a green status: [PercentComplete] = 100 AND Not IsNull([StartDate])
This is the best I could come up with for the Iif statement, but I get "invalid argument" which I suspect relates to the AND portion.
Code: Iif([PercentComplete] < 100 AND [ReportDate] < [EndDate], "Red", [PercentComplete]<100 AND IsNull([EndDate]),"Red", [PercentComplete] = 100 AND Not IsNull([StartDate]),"Green","")))
I have a database which I have been using for over a year now which includes a Dlookup formula in a report.The dlookup references the query QRYDwgCount and looks up the number of drawings issued by a particular architect. I have been using the following formula which was previously working
The problem I have now encountered is a new job with an architect that worked on another job already in the database. Instead of bringing forward the number of drawings issued by the architect for this current job, the formula is showing the number of drawings issued for the earlier job. I therefore need to add "ContractName" = [ContractName] but I am getting either Error or an incorrect answer as the result.This is what I have written:
I need to know if possible to create multiple queries or reports at the same time. We have large table that is updated monthly this report gets broken down manually by Manager (30 managers in total) hen email to each manager.
Report or query specs will never change only the data I could do this manually create and save query/report for each manager once but I was wondering if there was code that will create all the queries and reports at the same time.
I am in the learning phase for MS Access reports. I have a datasheet which contains the employee daily activities.I need a report when either or both criteria is selected. For ex..criteria1 is Start date and End Date and criteria2 is Employee Name.Output of the report should be based on the selection of the criteria ,as I said either or both.
I have a table named z_ResltsSampleCountMonthly. In that table I have fields SampleSize, Month and LOB.
I have a report that I need to use a Dlookup in order to pull the SampleSize by LOB into my report. However, my report has three columns that change based on the start and end dates for the report. It shows current month plus the two prior months to show a trend.
So I have each column heading update automatically based on the dates the user enters into the start and end date on the main menu form.
If they select 12/1/14 to 12/31/14 the three columns heading would update to October 2014, November 2014, and December 2014. In the table I have the data for all three months so when I use this formula it works but it's putting September 2014 data under October 2014 so I need to have multiple criteria; one being the LOB and the other being the column heading which is equal to the field Month in the table.
I have a report which I would like to apply conditional formatting on multiple fields. I would like the conditional formatting to be based on two types of criteria
First criteria would be contracts that start with the year 2014. I used the following expression which worked - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Joseph Steinbok" or "Adam Godson")
To this expression I would also like to highlight fields which contracts start with 2014 AND have a particular Director assigned to it. For this I used the following expression - And [DirectorInCharge]="Name"
On their own, both expressions are working but I want to combine them. How do I do this? I've tried the following - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Name") but then nothing is highlighted. I also tried InStr(1,[ContractNr],"2014") AND [DirectorInCharge]= "Name" - in this instance EVERY record was highlighted.
The user will be creating a new project that contains a bunch of releases. The releases have standard names which are stored in a table tbl_ReleaseNames It should be noted that the list of names is not static.
The user selects which of the releases pertains to their project and then based on their selections, new records would be created in tbl_RFP_Release and then a subsequent form would open where it would display each of these newly created releases where they could enter additional information. I thought of creating an unbound checkbox associated with each of the standard names, and then checking to see if the checkbox was checked and then creating the new records followed by opening up the new form.
HI Everyone, I searched the forums, and i got one solution but, still i want a help. i have a menubar created using customize command. Now i want to make enable & Disable some of the commands in that menu. I got following code for enbling or disabling the menus on custom menu. but i want to disable the commands in the menu, how to do it?
Is there a way to create a form that anytime i open it , i enter the data, when i hit save. the fields in the form goes blank and the data is saved in the table. Also when i open the form again, and repeat this process , it just adds to the table and does not delete previous data.
I am trying to create a user form with sub form using the form wizard. I have only 2 tables, Employee (main table) and Vacation (subform table). I pick the fields from Employee Table then fields fro Vacation, but the wizard treat the Vacation as main form and Employee as sub form.
I have been trying to create a login form that allows the user to change his/her temporary password logging it to the proper table along with timestamp and who done it info.But, after spending the morning trying to find the proper syntax I am flummoxed.
I can get everything to work accept the update of the fields. I can get the command to work (writes to the location) but it does the pop-up what is the parameter thing when it works. I have all the information just need to get it in so the command recognizes it.
DoCmd.SetWarnings False DoCmd.RunSQL "UPDATE lut_TeamList SET Pass = txt_Password.value WHERE TeamListID = Me.cbo_UserName.Value" DoCmd.RunSQL "UPDATE lut_TeamList SET UpdatedBy = Me.cbo_UserName.Value WHERE TeamListID = Me.cbo_UserName.Value" DoCmd.RunSQL "UPDATE lut_TeamList SET UpdatedWhen = Now() WHERE TeamListID = Me.cbo_UserName.Value DoCmd.SetWarnings True
I have been an MS Excel man all along my career and I am a novice in MS Access.I have created a table, [Initial Customer Approval] which records data from a Form, [Initial Customer Approval]. Once the data is entered in the Form, I need to do some calculations based on the data entered in some of the fields in the form.I created 6 different queries for the six possible values in those fields. now for each of those queries I created respective reports.I placed a Print command button in the Form.
1. When I press the Print button it should open the report for the current record in the Form. (Currently It Opens all the reports simulatneously, with only one relevant report containing the current record; other opened reports being blank.)
2. If user presses the Print button before pressing Save button then system should prompt user.
Here is the code (Please note [reference number] is the unique ID generated for each record entered in the tabe through form):
Private bSaveClicked As Boolean Private Sub Form_BeforeUpdate(Cancel As Integer) If Not bSaveClicked Then MsgBox "You are trying to navigate away from the active record. Please either save your changes, or press ESC to cancel your changes.", vbOKOnly + vbInformation Cancel = True
I want to create a form that allows a user to enter criteria that will be passed to an existing make table query. suggestions on a user friendly book on Access 2010 programming, I'd be really interested. I'd like to be able to do more with Access 2010.
