#### Total line makes query too complex

So I have created my functions to calculate the expected deduction per
deduction per agent number per account number. The problem I am having now is
that I need to sum up the deductions per agent number per account number.
(Each agent may have multiple deductions in the account.) I was working on
this DSum, but am getting a not defined error on DeductConcate:

Public Function ExpectedDeductionsSum() As Variant

ExpectedDeductionsSum = DSum("[ExpectedDeductionsToDate]",
"[qryExpectedDeductions]", "[DeductConcate] = '" & [DeductConcate] & "'")

End Function

The final function that combines the others:

Public Function ExpectedDeductions(CurrentLookup As String,
FinalStartMonthLookup As String, FinalNoMonths As String, FinalMoDeduction As
Currency, FinalTotalDeduction As Currency) As Currency

'Determine Expected Deductions

If ((CurrentLookup - FinalStartMonthLookup) + 1) <= 0 Then
ExpectedDeductions = 0
Else
If ((CurrentLookup - FinalStartMonthLookup) + 1) > 0 And ((CurrentLookup
- FinalStartMonthLookup) + 1) < FinalNoMonths Then
ExpectedDeductions = ((CurrentLookup - FinalStartMonthLookup) + 1) *
FinalMoDeduction
Else
ExpectedDeductions = FinalTotalDeduction
End If

End If

End Function

CurrentLookup is a DLookup entered into the expression

Public Function FinalStartMonthLookup(FinalStartMonth As String) As String

'Determine the Start Month Lookup from the Months table

FinalStartMonthLookup = "SELECT [Months.MonthIndex] " & _
"FROM [Months] " & _
"WHERE [Months.Month] = '" & FinalStartMonth & "' " & _
"ORDER BY [Months.MonthIndex];"

End Function

Public Function FinalNoMonths(ONoMonths As Variant, UNoMonths As Variant,
UpdatedMonth As Boolean) As Variant

'Determine Final Number of Months for each deduction

If UpdatedMonth = True Then
FinalNoMonths = UNoMonths
Else
FinalNoMonths = ONoMonths

End If

End Function

Public Function FinalMoDeduction(OMoDeduction As Variant, UMoDeduction As
Variant, UpdatedMonth As Boolean) As Variant

If UpdatedMonth = True Then
FinalMoDeduction = UMoDeduction
Else
FinalMoDeduction = OMoDeduction
End If
End Function

Public Function FinalTotalDeduction(OTotalDeduction As Variant,
UTotalDeduction As Variant, UpdatedMonth As Boolean) As Variant

If UpdatedMonth = True Then
FinalTotalDeduction = UTotalDeduction
Else
FinalTotalDeduction = OTotalDeduction
End If

End Function

I have tried to create a query and use the Total line to Group By the agent
number / account number and Sum the deduction amount, but it is too complex.
Any other suggestions?

Thanks

 0
Utf
1/7/2010 8:51:02 PM
access.queries 6343 articles. 1 followers.

0 Replies
981 Views

Similar Articles

[PageSpeed] 50

Similar Artilces:

How do I make numbers become text?
I am trying to create a spreadsheet where numbers entered in one location become text in another. I tried the Help option, but I am still lost. Please help, and thank you. From earlier postings: How to Convert a Numeric Value into English Words http://support.microsoft.com/default.aspx?scid=KB;EN- US;140704& and: (courtesy of a cut and paste from a Tom Ogilvy post): If you want an addin that provides a worksheet function that does this, download Laurent Longre's free morefunc.xll addin found here: http://longre.free.fr/english/ It is downloaded in a zip file which also conta...

Publisher has turned yellow. How do I make it white again??
When I open Publisher, it seems to have aquired its own colour scheme - the blank page is yellow. How do I make it white again? I have searched all the menus but cannot find anything that helps. It's driving me mad!! It only happened in the last few weeks, and I've no idea what I did to change it. I'm usung Publisher 2000. A small child turns to Ed, and exclaims: "Look! Look! A post from butterflyj <butterflyj@discussions.microsoft.com>!"... > When I open Publisher, it seems to have aquired its own colour > scheme - the blank page is yellow. How do I ma...

