Reports :: Average Contents Of A Field Based On Another Field

Oct 2, 2013

I have a report that has multiple fields - 3 of which matter for this discussion -

1.) employee
2.) employer
3.) rateofpay

The employer field contains 1 of 2 options. For the sake of this conversation, lets call it ABC123 and XYZ123

I need to be able to calculate the average rate of pay for ABC123 company, and exclude the rate of pay for XYZ123 company in my report.

I am calculating this in a section footer.

View Replies


ADVERTISEMENT

Contents Of A Field Based On The Contents Of Another Field

Jul 14, 2006

Hi,
I'm making a form where the contents of a field is determined on the contents of another field in another form. I thought an IIF function would work, but when I tried it the contents says #NAME.

I put it in the control source.

IIF([Forms]![Frm_NewBusiness]![Page4]![Child51]="NTU",NewBusiness_Date_Issued="NTU",NewBusiness_Date_Issued)

Any ideas?

Cheers,
Ben

View 1 Replies View Related

Print Text Based Upon Field Contents

Jun 23, 2006

I want to print text based upon the content of the field but need more than the IIF statement. For example, if the field contains an "A" I want to print "Active", "C" I want to print "Closed", "N" I want to print "New", etc.

Thanks in advance for any help. You guys have always had an answer in the past!

View 1 Replies View Related

Tables :: Autopopulate Field Based On Contents Of Another

Sep 12, 2013

I am in the process of revamping an old database. I have a main table that currently has two fields "Name" and "Job" but will have more. I have another table with the same fields. I want to use as a lookup to populate the main table with a user form from the second table. I can populate the "Name" field using a combo box, but my problem is how to autopopulate the "Job" field. E.g. If the user selects Mr Smith from the "Name" drop down list, the corresponding "Job" populates the second field.

View 4 Replies View Related

Restricting Data Entry Based On The Contents Of Another Field

Sep 22, 2005

Hello,

I have two tables which contain the following fields (simplified);

Table 1:
ID
Startdate
Enddate

Table 2:
ID
Unitdate
Treatment

The tables are related (one-to-many) through the ID field.

In operation, the user first enters the Startdate and an Enddate for a patients' hospital stay in Table 1. Following this, the user enters the Startdate again in the Unitdate field in Table 2 and selects a Treatment from a drop-down list. After this, the user enters the following in the Unitdate field and selects a treatment. This is repeated in consecutive order until the Enddate is reached.

The end result looks like this;

Table 1:
ID Startdate Enddate
123456 01/01/2005 04/01/2005


Table 2:
ID Unitdate Treatment
123456 01/01/2005 Ventilation
123456 02/01/2005 Ventilation
123456 03/01/2005 Haemofiltration
123456 04/01/2005 Ventilation

Does anyone know if there is any way to restrict the Unitdate field in Table 2 to only allow dates to be entered that are between the Startdate and Enddate fields in Table 1? Ideally, I would like the Unitdate to populate automatically with the dates, starting with the Startdate value and ending with the Enddate value and filling in all the intermediate dates. Also I would like to make sure that there is no way of having duplicate dates in the Unitdate field.

If anyone can help, I would be extremely grateful.

Best wishes
Russell

View 7 Replies View Related

Put Contents Of Field Off Table On Report Based On Date

May 24, 2012

I have fairly large table called QBInvoices that consist two fields- qbinv and date . Also, I have a report that showing info off query called TotalQueryQB. The table and query have no relations except dates . I need to show on report filed qbinv off table QBInvocies on the top of the reprot based on date. The user type date on form -frmQB text box txt Date and I would like to have contest of field qbinv off table QBInvocies on the top of the report base on date that was typed.

View 2 Replies View Related

Reports :: Total Field Contents Not Visible Until Click On It?

Oct 2, 2013

Imagine that you see a column of values and then a total at the bottom. The only issue is that the total value does not appear until you click on it.

Any setting that would cause a total value in a footer section to not appear automatically?

Behavior is noticed on reports as well as subreports. An image is attached.. Imagine the $37,000,000 simply not appearing until it is clicked with the mouse.

View 7 Replies View Related

Reports :: Get Remarks Automatically In Field Based On Other Field In Report

Dec 15, 2013

I made a report that have 'txtRemarks' field, I just want to get remarks automatically in 'txtRemarks' field based on the other field in report. that is why I used a function like below:

Code:
Private Function Estd_Remarks(Estd_Point As Long) As String
If Me.Estd_Point < 20 Or Me.Estd_Point = 0 Then
Estd_Point = "Earlier Established"
Esle
Estd_Point = "OK"
End If
Estd_Remarks = Estd_Point
End Function

And I wrote in properties 'On Format' event this code below:

Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Estd_Remarks = "Ok" Then
Me.txtRemarks = "Ranked & Sortlisted"
Else
Me.txtRemarks = "Estd_Remarks"
End If
End Sub

When preview the report then it shows
Compile error
Argument optional

View 3 Replies View Related

Reports :: Show A Field Based On Result Of Another Field

Dec 4, 2014

