Simple Calculation Using Value In Subreport

Nov 25, 2004

I am trying to do a simply calculation on a report which uses a value from a subreport. The main report is called rptAllProjectsStatus the sub report is called rptAllProjectsStatusExp, and the box from the sub-report is called tboAmount which is controlled by query.

In the main report I have a box whose control source is
=[tboFixedFee]-[tboSumStaffingCost1]-rptAllProjectsStatus!rptAllProjectsStatusExp!tboAm ount

But it doesn't recognise this formula - what am I doing wrong?

I know the problem is with the latter part of the formula (in connection with the sub-report), as if I remove this part, the rest of the calculation works fine.

thanks
S

View Replies

Simple Calculation Help

Jul 20, 2007

Well, I hope this is simple, but for some strange reason, very difficult for me.

I'm having issues setting up a calculation between 2 tables. for example:

Table1 ID Quantity

Table2 TransID Used

The "Quantity" from Table1 is a set number but the "Used" will change. all I want to do is have a Form and Report that will subtract the "Used" from Table2 from "Quantity" from Table1.

I've tried: =Table1.Quantity - Table2.Used
=Quantity - Used

what am I doing wrong? all my attempts have failed, even though I have setup a relationship between the 2 tables.

any idea?

thx

View 6 Replies View Related

Really Simple Calculation That I Cant Do

Apr 18, 2008

Hiya,

I have a formula in my querry which works.

total: (Ticket_Info!T_Number_of_tickets*Ticket_Info![T_How_many_games/events_does_this_allow?])-(Ticket_Info![How many Clients are going?]+Ticket_Info![How many Aplant staff are going?])

Now what I would like to happen is that if this value does not =0 then for a mesage to pop up to alert the user that they need to ammend something.

Background of database:
It is to record where and who a business take people on trips out.
So the questions asked have been: how many tickets do you have? Then how many people are you taking (followed by a break down of people). So if the number of tickets don't = the number of people going the user needs to fix it.

Thank you so much im completly lost!