Calculating totals by month from whole date
I'm trying to figure out the proper calculation to pull totals by dat in Excel. Here's my problem: I have a workbook with 2 worksheets; one is the main data, the other i the statistics from the main data. Within the main data, there is column titled "date paid" and another column titled "total paid" (ther are a bunch more columns, but they don't matter for this problem). Th "date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O the stats page, I have columns titled for each month of the year an would like each to include how much was pa...

Step Line Charts
Does anyone know how I can create a step line chart without having to have 2 data points per x-axis point. What I want is a horizontal line until the next data point and then I want the line to go vertical. Hi Carrie, You Will need some form of extra data to get excel to create a step chart. Take a look at these methods. (http://www.andypope.info/charts/stepchart.htm) Carrie wrote: > Does anyone know how I can create a step line chart without having to have 2 data points per x-axis point. What I want is a horizontal line until the next data point and then I want the line to go vert...

Want to make complex header on 2nd and 3rd page
I need to make a quotation which consists of a first page, thats fully on its own and then two pages which has a same header. The header, though, consists of quite a lot of text and also a company logo and that was byond the header functionality. How to get the 2nd and 3rd pages with a complex upper part? Bart Exc 2003 ...

Lines of text move when viewed in print preview and printed
I am inserting pictures with-in paragraphs of text. I have tried formating as both exact and in-line, using top/bottom. The text moves up 1-2 lines when printed or viewed in print preview. This change in text lines also happens when view is changed from 50% to 100%. I am working with two column text and the pictures are inserted into each column. How can I fix this problem? What version of Windows and Publisher are you using??? You people that think we have crystal balls or are mind readers are exasperating! -- >-----Original Message----- >What version of Windows and Publi...

making items available on website
Is there a way to 'make items available on the website' other than going into each items properties under the Options Tab and checking the box? I would like to make quite a few items available at one time if possible. Thanks for any help. Craig This is a multi-part message in MIME format. ------=_NextPart_000_0465_01C72051.D97BD680 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Craig, Backup first!! This will set _all_ items as available for web sales except those that = are marked inactive. UPDATE Item SET WebItem =...

Calling VBA subroutine from a query?
Can I call a VBA subroutine from within an Access query? I wrote some English to Metric conversion routines in the Access VBA code and would like to run a query on the data that will return coverted values. I need to be able to execute this from outside the database (run the query from another program). Yes. The function must be a Public Function in a standard module. You use a calculated control to return the results of the query. ConvertedValue: MyFunction([SomeField]) -- Dave Hargis, Microsoft Access MVP "DavidY" wrote: > Can I call a VBA subroutine from within an ...

Button on Form to Run Report based on Query with Parameters
Hi Guys. I am busy working on a stock control database (and job control). When I have a Form Open, it shows several fields, including JobID (Which is the PK for that job) (frm_Invoicing) I also have a Query that when Run, it prompts for the JobIDNo. (qry_CustJobsInvStock) I have a report based on that query. (rpt_CustJobsInvStock) I have added a button onto the form (onclick -> DoCmd.OpenReport) to open and automatically print the report, But I just cannot figure out how to get it to get my JobID from the form, and to Automatically add it as a Parameter for the que...

Two lines and pne column
I am trying to modify one of the custom charts to show two line and one column rather than two columns and one line. can anyone help. Ian - Forget the custom type. Make a chart with all three series as lines, select the one series, choose Chart Type from the Chart menu, and select the Column style you want. Voila, your first custom combination chart. There's more about Combination Charts on my web site: http://peltiertech.com/Excel/Charts/ComboCharts.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech....

making my own reports......
I am doing a mailshot next month and i have several customertypeid's set up for all my different customers, i have a report which shows everything i need (|Customer name, address1,address2,state,zip,country) but i want account type id to be available on the report so i can filter it accordingly, when i add the following to the report i get errors - can anyone help? If i just plonk this into a customer list report should it work? I did this to add the country colum to my reports and it worked ok! Begin Column FieldName = "AccountTypeID" DrillDownFieldName = "&qu...

Pivot table total decimal places rounding
The totals in my pivot tables have too many decimal places. I total -\$1,025.31 and \$1,023.87 and the result is \$1.43999999999994 in the total cell of the pivot table but I need just \$1.44 I have formatted the table as currency and using 97. Pivot tables can lose their formatting if the data is refreshed. Reapply the formatting, and make sure that 'Preserve formatting' is checked (under PivotTable>Table Options). AM wrote: > The totals in my pivot tables have too many decimal places. > I total -\$1,025.31 and \$1,023.87 and the result is > \$1.43999999999994 in the to...

Monthly contract lines
Hello, I need to be able to provide my clients 60 minutes allotments each month, then start again with a clean set of 60 minutes each month. What is the best way of doing this? I.e. perhaps automatically create a new contract line each month? Thanks for your recommendation Tony ...

CVS file only inserts commas for 15 lines of spread
Hi, I hava an user in Europe that is following the exact steps I am to covert a file to CSV format. When we open her file in word pad, only the first 15 lines of data have comms delimted. It happens every time regardless of how much data is in our spread. Any ideas on how to correct this? Thanks much! S I've never seen anything like this. If they reopen the .csv file in Excel, does it look ok? If they open the file in NotePad (not wordpad), does it look ok? My silly guess: I'm wondering if there is any special character in the data that is being used as an end of file marker....

Matching Zip coded in a Query
I have query that joins two tables using the Zip Codes (only show the records where the Zip codes match). The problem is that in one table, the listing of zip codes, it is 5 digit zip codes. In the main table some of the zip codes are 5+4 zip codes. My current query leave out the 5+4 Zip codes. I want all the records as long as the 5 digit zip codes matches. I quess I could delete the - and the last 4 digits on the main table, but it would be nice to retain the whole zip codes. Is there a way to do this? Thanks!!!!! Use Left(MainTableZipCodeField,5) to compare to the original ...

I need .5 instead of .3 on total time on time sheet
I need to make the total time at the end of the week show .5 (39.5 hours) not .3 (39.3 hours) What formula? What value of the source cells? What formatting? -- Kind Regards, Niek Otten Microsoft MVP - Excel "Anthonyt65" <Anthonyt65@discussions.microsoft.com> wrote in message news:0B4BFDEF-6625-42E9-ACC3-143C5C8C8832@microsoft.com... >I need to make the total time at the end of the week show .5 (39.5 hours) > not .3 (39.3 hours) try =MROUND(F2,0.5) -- Don Guillett SalesAid Software donaldb@281.com "Anthonyt65" <Anthonyt65@discussions.microsoft.c...

Creating a Total from a Sub Report
I have a report for Orders, and a subreport for each Order for Invoices. The subreport shows the total amount for Invoices against each Order. The Orders are grouped by Area. On the Area header/footer I have an Order total. I now want to show the total of ALL Invoices by the Order total. Please can someone tell me as simply as possible how to do this? For ease I will call my main report ORDERS and my subreport INVOICES. Any help is greatly appreciated - I have searched through relevant questions but have not found an expression that works! Answered in another NG where ...

How can I make this happen?
I want a macro or something that will allow me to "show" every comment on a work sheet (there are almost 100) then go to Format Comment and Properties and change it to "move and size with cells" then rehide the comment. Any ideas? -- foxspirit ------------------------------------------------------------------------ foxspirit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31264 View this thread: http://www.excelforum.com/showthread.php?threadid=509350 On a worksheet is there a function that unhides all comments in every cell? -- foxsp...

Confused, help with this line please!
Range(Cells(9, 1), Cells(9, 6)).Delete Shift:=xlUp works fine, but Worksheets(3).Range(Cells(9, 1), Cells(9, 6)).Delete Shift:=xlUp gives a runtime error 1004 message. why? ( function is in worksheet 1 ) try it this way Sheets(3).Range("a9:f9").Delete Shift:=xlUp Don Guillett SalesAid Software donaldb@281.com "serdar" <serdarsoy@yahoo.com> wrote in message news:eaBhR0pbFHA.2128@TK2MSFTNGP15.phx.gbl... > Range(Cells(9, 1), Cells(9, 6)).Delete Shift:=xlUp > > works fine, but > > Worksheets(3).Range(Cells(9, 1), Cells(9, 6)).Delete Shift:=xlUp >...

Queries and reports in access Need help please
Hello everyone , I have a little issue to setup a database. I created a table with approximately 20 columns. The columns have an entry of Yes or no base on a questionnaire from survey. What can I do to get a result of my table I would like to know how many "Yes" and "no" I have. I don't know how to add them or get this information. Thanks in advance GABRIEL Gabriel, The first answer to your question is that your table structure is not really the best for this situation, and it will be a lot easier if you can set it up differently. Is your database design set in ...

CRichEditControl and error when number of chars is >3072 in single line
Hi All, I am using CRichEditControl for my text editor and when there are more than 3072 characters in a single line ( font being used is Courier 10pt ). , i get an error from this control. I am using it without wordwrap and this error comes. Is this a known issue and is it documented somewhere about such a limit of number of characters in a single line without word wrap ? Is this a known limitation ? thankyou Narasinga "An" error? WHAT error? Perhaps you know, but would it really inconvenience you when asking a question to share the EXACT information with us? joe On Tue, ...

How can I make an application transparent?
Can anyone help me with an ideea how can I find a code that makes aplications transparent?I need it at work and I m not allouded to install any kind of software (like Glass2k) therefore I'm thinking of a VB Script to run under Excel. I'm using a personalized application that I need to put on top of maps.google.com to draw streets. I'll appreciate any kind of suggestion! Dan Tabla <DanTa...@discussions.microsoft.com> wrote... >Can anyone help me with an ideea how can I find a code that makes aplications >transparent?I need it at work and I m not allouded t...

How do make a text with broken underline #2
I've seen in a sample template for brochure a text with broken underline and the underline color can be change to other color, I was trying to find out how to do it. Maybe someone here knows how, I would be very grateful. ...

Make current option group choice default
I have been wanting to make the current option group choice the default the next time a user opens a form. I don't want them to choose all the options every time they print a report (option groups on form that loads at report open determines layout, font type, size). I getting myself confused in the effort to get this accomplished, so any pointers, ideas, suggestions, commandments, etc. would be appreciated. Jeff -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201002/1 To do this, you need to save the users choices for ...

Query repeated values
Hello everyone. Sorry for such a newbie doubt, but since my Access and SQL experience is a bit “trial and error” I don’t even know what to search for here in the forums that so I can help myself. I have two tables A and B. First table has people’s NAMES field. Marc Wilson Andrea Smart Francis Junior Second table has a field with people’s ABILITIES but some are repeated: WORD – Marc Wilson WORD – Andrea Smart EXCEL – Francis Junior COREL – Andrea Smart COREL – Andrea Smart I’m trying to build a query to point out the duplicated lines, in this case: COREL – Andrea Smart ...