I have a list of all previous employers for a job applicant. I can enter up to 10 but I only want the boxes to show if there is a previous position for that box. So, if there are 3 previous positions, I want to see 3 lines. If there are 10, I want to see 10. I have fields [Company_1]. [Company_2], etc. I only want company 2 to show if the person has 2 previous positions entered into the form.

View 1 Replies View Related

Reports :: Having Field In Report Change Based On Another Field

Sep 5, 2013

I have a report that is looking up min and max quality specifications for a product. The table with the specifications has a Yes/No check box field for when the specs are defined as "Internal". So the table looks like Product/MinValue/MaxValue/Internal

I want the report to display "TEST AND RECORD" when the Internal box is checked where the MinValue and MaxValue (that are stored in the table) would be displayed otherwise.

View 3 Replies View Related

Modules & VBA :: Code To Check Contents Of A Field In A Table Where Another Field

Sep 11, 2013

I am trying create some code that checks if the contents of the status field in a table is "Authorised" where the ID = something specific for multiple records. If all records witht the specific ID are "Authorised" Then generate a new record in another table. Where to start, perhaps a Dlookup?

View 1 Replies View Related

Queries :: Querying Contents Of One Field Against Another Table / Field

Apr 26, 2013

I am trying to perform a search function query in access but am having trouble. I have two tables. One table has a column in which I need to search the contents of this field by referencing data in another field/table. I am able to write the query searching the data manually (not referencing the other table) but can't seem to get the query right when referencing the other table!

this query works =
SELECT *
FROM Sample_Data
WHERE (((Sample_Data.[Affected_Frequencies]) Like "*451.425*" Or (Sample_Data.[Affected_Frequencies]) Like "*451.400*"));

but, i put together a frequency table (Table name = Frequencies) and the column within that I am searching for is called Frequency. im trying to reference Frequencies.Frequency using a LIKE statement.

View 1 Replies View Related

Reports :: Print Two Different Formats Based Upon Value Of A Field

Dec 20, 2013

FoodStamps This is a YES/NO field (printed in the first or second columns).FSAmount This is a numeric field(always printed in the third column).I have a preprint form with three columns.The first column is for a YES answer and I want to print YES if FoodStamps is TRUE.The second column is for a NO answer and I want to print NO if the FoodStamps is False.The third column is either blank if the first column is YES or the actual value if the second column is a NO

Two cases:

If FoodStamps is TRUE then print

YES Blanks
I have a preprinted form and I want the YES in the first column and blanks in the third column on the form regardless of what is in the fieldFSAmount

If FoodStamps is False then print
NO value in FSAmount

the NO in the second column and the value of what is in the FSAmount field printed in the third column.I tried to figure it out with conditional formatting with no luck.

View 1 Replies View Related

What About This Average?...Field Average...

Feb 16, 2007

Hi guys!!!!

