Reports :: Sorting And Formatting A Report Based Off Of Values And User Input?
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:
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.
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 would like to change the text formatting (color, italics, bold etc) of the contents of a control based on a boolean value in the underlying datasource of the report.
For instance, I have a report that generates a "Proforma Invoice" i would like to ability italicize the prices of certain items based based on a boolean value (EstimatedPrice) in the underlying datasource.
I have to print a label quickly every time that the product hit the warehouse. The label has been created as a report linked to the query that will provide the info to the report. In order to make this report printing as quick as possible the idea is to scan the sample id from the product and once the label is printed scan the next sample and an on.
I'm not an expert on VBA but I have created the following scrip but the reports doesn't pop up.
Here is the code:
Dim SampleID As String SampleID = InputBox("Enter Sample ID") If SampleID > 0 Then DoCmd.OpenReport "rptGRM_QuickPrintLabelDymo", acViewPreview, , "[Sample]=" & SampleID Else DoCmd.Close End If End Sub
I am quite new to Access and have been working with a sample DB from Microsoft. I have been looking for some ways to filter a report based on the values in some comboboxes, and have followed the instructions on a website (can't post link yet).
I also looked at this file (**can't post link yet**) and it seems like everything works fine here. So I started a new DB with my own table, and tried to do the same thing as in this file.
Everything works fine.
But when I try all these steps in my own DB (the sample DB from Microsoft), it doesn't work.
When I filter by "Status" (f.ex: "In Progress" or "Not Started"), it seems to work fine.
However, when I filter by "Assigned To", I get the following error message: "Data Type Mismatch in criteria expression". So I googled this error message , but this field has nothing to do with any date formatting.
It gets even stranger when I try to filter by "Project":
Then a new pop up appears and asks me to enter the "Project".
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'
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.
Please could somebody point me in the right direction of code.
I have a database which I would like to query using a form with a combobox or two, each of which containing a list of eg. Customer Names, or Reference Numbers, which will then produce a report which can be printed out.
I know basic ADO, like get the database, open it, find records, update and delete records, however I have been unsuccesfully searching for the code to open a report, which will respond to certain sql parameters that correspond with the values in the combobox. And then be able to print that report if possible.
I would be much appreciative if anyone would point me in the right direction, Thanks!!
I have a database table in which I'm trying to pull sales data and generate sales reports from. The problem I face is that the sales data is recorded into a table with this structure:
Year | Customer | Sales_Month_1 | Sales_Month_2 | Sales_Month_3
Rather than having a single field "Month" in which I could set criteria or prompt the user to select a month to derive sales data from, I need to write a query that through user input (or through some code within the query) the right field will be selected. An example of this would be generating a report for the 1st month of the year, obviously.
I have a form in my front end database that is supposed to allow a user to search for a record based on account number and then make changes to the that record and for it to save in the back end database. I have the search function working where it populates different input boxes on the form with what is stored in the back end database but I cannot get the update function to work. I have tried to assign each input box with a variable and then run an Update SQL function to update each of the fields but the updates are not storing. Any example of a successful update statement that uses VBA variables in it or a way to update a specific record via a recordset type function?
I run a charity pre-school and have invoices to send out. Some of the parents cant afford to pay the whole amount in one go, so we give them a payment plan. e.g. If an invoice is for a 6 week term we let them pay weekly. So I have a check box on my form to say "are you on a payment plan".
Then - I have an free form input box..."How many payments..." and the answer may for instance be '6'.
What I want to do now is dynamically create/display 6 date fields, to record what the agreed payment dates are.
but maybe the answer is 4, or 7, etc. So I need to create/display the correct number of payment date fields.
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.
I have a query showing lots of records of equipment that is in different rooms. I would like to have a form where the user can bash in a room number and all the records from the query that have that room number in it pop up.
hi everybody, im have a database with table called "project". there are many column in this table. my user want to export this table to Excel, but only some of column, with particular order ( depend on him) to analyze in Excel. he asked me to build a form with a list box, drop box,somthing like this, so he can choose what column to export in what order. i try to make a query like this: " Select Forms!UserInput.combobox1.value , Forms!UserInput.combobox2.value,etc, From Project" but it wont work. Dou you have any idea. thanks in advance
I have a search form that looks up a value in a field and lets the user know if it is there or not, they have now asked to put in multiple values to search... eg. searching containers number, I will put user input box where they can put these numbers
NYKU023561 TRLU102356 TCNU123023
This will search my container number field and show a pop up message box saying.. Please note below NYKU023561 - has been found you cannot use this container TRLU102356 - has not been found please check internal system TCNU123023 - has beeen found you cannot use this container
Below is the basic search I had before which was all I needed.. how can I adapt this?
Code: Private Sub Command256_Click() If (CntSearch & vbNullString) = vbNullString Then Exit Sub Dim rs As DAO.Recordset Dim CNT As String
I'm attempting to build an import module so that my users can take data from different walks of life and import it into my tables. I'm doing this by setting up a module and allowing the user to specify which column data will come from when importing data. So lets say I have a field that is CustomerID in my table and user one pulls data from 1 place and in his excel or csv file, customerid is in field 1. Another user does the same thing and its in field 2.
How can i make some form of a loop that when I'm attempting to update data, i pull the correct column?
strSQL = "SELECT * FROM tblImportTable" Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges) With rst Do While .EOF = False If IsNull(DLookup("DefaultValue", "dbo_tblImportTemplateDetails", "Template_ID=" & Forms!frmImport!TemplateName & " AND FieldName='CustomerID'")) Then
I am new to VBA and I'm trying to write a query that will update a table with dates based on user input. For example a user will run data each Monday and that date will be day01. The date table has 28 days total and I need each day row to update with the next date i.e. day01 is 12/30, day02 is 12/31, day03 is 1/01 etc..I am having issues just running the update the query. I get a too few parameters error message on the strsql statement. There are only two columns in the table, order_day(date column) and date_value(text). I want to update order_day. I also need creating a loop so it knows to go back and add days to the other values.
Here is what I have:
Code: Sub Update_Dates() Dim rs As Recordset Dim db As Database Dim lmsg As String Dim transactiondate As Date Dim strsql As String
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
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!
I have a database that I created that uses a form to fill out information about server builds. I contains information about who built it, the IP address, server name, etc. I want to be able to create a dailog box that allows the user to input a server name to query the database and create a report based on that input. And if the user input isn't found in the database, a dialog box comes up with some sort of error message. I am still pretty new to Access, so the more detail you can give me the better. Thank a lot.
I have a report that requires the user's input for a field called UserID.
I also have a form in which I want to have a button that can be used to print off this report (which would involve automatically entering the UserID into it). How do I do this?
Here is the code for the form button that I am using at the moment, but when you click it, Access still needs the UserID (duh!). So, how can I automate the input?
Code:Private Sub Command25_Click()On Error GoTo Err_Command25_Click Dim stDocName As String stDocName = "Menu" DoCmd.OpenReport stDocName, acNormalExit_Command25_Click: Exit SubErr_Command25_Click: MsgBox Err.Description Resume Exit_Command25_Click End Sub
I am having a problem with the formatting of a report. My boss would like for the report to show (grouped by employee name) a horizontal list like below and update itself.
Each training class has a category in the table showing which model(s) the training is. For OJT I got it to count up the tasks and tasks completed in each model and calculate the percentage. I can easily get it to show vertical for each employee. I cannot get it to show like above. I tried using IIF statements to pull the hours and OJT by model aircraft. If I try to put it in the detail section it shows vertical and if I put it in the group of employee name it only returns one record (I assume its still trying to show vertical). The only way I could think of to get it to show properly was a crosstab query but its my understanding those are for archival purposes or for a snapshot and do not update. This report will need to update as things are entered.
A client wants to convert a report I designed for him, to a word document so he can edit it.
The report's Report.Orientation Property is set to 1 - Right-to-Left
Relevant TextBox.ReadingOrder Properties set to 2 - Right-to-Left,
and relevant TextBox.TextAlign Properties are set to 3 - Right
When exported to RTF, the document is LTR and text alignment is to the left (even though the page actual layout is seems RTL, just like the report).
Is there a way to enforce RTL layout in the exported Word file?
I am using Acc2010.
Edit: Iv'e added a sample word file. You can see it looks RTL, but is actually LTR. Set it as RTL - and it gets all disordered. Now multiply this by 47 pages...
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.
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.