Sarah :(

View 3 Replies View Related

I Need To Make A Simple Calculation

Feb 4, 2007

Hi,

I have used the Contacts template.

I want to make a simple calculation for mins remaining from sunbed courses.

So lets say one of my clients in my database was called Sally.
When I open up Sallys details screen I want it to show:
- What sunbed course she's on (inputed via details screen per course taken out)
- How many mins she's used (inputed via details screen per visit)
- How many mins remaining (calculated for us)

I don't know how to make these calculations!!!
I can not work out how to put formulas into the Contacts table.
Can anyone help me out please?

(see pic below)
http://img219.imageshack.us/img219/9760/accesssunbedzd5.jpg

View 8 Replies View Related

Fairly Simple Calculation (that I Cannot Do!)

Mar 14, 2006

Hello.

Please see attached picture for an idea on what i'm talking about. I need to achieve the following:

I have a query and it gives a total policy commission for each policy an agent sells. There are several agents under one commission statement.

For example

agent ref 1, policy 1, information, commission amount
agent ref 1, policy 2, information, commission amount
agent ref 1, policy 3, information, commission amount
agent ref 2, policy 1, information, commission amount
agent ref 2, policy 2, information, commission amount
agent ref 2, policy 3, information, commission amount
agent ref 2, policy 4, information, commission amount
agent ref 3, policy 1, information, commission amount
etc...

What i need access to do is add a total for each agent before it shows the next agent, so the above example would change to:

agent ref 1, policy 1, information, commission amount
agent ref 1, policy 2, information, commission amount
agent ref 1, policy 3, information, commission amount
Total: commission total
agent ref 2, policy 1, information, commission amount
agent ref 2, policy 2, information, commission amount
agent ref 2, policy 3, information, commission amount
agent ref 2, policy 4, information, commission amount
Total: commission total
agent ref 3, policy 1, information, commission amount
etc...

I hope that this makes sense, and i know its probably very simple but im a n00b! so any help would be much appreciated.

Kind Regards
Darren

View 1 Replies View Related

Simple Calculation Won't Work?

Jan 19, 2005

I have been designing my first database and am just about ready to finish my first report. I have created a text box and added the following to the box =myfield1+myfield2+myfield3+myfield4

I created this through the expression builder so I know the fields a correct but now the calculation is not working. I don't get any calculation in the box except a "0". Why isn't this calculation giving me the sum of these 4 fields?

Please please help.....

View 8 Replies View Related

Simple Calculation Question

Dec 21, 2005

I have a report that contains an expression field for total sale. It is computed from multiplying quantity * price. I want to have a grand total item at the end of the report that sums all the "total sales" items. My question is what is the syntax to do this and also what section of the report should I put it in? Should I put it in the header? Any and all help is appreciated.

View 3 Replies View Related

Simple Calculation Question

Aug 3, 2007

Hello,
This is a pretty simple question I'm sure. This is a simplified version of my data:
AAC |Begin |End |Total # of days |Average for AAC
m28357|7001|7195|
m28357|7001|7124|
m20195|7002|7234|
m20195|7002|7143|

The purpose is to calculate the total # of days by just subtracting the julian dates and then I need to figure out how to have some sort of way to calculate the average # of days but only based on AAC.
Thank you for the help!

Kat

View 1 Replies View Related

Modules & VBA :: Open Recordset To Do A Simple Calculation And Lock It Again

Feb 18, 2014

I'm trying to open or unlock a recordset do do a simple calclation and then relock the record set. How this can be done with vba?

View 7 Replies View Related

Subreport?

Jun 16, 2006

I've created a database which I'm pretty much using as a point of sales terminal.. it does sales, returns, expenses etc. I have created a report for each one of these (sales, returns...) which can be printed off at the end of each day.

At the moment, if there is no data in one of the reports, I have put a bit of code in the NoData event of the report that will make a label visible saying "no sales/returns/expenses for this date".

I am trying to combine all of these reports into one report so the user can click a button to open a general "End of Day" report. The problem with making them all subreports is that if there is no data in a subreport, nothing will be displayed in that subreport.

Is there anyway to force access to display a subreport regardless of whether is has data in it? or can anyone see another option which i have missed?

View 3 Replies View Related

Go To Active Subreport

Apr 6, 2006

Is there any way to add a dropdown box or something that determines that a subrecord is active/completed, and then have a way to jump straight to the one that is active by clicking a button on the record? In the example I attached, I want to be able to click on the button above the tab and have it switch to that tab and open the first record it finds that has "Active" selected.

View 1 Replies View Related

Subreport Header

Oct 28, 2004

i have a subreport on a header of one group and sometimes has data and sometimes hasnt.I would like that when subreport is empty it doesnt show subreport header but I dont know how.code Me!subreport.Visible = Me!subreport.Report.HasData
doesnt work properly since it always has data,just not on every page....

View 1 Replies View Related

Newbie Needs Help With Subreport

Dec 5, 2004

I have created a report and the subreport. The information from the subreport will not show up when I bring the report up. I created a query with the info needed for the subreport but know I'm missing linking something somewhere? Any help?
Thanks-

View 2 Replies View Related

Subreport Issues

Aug 16, 2005

I'm new to access, I've done database work before just not with this package, I have only one small problem and this should be an easy one...

Without going into too much detail, i have a report that generates a list and calculates a few totals based on a date range inputed by a user. (I do this by using the "Between[Report Start Date] and [Report end Date]" statement as my criteria for the DATE field in my SQL statement.)

Inside that report i would like to include a subreport which summarizes the data of the main report. this subreport needs to display data based on the same date range as the main report. I simply cannot figure out how to get the subreport to use the same date range without prompting the user to type it in twice. I have another report that generates a subreport based on a users last name, this subreport works just fine ( I think this is because there is only one user inputted field and not two as with the date range.)

Both the subreport and the main report calculate the correct data when run independently, so I am confident they whole thing will work as soon as I know how to share the user inputted criteria.

If you have any answers please reply ASAP, it would be really appriciated.

View 1 Replies View Related

Subreport Question

Feb 6, 2006

Hi all!

I'm kinda stuck in a rut with one of my reports.

I have a loan database, and I need a detailed report with the borrower's name, repayment details according to their loan serial number. In the same report, I will also need details of all other people whom they have acted as guarantors for.

So what I have is a form for the user to select the person they would like to check on. Based on the user input, it generates a report with the borrower's details, as well as loan details. I then have a subreport which lists all repayments made based on the loan serial number, which works fine.

I'm stuck, however in trying to create another subreport in the main report to generate all details pertaining to loans which the person has acted as a guarantor. I tried linking the Guarantor's name to the form for the input, but in doing this, I get the guarantor's details, but my loan repayment subreport returns a blank.

Any suggestions or ideas, please?

It will be greatly appreciated! Been stuck with this for a few days now.

Thanks again!

View 11 Replies View Related

SubReport Filter

Mar 21, 2006

I have a report open that contains a subreport. I am trying to run the following code from a form to change the filter on this subreport.

Reports("rptStaffDetail").Controls("rptStaffReportSub").Report.Filter = "MasterCourseType = 1"

I get the error 'The setting you entered isn't valid for this property'

I should be able to access the subreport this way as the following code ran from the form works fine.

Reports("rptStaffDetail").Controls("rptStaffReportSub").visible = False

View 2 Replies View Related

Cancel Report With SubReport

Jun 15, 2005

I have a Report linked with a SubReport. I would like to cancel viewing this Report if my SubReport Has No Data.
i tried this code but the report just shows up. Any Ideas?

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Trapper
If Reports![qryRepCurOrgProjsSR].Report.HasData = 0 Then
Cancel = True
End If
Err_Trapper:
MsgBox Err.Description
Exit Sub
End Sub

Ok i got an error message. Yoy entered an expression that has an invalid Reference to the hasData property.

Ive tried using a text bpx on my main report and putting a control source as

=IIf(qryRepCurOrgProjsSR.Report.HasData,0,"Nothing")
Then in my Report Open Event i put the code below. But it fails me still.

If trim(Me.MyText) = "Nothing" Then
MsgBox "The Report Is Cancelling....."
Cancel = True
Exit Sub
End If

View 3 Replies View Related

Updating Subreport From Combobox

Jun 8, 2006

I tried looking through the threads to find something that is relevant to my situation.

I am having trouble with a form where i need to select a name in one combobox and a month in another and then upon which a subreport displays the records for that name and month on a subreport which can then be added extra records or old records edited.

Does anyone know how to do this or can maybe just give me some code or point of reference to do this. I have tried but all attempts return no results.

Any help is much appreciated. Thx

Frans

View 1 Replies View Related

Same SubReport Twice On A Form, Different Queries

Oct 6, 2004

I have a report with the same subreport appearing twice. The two instances of this subreport have different queries.

For instance boys and girls information. The only difference is the queries (one would have sex=M the other sex = F)

Is there any way of doing this without copying the subreport and giving it another name (this of course would allow me to assign different queries to each subreport).

Thanks

View 3 Replies View Related

Page Numbers In Subreport

Oct 22, 2007

I have page numbers in a subreport. How do I get them to update to match the correct page numbers for the report?

Thank you.

View 2 Replies View Related

Reports :: Chart On Subreport

Jun 20, 2014

So I am adding a chart (on a subreport) to a report that has multiple subreports already. I have gotten the chart to show up correctly on the report, but now 2 of my other subreports are not working. I am being prompted to input parameters for these subreports that used to pull their parameters (between dates) from the main report.

I read somewhere that charts master/child linking doesn't work but I'm not sure if that applies to subreports with charts on them, or just charts in general. I've tried changing the query on the chart subreport a little without luck. Is this likely some glitch thing? Should I keep trying to alter the query or maybe there is some other work around option?

View 7 Replies View Related

Forms :: Using Same Subreport But With Different Queries

Jun 15, 2014

I am creating an end of week report, which needs information from Sat-Fri, so basically it will be one sub form, but used 6 times.

I'd prefer not to have to make 6 subforms, with 6 queries, but rather using 1 subform and 1 query 6 times.

For example, on the parent form, I will add the subforms, and name them "Sat", "Mon", "Tue" etc...

I want to make one query that filters the information based on the name of the subform:

Day: Format([Date],"ddd")
Where: Day = Me.Name

I can't figure out what to ask the query to refer to, to retrieve the name of the subform it's currently sat in.

View 3 Replies View Related

Report, Subreport, Query, Fieldname

Mar 5, 2006

I have a report named rpt100 with two subreports srpt100a and srpt100b. The subreports are based on query qry100a and qry100b. Both queries are based on tbl100. I removed a field named 'Comment' from tbl100, as it wasn't useful; Also removed the fieldname from both qry100a and qry100b. When opening rpt100 a parameter dialog opens asking for data on the deleted fieldname 'Comment'. The field 'Comment' was never used in the report or subreports.

Inspection of the subreport fieldlist shows field 'Comment' still present.

How, other than remaking the rpt100 and both srpt100a and srpt100b, do I remove the field 'Comment'?

Gunner...:confused:

View 1 Replies View Related

Question On Grouping And Filtering In A Subreport

Aug 26, 2004

I have a report that has an embedded subreport. The main report is grouped on client id. The aim is print a different client on a different page (I have the force new page after section set). The subreport is put into the section footer. The problem is that the subreport is based on a different query. How can I filter the subreport query result to show only the information for the client specified in the main report.

thanks
newbie

View 3 Replies View Related

Calling A Public Function From A SubReport

Oct 2, 2004

I haev a report with 15 sub reports. I have to use Visual Basic to format the sub reports. I know how to do the formatting (see below) but what I would like to do is have one function (Public) that I can pass the subReport name to and have the formatting happen. This would be mush easier to maintain than copying the code below into on_print events of all 15 sub reports.

Has anyone done this and have an example??

Thanks

--------------------------------------------------------------------------------------
Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
Dim widthOfBox, startLeftSide
startLeftSide = 0.017
widthOfBox = 0.21
Me.ScaleMode = 1
Me.ForeColor = 0
'Repeat the following line of code for each vertical line
' 1*1440 represents 1 inch

' Me.Line (0 * 1440, 0)-(0 * 1440, 14400) 'Draws line at Left Margin
Me.Line ((startLeftSide * 1440) + widthOfBox * 0 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 0 * 1440, 14400) 'Draws line at Left Margin
Me.Line ((startLeftSide * 1440) + widthOfBox * 1 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 1 * 1440, 14400) ' Draws next line
Me.Line ((startLeftSide * 1440) + widthOfBox * 2 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 2 * 1440, 14400) ' Draws next line
Me.Line ((startLeftSide * 1440) + widthOfBox * 3 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 3 * 1440, 14400) ' Draws next line
Me.Line ((startLeftSide * 1440) + widthOfBox * 4 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 4 * 1440, 14400) ' Draws next line
'the 14400 is an arbitrary number to increase the line to the max of a
'section.
End Sub

View 1 Replies View Related

Access Blank SubReport Problems

Dec 5, 2004

Ok I've browesed this forum and can't seem to find anything that will work.

I have a report that includes 7 subreports with a page break before each subreport.

I want to hide, or disable the page breaks and subreports if a give subreport is empty.
Any ideas on how to do this?

View 2 Replies View Related