I try to find an answer in the forum about "Average Fields",but ican't
I am confused:(

I wan't to export Avg Of the fields like in the panel below:

View 2 Replies View Related

Reports :: Visibility Based On Whether Table Field Is Empty

Jul 11, 2013

I'm having trouble with syntax for using VBA on a report in Access 2007. I need to some hide 3 text boxes if one of my fields is empty in a table. The table name is: 'ALM-RESP' with rows grouped by: 'TAG NAME' and the field I'm checking is: 'Rev 002 Author'. I've tried having a rectangle to cover it up but I'm having trouble using the IsNull command and setting the visibility of the rectangle. Here's my code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull([ALRM-RESP]![TAG NAME]![Rev 002 Author].Value) Then REV2BOX.Visible = False
End Sub

Basically I'm having trouble checking if the field is empty and then setting the visibility.

View 1 Replies View Related

Reports :: Delete / Ignore A Page Based On Field Value

Oct 18, 2013

So I have a report with two pages. One page is about product exchange information and the other one payment. This report can either be printed or saved as a pdf (one pdf for both pages) and e-mailed.

This works fine, however if the item is under warranty then the exchange is for free and I do not want to print/e-mail the 2nd page (payment).My trigger field for this is "Warranty" which can be "Yes" or "No".How can I prevent that 2nd page from printing/e-mailing? Can it be simply skipped when the report is being created based on my field?

View 2 Replies View Related

Reports :: Showing Image Based On Another Field (Date)?

Apr 8, 2013

I need to show an image called PAID if my hidden field Paid Date has a date in.

I thought this would be straight forward but so far nothing.

I have my images within the database itself on Access 2010, so I am not referencing any in a C drive or other location.

Code:
Private Sub Report_Current()
On Error GoTo ErrHandler
If paiddate = Date Then
Paid.Visible = True
Else
Paid.Visible = False
End If

ErrHandler:
MsgBox "Error detected, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub

But with no joy.

View 8 Replies View Related

Reports :: Pull Information Based On Group Field?

Jul 21, 2014

My report is grouped by a Billing Group #...within each billing group there are sub invoice numbers that are tied to the main billing group # (they all have the same billing group # but different general invoice numbers). I need to pull data if one of the general invoice # invoice dates is greater than or equal to a specific date entered.

In other words: If a Billing Group # and one of the Invoice Dates is greater than or equal to a specific date entered then pull ALL GROUP data to report.

View 14 Replies View Related

Reports :: Hyperlink One Report To Another Based On Field Selection

Nov 29, 2013

I have a report that shows me an employee number that pulls data from the database, once that information is pulled, it shows me the results, I need to look another level down. Is there a way to put in a hyperlink to another report from the original report? example: Employee number 22 jobs worked on: 2214, 2218, 3222 From within this report, I would like to open job 2214 Is this possible?

View 2 Replies View Related

Reports :: Filter A Report Based On More Than One Possibility In Same Field

Mar 12, 2013

I am trying to create a report that filters out certain records in a look-up field.

The field is called "program status", and the options are:
Housed
Evicted/Un-housed
Grad
GRAI
Closed

I want the report to display only records that are classified as "Housed" or "Evicted/Un-housed". Additionally, I'd like to be able to create a second report with only the Grad, GRAI, and Closed records.

View 2 Replies View Related

Forms :: View / Print Reports Based On A Field In Form

Jul 23, 2015

I am working on a DB that will have multiple reports available. What I would like to do is to have a form that lists the reports available from a reports table. In other words, each detail line of the form will contain a report. I would like to have a button or link placed in the detail line that will open the report for that detail line when it is clicked. This will keep me from having to add a new line to a static form every time I add a report.

I have tried the DoCmd.OpenReport command, but it requires a report name, and I can't find where you can place a field name variable in the command line. I have tried other things I have seen here, but none have done the trick.

View 3 Replies View Related

Reports :: Hiding A Field In Page Header Based On A Variable

Oct 6, 2013

I have a report where I number pages based on the Store (muliple stores in report but page numbering resets when Store changes) I do this by manipulating the page number field on the report by code in the report sections to reset it and to increment it.

Code:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me!PageNum = 0
End Sub
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
Me!PageNum = Me!PageNum + 1
End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Me!PageNum = 0
End Sub

The report has a Page Header with various fields and labels and that's been fine however I've had a request to hide a field on subsequent pages (i.e. print it on the 1st page of each store then hide it).

So I added the following code to various sections, but can't get it to work correctly. The fields (label and text box) appear on Page 1 for the first store then get hidden but never return even though when I step through the code it is functioning 'correctly'. The GroupHeader code is performed but the fields don't get displayed on the report. I think is isn't re-painting that section but adding code to the Paint event does nothing, and in fact doesn't actually get run, so not sure when that event is triggered.

Code:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me!PageNum = 0
lblMemberNo.Visible = True
MemberNo.Visible = True

[Code] ....

View 3 Replies View Related

Reports :: DLookup - Populate Textbox Based On Value That Is In Another Field On Report

Oct 20, 2014

I have a textbox on a report that I wish to populate based on the value that is in another textbox/field on the report, and I thought DLookup was the way to go - however, I cannot seem to get it to work.

I have a table (ComplaintsResponses) that has two fields, both text

[ShortDescription]
[ResponseText]

The text from [ShortDescription] is saved in a field on another table that contains all the other relevant information that is used in the report, and whilst this short description is mostly fine, I have one report where I need the data from the larger [ResponseText] field.

I have tried the following code:

Code:
=DLookUp("[ResponseText]","[ComplaintsResponses]","[ShortDescription='" & [Reports]![PublicComplaintsArea]![txtSAPCRMResponse] & "'")

and

Code:
=DLookUp("[ResponseText]","[ComplaintsResponses]","[ShortDescription='" & [SAPCRMResponse] & "'")

Both of which return a #Error in the text box.

The field that contains the text that is used for the lookup is SAPCRMResponse, and the textbox on the report itself is called txtSAPCRMResponse.

View 2 Replies View Related

Reports :: Change Font Color Based On True / False Value Of Another Field

Jun 28, 2013

In access report, I'm trying to change color of text in specific records based on the true/false value in another record. Works in forms using conditional formatting, but won't seem to work in a report.

Here's what works in forms : IIf([2009 Symposium]=true, forecolor=255 ....this changes the records to red.

But using the same expression in a report doesn't change the text color.

View 2 Replies View Related

Reports :: Conditional Formatting - Rows To Be Of Specific Color Based On Value In 1 Field

Jul 24, 2014

I have a report that I would like to have the rows be a specific color based on the value in 1 field on the row.

I have attempted to use conditional formatting but it will not work (IE, I can get font color to change, but not field boxes to have color).

The field is labeled [text144] based on the value in the field (1,2, or 3) I would like the field to be a different color.
i.e.
3 = green
2 = yellow
1 = red

View 4 Replies View Related

Dynamic Field Contents

Sep 1, 2006

Is there a way to make the value of one field change dynamically based on the values in other fields on the form?

Here's the general idea. I have two tables.

RecordTable
--------------
SiteID, etc.

SiteTable
-------------
SiteID, SiteBlurb

I want have a form, where you can change the SiteID value in RecordTable, and have the SiteBlurb change (i.e. the form pulls the information from SiteTable) dynamically once you have entered the new value.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved