I have reports, that are based around dates. Is it possible to make a report highlight different report outputs according to age?
In other words, if an entry is older than say 15 days, highlight it in blue, if its older than 30 days highlight in orange etc...
Is there a way to only highlight the last row of the detail section in a report?
I tried the following code in the "Format" but could not get it to work in Access 2010.
If Me.ClaimStatuses = "Total Potential Recoverable" Then
Me.Section(acDetail).BackColor = vbYellow
Else
Me.Section(acDetail).BackColor = vbWhite
End If
Where "ClaimStatuses" is the control text box and "Total Potential Recoverable" is the value I want to equal so this row which is the last row will be yellow.
I am exporting a report from access 2010 as PDF to email and the email attachment is coming highlighted. I need it to be white like the report when seen on screen with alternate rows to NO. if I export it to PDF and view it from within access it comes perfect, but when exported to an email, its coming shaded.
View 1 Replies View RelatedSo i have two fields I need to tie together for conditional formatting
If the "status" field says "RETURN" and its over "30" days then I need to highlight it RED
If the "status" field says "AVAILABLE" and its over "90" days then I need to highlight it RED
Is this possible?
I'm building a report which should highlight/change text color in field values in a modified table which have changed relative to field values in an original table. I'm using the same conditional formatting on all my fields: e.g. on the field named [Primary Instructor] in the report on the modified table, I have "Value <> Reports![F2015original]![Primary Instructor]", where I reference an identical report on the original table. Both are loaded.
My problem: it's reading all values as changed/different, even when both visual inspection and copy/paste indicate that the values are identical. Thus my report highlights all values for the field, rather than only those which have changed. The reports have identical non-conditional formatting and data type.
I built an expression for the control source in Access 2007 report as follows:
=Sum([Weighted Value])/(Sum([CourseInfo.Credit])-Sum(IIf([StudentClassRecord].[Grade]="P",[CourseInfo].[Credit],0)))
This is to calculate the GPA. However, sometimes the denominator can become 0 and an error us returned. I would like to get "NA" in return instead.
All the field names are derived from table or query. I tried to aggregate the IIF function but ran into error message.
I recently took over a DB that is based on switchboard form upon opening. Haven't done anythng with switchboards, my question is.
On my report that I have open up I would like to filter between 4 conditions. Would it be best to setup buttons in the report after it is run, or have something in the swithboard code that will filter before it gets to the report? I have looked at the code for the switchboard and it looks "greek" to me.
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
[code]...
I am editing a database that provides the option of creating custom reports, where the user can input a date range of their choice and receive aggregate data for that time frame. Although all of the numbers in the report are correct, I am having trouble with a chart that I inserted into the report.
Specifically, if the date range requested spans 2 calendar years (i.e. April 2014 through January 2015), the data for January 2015 appears at the beginning of the year (so the chart x-axis is for Jan through Dec, and the Jan 2015 data is showing up in Jan (as if it was 2014, not the end of the given range in 2015). When I try with smaller time frames within a calendar year, it adjusts just fine (i.e. shrinking the window so just March-May is displayed on the graph).
How to adjust the axis so that it properly records the data range- so that it would start the axis with April and end in January, for example?
In my tables i have used calculated fields. one of the fields is to "total expenses." In a report, i need to show the sum of all the "total expenses", the filed populates in the report but the cents are missing. for example if the amount is 6080.40 it shows as 6080. how can i get around this? I have tried changing the decimal point value to 2 at which point the value turns to 6080.00 when it should be 6080.40 (i am a beginner at this i am assuming the answer will probably involve c++ or visual basic's, two concepts i am not familiar with.)
View 2 Replies View RelatedI am still trying to get a hang of development in access 2010.
I would like to design a form with a listbox or a combobox which holds all 8 of my reports (a table has all the reports), with a Print and a Preview view buttons. In addition, the user must be able to select if they want to view the report by month, quarter and the year in question.
How do i have a specific report print or previewed based on the value selected in the listbox or combobox and the date criteria.
I have a form that the user can add Work Order numbers to a text box and pass them to a listbox to collect 1 or more values. Each of which need a separate report with the labour hours for each Work Order.
I am having issues figuring out how to get it to pass them to a query or filter the reports.
I have tried many different examples and nothing seems to work.
I print a report onto labels (Avery 5960: 3 columns of 10 labels on a letter sized piece of paper). The report and labels print fine BUT...
I need physical page breaks between certain sections. When I click on "keep whole group together on one page" in the "group by" section of the report, it starts a new COLUMN of labels (which Access sees as a new page), but does not force an actual new piece of paper.
How can I force a real page break?
I have been tasked with creating a database to log employee suggestions and then automatically present reports around the site. I have developed the database and it works OK and can output *.PDF reports, but I'm having problems with how to display these reports. My idea was to output the reports automatically as *.TIFF or *.JPG files and have them stored in a folder that the My Pictures screensaver uses. The reports (in fact, any site report stored in this folder) can then be shown on any screen dotted around the site when it goes into screensaver mode. However I can't find any way to output/convert to a picture file.
The other option is to create a webpage that rotates through the saved PDFs, but this isn't ideal as the PC users will have to load the webpage to display the reports.
I have date picker which works correctly in form. When I put that form as subform to reports header calendar shows up but after selecting date on calendar textbox stays blank. Format of textbox is Short Date, Show date picker property is For dates.
View 1 Replies View RelatedI have a database of high-school football players, and I am looking to print out single page reports (or forms) that will show detail from several tables and queries. This will act as their resume when they visit schools on recruiting visits. The reason for needing query items, is that I have developed queries that return the most up to date height, weight, 40 time etc., and that single most up to date number is what should print, not the entire table. When I try to build a report it will let me bring in multiple tables, but not queries.
View 2 Replies View RelatedI have a continuous form that displays a list of invoices (frmInvoiceList) based on an adjustable filter contained within the form. I have the filters working the way that I want them through VBa and I have a button on each record to open a report (rptInvoice). Inside that report, I have some controls to "print", "email", and "export to PDF". Again all that works just the way I want. The Form and the Report are based on a different query and each has an InvoiceID field to link.
What I am trying to do is put the Print, Email, and Export buttons on the main form which would perform the appropriate action for all the records that are displayed on the form. I've been able to get the Print function to work to a degree. It will print all the records, but it changes the background colors based on the alternating records. When I go to Email or Export, it creates a single file with multiple pages and each page is a new record, again with the alternating background colors.
Ideally, I would like to have a separate file exported for each record that can be renamed and archived separately. I'm not so concerned with the email function but if it would be possible to generate a separate report for each invoice, then pull the appropriate email address for the record, that would be really nice.
I've tried some "for" and "do" loops that I found through some Googling but none of the samples ended up working like I wanted.
I am trying to create a P&L statement in access. I know what I want to come out at the end. I am just starting to play with access and having trouble getting what I want to come out of it.
On the sales side I have a query that gathers all the revenue sources and calculates a total for each date. I then use a second query to just take out the data I want for the P&L report. I created a sub-report that displays the data I want. I use the grouping and grand total features to get the total into the report footer. So far so good.
On the expense side I created a form of a query to manage the one to many relationship to capture the data for expenses (one purchase with many line items). I created a query based of this query to get the relevant data for my expense sub-report. I created the sub-report and got everything looking and calculating the way I want it to. I use the same grouping and grand total features to display the data in the report footer. Still good.
I created a new main parent report with the two sub-reports (sales & expenses) on it and even was able to pull the totals from the sub-reports into the main (so currently the subtotals of the two sub-reports are displayed twice). Now when I try to use the textboxes I used to pull the sub-report totals into the main report to perform additional calculations (sales - expenses) I get #error. I have tried different things and gotten ?name.
Control source for the two textbox controls on the main that display correctly, but don't let me do any further calculations.
=[rptP&LExpensesOverview]![AccessTotalsAmount]
=[rptP&LSalesOverview]![AccessTotalsTotal Sales]
To do the subtraction I have tried using the references above, as well as just using the names of the unbound text boxes in the report that bring the totals into the main report.
As a work around, I tried to build one query with all the data from sales and expenses, but can't "filter" based on date and get the data I want in the query results because the two sets of data are not necessarily related. I either get a long list of records, or no records (I am currently only playing with about 5 days of data).
I am using a form with command buttons on it to set the criteria in my query to run the report.
Example : cmdQ1 'Command Button
PHP Code:
'1st Quarterly Report.
BeginDate = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 1)
EndDate = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 4, 0)
I managed to get this far but need to continue on with 2,3, and 4th quarters.
I am trying to pass a date range parameter & an additional parameter (Type of Audit) to 6 subreports based off individual crosstab queries and housed on one Unbound Report ("rptFinal").I have an Unbound Form "frmDate" passing a date range and Type of Audit using [Start Date] and [End Date] to rptFinal with a button that simply opens the rptFinal as follows:
stDocName = "rptFinal"
DoCmd.OpenReport stDocName, acViewReport
In each Crosstab query, I have set the parameter criteria (in both the Parameter section & the query itself) to:
[Forms]![FrmDate]![Start Date] And [Forms]![FrmDate]![End Date]
and also,
[Forms]![FrmDate]![Enter Type of Audit]
On each subreports On Load Event, I have added:
Private Sub Report_Load()
Me.Filter = "[DteAuditDate] BETWEEN #" & Forms!frmDate![Start Date] & "# AND #" & Forms!frmDate![End Date] & "#"
Me.Filter = "[Type of Audit] = #" & Forms!frmDate![Enter Type of Audit] & "#"
End Sub
and I've set the Filter On Load property to: Yes..I can open frmDate, fill in the date and Type of Audit, launch the report and it runs with no error, I have 6 blank subreports in report Preview. The headers are showing up but none of the data.
I have created a report which contains within it multiple sub-reports, which I use to generate a document for management meetings on a bi-weekly basis.
For each of these I have the subreports filtered to a unique number for consideration that period e.g. LIKE "88/00039" which relates to my data.
In order to change this I have to manually update each of the filter commands within the subreports but I assume there must be an easier 'catch-all' method of achieving this?
Ideally I'd be looking for a command prompt so I could enter just the number sequence e.g. "88/00040" and then enter this (via a corresponding macro or similar) to update the filter commands.
I have four forms named [Information Form], [Evidence Form], [Subject Form], and [Vehicle Form]. The Final Form is a Tabbed Form named [New Incident]. The tabbed form has four tabs, with each tab displaying one of the forms above. I also have a Print Command Button named [Print All] underneath the tabbed portion of the [New Incident] form.
Also, All of the four forms are set to data entry - yes, so that on open they will be clean and read for new record entry. I need the [Print All] button to do, just that, print everything that has or has not just been entered into the four forms, even if some are printed blank because they were not necessary for this particular report. One thin of note is that each form on the tab has a field for the "Incident Number" which will be the same for all four forms.
I am trying to add to a db I inherited. One of the end reports that is produced is a cost breakdown for each end user.As things stand, the data collates into individual reports which are then grouped into one file and saved via PDF. What I am trying to work out is whether or not I can selectively pick some of those reports to have more than one copy.
I see a form (within an existing form) that will list all of the end users for a particular scheme and, next to that, be a dropdown that will allow the db user to select how many copies of each report needs to be published. These will then collate merrily into one document to be saved to PDF.
I have a queries that do all the calculations and dumps the output to Query X for all different types of customers. At the moment I am required to generate a report for each of the customers and send it to them, manually.
End Goal:Initiate a Macro (at a given time at a given frequency) that would run a process to generate different reports for all different types of customers using a standard report template. I am also trying to avoid having to create a report for each customer (as the customer base grows, the report count will grow) so looking at something that would look into Query X and generate # of reports depending on number of customers.
I'm working on a project where I must send roughly a thousand individual reports to a thousand different email recipients with .pdf attachment.I know I'll need to add a table that designates the email address by SHIP_TO_CODE. Then create a form with the email subject, body, etc.. I don't neccessarily have to have the reports saved to a folder; I really just need them emailed to each account.
I was thinking I could modify the code some to accomplish my goal, but I'm not sure what to put. Maybe add a SendObject in there somewhere.CODE that I have now that saves the report to a folder. (I want to modify some to send email attachement instead)
''Module CODE:
Private Sub Form_Current()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [SHIP_TO_CODE] FROM [qryWty&PendingData];", dbOpenSnapshot)
Do While Not rst.EOF
strRptFilter = "[SHIP_TO_CODE] = " & Chr(34) & rst![SHIP_TO_CODE] & Chr(34)
DoCmd.OutputTo acOutputReport, "rptDraft", acFormatPDF, "C:UsersmrutherfordDesktopASC Daily Reports" & "" & rst![SHIP_TO_CODE] & ".pdf"
[code]....
I am trying to produce statistics reports in my database. The user selects the information they require in a form (date from, date to etc). A query then produces the results listing each job in turn which is what I want however at the end I would like to produce sub-totals for each client.
Depending on the criteria selected by the user, only some clients will appear in the list. Ideally I would like the footer to show something like:
Client 1 - 4 jobs
Client 2 - 1 job
Client 5 - 6 jobs
Client 6 - 17 jobs
I have tried using the field:
Code:
=Count([ClientDetailsName])
However this only gives the total number of jobs.