Sum of values in a form


I have read numerous threads on this topic, and understand that in order for
my Sum() to work I need t ensure all references are made back to the Record

I have endeavoured to do this, but am still getting #Error.

I am trying to Sum the revenue each tenant provides in a given date range, to
give a total revenue figure. If the date range is a whole month, then the
expression just takes the value of Rent PCM, and not Rent PCM * No. of Days,
which would give some unusual results.

My Expression looks like this:

=Sum(Nz(IIf([Short Let]=-1,[Rent pcm],IIf(Day([Forms]![Revenue Report]![Start
Date Chosen])=1 And (Day([Forms]![Revenue Report]![End  Date Chosen])=Day
(DateSerial(Year([Forms]![Revenue Report]![Start Date Chosen]),Month([Forms]!
[Revenue Report]![Start Date Chosen])+1,0))) And (Month([Forms]![Revenue
Report]![Start Date Chosen])=Month([Forms]![Revenue Report]![End  Date Chosen]
)) And (Year([Forms]![Revenue Report]![Start Date Chosen])=Year([Forms]!
[Revenue Report]![End  Date Chosen]))=-1 And [Moved In]<=[Forms]![Revenue
Report]![Start Date Chosen] And ([Moved Out]>=[Forms]![Revenue Report]![End
Date Chosen] Or [Moved Out] Is Null),[Rent pcm],DateDiff("d",IIf([Moved In]<=
[Forms]![Revenue Report]![Start Date Chosen],[Forms]![Revenue Report]![Start
Date Chosen],[Moved In]),IIf([Forms]![Revenue Report]![End  Date Chosen]>=
[Moved Out] And [Moved Out]>=[Forms]![Revenue Report]![Start Date Chosen],
[Moved Out],[Forms]![Revenue Report]![End  Date Chosen]))*([Rent pcm]*(12/365)

I wonder whether referencing back to another form where I have entered the
dates for the query range (Start Date Chosen and End Date Chosen) are the
current cause of my problem.

Any thoughts gratetfully received.

Kind Regards,

Message posted via

11/19/2009 5:39:21 PM
access 16762 articles. 3 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 29

I noticed that nobody answered.    

A couple of thoughts:   

1.  Make sure the name of the box is not the same as the name of anything 
else, including anything used in the expression.  A common problem if you 
"started" the box as one bound to a single field.    Or, to put it an easier 
way, try renaming the box to somethinn that certainly doesn't have that 
conflict.  If you "started" box    

2.  Try "cutting down" your expression until it starts working.   That 
should narrow down what the source fo the problem is.  

11/20/2009 1:13:01 PM

Similar Artilces:

Access 2007 Problem with Forms
When i try to add a contol to a form such as "Close form" or "Save" it doesnt work - does anyone know what im doing wrong. Running Access 2007 on Vista and have databases saved as Acess 2000. Have you tried doing this: -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at If my post was helpful to you, please rate the post. __________________________________ "Belle" wrote: > When i try to add a ...

Output form to excel with range ????
I've read the code written by Mr Dev on the site but that is not excatly understand how the code is work.I have condition: Some data that I want to output to are on the form Header,and the others are on the detail section of my continuous form.Below is where I've got so far Dim appXL As Object Dim wkb As Object Dim wks As Object Set appXL = CreateObject("Excel.Application") Set wkb = appXL.Workbooks.Open("C:\MyFolder\My Workbook.xls") Set wks = wkb.Worksheets(1) appXL.Visible = True wks.Cells(4, 3) = [Customer] wks.Cells(6, 3) = [LotNo] wks.Cells(7, 3) = [Model] ...

Invoice forms
In reference to Money 2003 Deluxe/Business, I would like to use blank computer forms which are preprinted with my logo when I print invoices. I noticed that one of the choices for invoice printing is Harland. I cannot find a supplier for Harland invoice forms. Is there any other supplier for Money invoice forms? "Sam" <> wrote in message > In reference to Money 2003 Deluxe/Business, I would like to use blank > computer forms which are preprinted with my logo when I print invoic...

How to generate a form from an entry?
I hope I explain this correctly. I have a spreadsheet that merely is to record an entry instead of writing it in a paper form. For example: Entry #, customer, color#, Charge, Date. This is just to record the submission of a color chip to a vendor. THEN, we have to get an actual pre-pinted form and fill out all the same information, as well as some other info and fax it to our color vendor. The spreadsheet is just for us internally. What would be fantastic is, if after entering that line in Excel, somehow we would then be able to print the form that we have to fax, with all of the dat...

capturing pictures from a webcam with a form
Hi there, now that I have a working Student roster in my teaching database, I want to be able to snap a picture of each student with my webcam and have the picture automatically stored in the OLE Object field of that student's record. A search turned up only one possible solution: a plugin called DBPix from Ammara ( That looks like a full-featured commercial image-manipulation product... more than I really need. Do you (readers) have any better way to do it? ...

Change default zoom value to page width in SSRS 2008
Is it possible to change the default zoom value from 100 to page width? thank you. ...

Outlook 2000 forms does not work in Exchange 2000
I have a Outlook 2000 leave form that user used to apply leave. The form is then emailed to the approval and the approval opened the form to approve or reject. The reply is then emailed back to applicant. The Outlook form did not work for the approval. When you click on the approve button nothing happened. I understand MAPI doesn't work on Exchange 2000. Here is the approve subroutine: Sub cmdApproveLeave_Click() Set cControls = Item.GetInspector.ModifiedFormPages ("Message").Controls If cControls("txbDisableFunctions").text = "888" Then Msg = ...

Assigning letter for numerical value
want to assign a letter or number based on calculated value (much like a grading scale.) If calc value is: ..0 to .25= I ..26 to .50= H .. .. .. final one >5.1 =A can you do as an array beside doing the If > and < formula thx Use the =VLOOKUP() function -- Gary''s Student - gsnu200909 "Reno" wrote: > want to assign a letter or number based on calculated value (much like a > grading scale.) If calc value is: > .0 to .25= I > .26 to .50= H > . > . > . > final one >5.1 =A > > can you do as an array b...

Outlook Forms????
I created a form, however, there is a message page, with the To:, From: ect. & a Page 2. I really don't want the form to be 2 pages. How can I get everything onto the message page. This way a user does not have to click the P2 tab to fill in the form. Cut and paste the controls on Page 2 to the main message page. FYI, there is a newsgroup specifically for Outlook forms issues "down the hall" at microsoft.public.outlook.program_forms or, via web interface, at -- Sue...

printer -form feed
I want to repeatedly print a small 3 line report from Excel to an old panasonic tractor feed printer. I dont want the printer to form feed after each report because it wastes too much paper. Is the form feed a function of Excel or of the printer? How can I defeat it? -Kevin The printer driver sends the form feed after the print job is complete. I don't know if you can defeat it but try looking at your Printer Properties to see if there's a setting that can prevent the form feed. >-----Original Message----- >I want to repeatedly print a small 3 line report from >Exc...

Formula that Ignores Values less than 1
I have a formula that compares two values in separate worksheets. I the difference between the two values is between -5 and +5 I want th formulat to ignore it, if it is greater than -5 or +5 I want an "ERRO - Does not balance" message to show in the cell. I have tried with IF / OR statements but can't figure out the syntax can anybody help? Thank -- Message posted from Hi Herman Try =IF(ABS(A1-A2)<5,0,"Error - does not balance") Hi Herman, It might be better as =IF(ABS(A2-A3)<5,"","Error - does not balance") ...

Sum Value using ALT+ENTER
i have enter the record in the row, and inside the cell, i'm using ALT+Enter to make more line for the record, but i would like to know how to sum of the value in this cell?? e.g. C1 C2 C3 R1 1 1 XD R2 2 2 YY R3 1 XY R3 2 A R4 3 3 how can i get the value of A = 3, that is the sum of R3C1(1+2) also, how can i put the dollar sign automatically in cell R3C1 if i'm using "ALT+ENTER" to make more line. -- kilianli -------------------------------...

Find Value and delete rows above
Hello everyone, Ive got a sheet which draws data in via a web query, It then pull all the info and puts it into my sheet down the sheet in rows.... Depending on which page of my site it draws it from.. depends where the starting point in..... for example... Page 1 = A1 = blah blah 1 B1 = blah blah 2 C1 = Start D1 = Data E1 = Data F1 = End G1 = blah blah 3 Page 2 = A1 = blah blah 1 B1 = blah blah 2 C1 = blah blah 3 D1 = Start E1 = Data F1 = End G1 = blah blah 3 Is there a way I could get it to strip out everything from the START and above, and everything from END and below no matter how ...

Method to increment value in data field?
It would be helpful while tabulating survey data, to be able to increment a field value by one-- with the stroke of a "hot key" rather than having to erase the current value and raise it by one. Does such a method exist? Karl You have to use VB Copy this macro into a module. Press Alt + F11,Insert, Module and then paste the code Sub Incr() Dim c For Each c In Selection c.Value = c + 1 Next End Sub Press Alt+F8 select the macro and choose OPtions and you can assign a short cut key to run the macro Regards Peter "Karl H" wrote: > It would be helpf...

Column Chart, Displaying two values on one column.
Hello, Is it possible to display two value on one column. For example Budget = 4.6% Actual = -6.0% I can display this on a column without any problems, one column for budget and one for actual. Is it possible to add another value to the Actual so it displays on the same column but in a different colour? For example I would like to add: Net = 2.3% and display this on the Actual column in a different colour. Any help would be appreciated. Thanks, Brett You can format a stacked column chart to resemble a clustered-stacked column chart:

Color Chart Columns to Color Specified by a Cell Value
I want to create a chart with the color of the columns specified by a value in a cell. All of the columns can be of the same color. I am already using the code below to update the scale of the chart, using a value in cell E35 in this case. Is there something I can add to this to control the color or do I need to use a seperate piece of code? Sub UpdateScale() ActiveSheet.ChartObjects("Chart 4").Activate With ActiveChart.Axes(xlValue) ..MinimumScale = 0 ..MaximumScale = Range("e35").Value End With End Sub ...

Calling a .NET Form
If you have a VC++/MFC application and you turn it into managed code, can you then call into a .NET assembly that will display a Windows Form? I have found articles that allow .NET to easily call into VC++ but not the other way around. Thanks, Rob C Hello Rob, > If you have a VC++/MFC application and you turn it into managed code, > can you then call into a .NET assembly that will display a Windows > Form? I have found articles that allow .NET to easily call into VC++ > but not the other way around. > So you want to call managed code from a MC++ module? Any managed mo...

We're in the process of upgrading from GP7.5 SP4 to SP6. We are on MSSQL Server 7.0. My supervisor has already implemented the hot fix as describe in MBS (that's a script to update some indexes on the server if someone is using GP7.5 manufacturing on MSSQL7). When I try to import modified forms and reports I've received some errors. For instances some references on the VBA to the GUI forms (i.e. text boxes, buttons) become missing. And on the reports I've been told it couldn't find the table MOP_MO_Variance_DC_Detail_Report_Temp and MOP-Sched_Interface_Report_Before....

Form coding question
Hi everyone, I am just setting some rules for my form via code and was in need of some advise/help. I have coded the following: Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(Me.First_Name) Then MsgBox "Please enter the your first name", vbCritical Cancel = True First_Name.SetFocus End If End Sub If I wanted to add addional fields to check if they are empty before the form closes let say received date and zipcode, how would the code change? Any help is apprecieted. Would i just put something like the following?: Private Sub Form_BeforeUpdate(Cancel ...

numbering order forms
I am trying to develop an order form to be used in our office by our employees. I'd like to be able to have each invoice have a new, consecutive number - done automatically! Is it possible and if so, how. Thanks for any help. Perhaps try this previous post by Leo Heuser where he provides 2 routines: Or this one by Dick Kusleika -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <at>yahoo<dot>com --- "ediee" <> wrote in message news:0b2f01c425af$0a5b32f0$a4012...

in excel how can i have text ontop of a picture? (a scanned form)
I have scanned a blank form and copied it into excel. I would like to then type ontop of it (in normal cells) so that my text can therefore be printed on this form. Can you please explain how i can do this? Thanks Try using the text box on the Drawing Toolbar (View, toolbars, drawing). Select the icon with the A in a box, draw where you want the box to appear, then type in it. There will be a black lined box around it, which you can remove by clicking on the box line (the pattern will change slightly), go to the icon in the Drawing Toolbar that has 4 solid lines on it, click on it, ...

Verify a collection value
I've this code Dipendente myDipendente = new Dipendente(); myDipendente = DipendenteManager.GetItem(IDDipendente, true, true, true, true); Now in myDipendente.Elenco I've an ElencoCollection Now I would like to verify if in my collection (Elenco) there is, for the field Elenco.ID the value 10... is it possible? Thanks roberta wrote: > I've this code > > Dipendente myDipendente = new Dipendente(); > myDipendente = DipendenteManager.GetItem(IDDipendente, true, > true, true, true); > > Now in > ...

Formating Form
Is it possible to format form to look just like a button? (i.e. withou caption area, without x button -- Message posted from ...

eliminating zero-value rows
Greetings, A series of rows comprise a product list we are using (eg "Item X, Item Y, Item Z"). A column next to these products is used to designate the quantity of each item we will need for a particular job. So, we might put a "3" in the column next to Item X, or a "0" next to Item Y. I would like to then have a tab that lists all the materials we need for a job, and their quantities, without having the blank rows if the quantity is zero. Any ideas how to accomplish this? Thanks for your help. Cheers, Scot B. I'd keep all my data in place an...

Query to add 2 different record values
I am trying to build a query which will add together 2 values from 2 different record going along rather than going down. Something like this in Excel: Add Cell B205 to Cell C205 and produce the SUM in Cell C206 So in Access 2007 I have RainAvg value field then RainTotal field for record 1 and the total will be equal to RainAVG In the 2nd Rrecord the RainTotal will be the sum of RainAvg in the 2nd record plus RainTotal from the previous record. Is there a way this can be done in a query? thanks Only if there is some field in the table to tell me that record 1 comes before ...