Sum values in listbox?

In one of my db's (A2003) I have a listbox that displays CurrentDate,
CompanyName, CountOfInvoices, SumOfInvoices. I'm wondering If there is
a way to display a sum of all the items in, for example, Column(2) and
(3) of the list box to get a grand total.

 Right now I'm using a separate list box to display the values I want,
which works fine, so mostly I'm just curious. I couldn't figure out a
way to do it, but maybe I'm just being a meat head and overlooking
something.

Any ideas?

TIA

(is meat head one word or two?)
1
Beetle
1/18/2008 6:21:22 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
1763 Views

Similar Articles

[PageSpeed] 45

I have not tried this, but I had an idea that maybe using a Union query as 
the row source of the list box.  Union another query that would Sum the 
values for your.
Just a spurious thought.
-- 
Dave Hargis, Microsoft Access MVP


"Beetle" wrote:

> In one of my db's (A2003) I have a listbox that displays CurrentDate,
> CompanyName, CountOfInvoices, SumOfInvoices. I'm wondering If there is
> a way to display a sum of all the items in, for example, Column(2) and
> (3) of the list box to get a grand total.
> 
>  Right now I'm using a separate list box to display the values I want,
> which works fine, so mostly I'm just curious. I couldn't figure out a
> way to do it, but maybe I'm just being a meat head and overlooking
> something.
> 
> Any ideas?
> 
> TIA
> 
> (is meat head one word or two?)
> 
0
Utf
1/18/2008 6:30:00 PM
Thanks Dave. I hadn't thought of that, but I'll try that when I get a chance. 
It's not a big deal, but right now my form loads a little bit slower than I'd 
like and I think it's because I have 4 list boxes, so I'm just looking at 
alternatives.

As usual, there is nothing "meatheadish" about your advice.

meatheadish - that's a word right?
Note to self: find a dikshunairee ;-)

-- 
_________

Sean Bailey


"Klatuu" wrote:

> I have not tried this, but I had an idea that maybe using a Union query as 
> the row source of the list box.  Union another query that would Sum the 
> values for your.
> Just a spurious thought.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Beetle" wrote:
> 
> > In one of my db's (A2003) I have a listbox that displays CurrentDate,
> > CompanyName, CountOfInvoices, SumOfInvoices. I'm wondering If there is
> > a way to display a sum of all the items in, for example, Column(2) and
> > (3) of the list box to get a grand total.
> > 
> >  Right now I'm using a separate list box to display the values I want,
> > which works fine, so mostly I'm just curious. I couldn't figure out a
> > way to do it, but maybe I'm just being a meat head and overlooking
> > something.
> > 
> > Any ideas?
> > 
> > TIA
> > 
> > (is meat head one word or two?)
> > 
0
Utf
1/18/2008 6:51:01 PM
I don't know if this technique would work for you, but I had a similar 
situation with a form where I needed 7 list boxes.  It took 2 to 5 minutes to 
open.  So, took the row source off each list box, set its height to 0", and 
put a command button just above each list box.  In the click event of the 
list box, I assigned the row source to the list box, set the height to what 
is was in design view, and set the focus to it.
It loaded very fast and even the largest rowsource used a filtered query 
that looked up values in a table with about 20,000 rows.
-- 
Dave Hargis, Microsoft Access MVP


"Beetle" wrote:

> Thanks Dave. I hadn't thought of that, but I'll try that when I get a chance. 
> It's not a big deal, but right now my form loads a little bit slower than I'd 
> like and I think it's because I have 4 list boxes, so I'm just looking at 
> alternatives.
> 
> As usual, there is nothing "meatheadish" about your advice.
> 
> meatheadish - that's a word right?
> Note to self: find a dikshunairee ;-)
> 
> -- 
> _________
> 
> Sean Bailey
> 
> 
> "Klatuu" wrote:
> 
> > I have not tried this, but I had an idea that maybe using a Union query as 
> > the row source of the list box.  Union another query that would Sum the 
> > values for your.
> > Just a spurious thought.
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "Beetle" wrote:
> > 
> > > In one of my db's (A2003) I have a listbox that displays CurrentDate,
> > > CompanyName, CountOfInvoices, SumOfInvoices. I'm wondering If there is
> > > a way to display a sum of all the items in, for example, Column(2) and
> > > (3) of the list box to get a grand total.
> > > 
> > >  Right now I'm using a separate list box to display the values I want,
> > > which works fine, so mostly I'm just curious. I couldn't figure out a
> > > way to do it, but maybe I'm just being a meat head and overlooking
> > > something.
> > > 
> > > Any ideas?
> > > 
> > > TIA
> > > 
> > > (is meat head one word or two?)
> > > 
0
Utf
1/18/2008 6:59:00 PM
Now that's a really good idea! I like that solution.

Again, thanks for your advice Dave. I appreciate it.
-- 
_________

Sean Bailey


"Klatuu" wrote:

> I don't know if this technique would work for you, but I had a similar 
> situation with a form where I needed 7 list boxes.  It took 2 to 5 minutes to 
> open.  So, took the row source off each list box, set its height to 0", and 
> put a command button just above each list box.  In the click event of the 
> list box, I assigned the row source to the list box, set the height to what 
> is was in design view, and set the focus to it.
> It loaded very fast and even the largest rowsource used a filtered query 
> that looked up values in a table with about 20,000 rows.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Beetle" wrote:
> 
> > Thanks Dave. I hadn't thought of that, but I'll try that when I get a chance. 
> > It's not a big deal, but right now my form loads a little bit slower than I'd 
> > like and I think it's because I have 4 list boxes, so I'm just looking at 
> > alternatives.
> > 
> > As usual, there is nothing "meatheadish" about your advice.
> > 
> > meatheadish - that's a word right?
> > Note to self: find a dikshunairee ;-)
> > 
> > -- 
> > _________
> > 
> > Sean Bailey
> > 
> > 
> > "Klatuu" wrote:
> > 
> > > I have not tried this, but I had an idea that maybe using a Union query as 
> > > the row source of the list box.  Union another query that would Sum the 
> > > values for your.
> > > Just a spurious thought.
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > > 
> > > 
> > > "Beetle" wrote:
> > > 
> > > > In one of my db's (A2003) I have a listbox that displays CurrentDate,
> > > > CompanyName, CountOfInvoices, SumOfInvoices. I'm wondering If there is
> > > > a way to display a sum of all the items in, for example, Column(2) and
> > > > (3) of the list box to get a grand total.
> > > > 
> > > >  Right now I'm using a separate list box to display the values I want,
> > > > which works fine, so mostly I'm just curious. I couldn't figure out a
> > > > way to do it, but maybe I'm just being a meat head and overlooking
> > > > something.
> > > > 
> > > > Any ideas?
> > > > 
> > > > TIA
> > > > 
> > > > (is meat head one word or two?)
> > > > 
0
Utf
1/18/2008 7:08:02 PM
Reply:

Similar Artilces:

Copy a currency value from one control to another
I have a text box (called ExtendedCost), that has a sum statement in it. I want to copy the value on the exit control event to another text box control (called WithdrawlAmount) on the same form and write the value into WithdrawlAmount box on the Enter Event for the control box, so people don't have to input the same number into the control box. Is there an easy way to do this? If it's the same calculated value, just use the same expression. Generally, don't store calculated values. Just perform the calculation as needed. If you need more help with this, describ...

conditional summing of arrays
I would like to sumproduct 2 arrays up to the row where it first exceeds a given number. For example assume 2 arrays are as follows : 1,2,3,4,5 & 2,3,4,5,6 the sumproduct is 2,8,20,40,70. So if my given number was say 30, the answer would be 4. if the number was say 80, the result would be NA. Can anyone help me with this problem? Thank you and kind regards JV ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** This can probably be done easier =INDEX(LARGE(ROW(INDIRECT("1:"&...

Getting a value from the last record
Hello, I have an inventory database. I have the sum of apples in my last record and want to subtract apples today from it. The value is the last record number from a table: Total apples (in last record) = 25 (new record) sell 5 apples = total apples = 20 me.total_apples = me.sell_apples + (last record me.total_apples) How can I get that last records me.total_apples. I do not have an open internet access so I can not go see alan browns web page only microsoft web pages. Thanks How will Access know what the last records is? Do they contain a DateTime tracking fiel...

paste values Excel 2007
Is there a way to setup a hot key to paste values in Excel 2007? Also, is there a way to change the icon buttons? Thx You can add the "Paste Values" icon to your quick access toolbar. I placed mine next to a "Copy" icon. Only custom icons can be modified to my knowledge. There may be ways developed by independent users. Squeaky "Diamond" wrote: > Is there a way to setup a hot key to paste values in Excel 2007? Also, is > there a way to change the icon buttons? > > Thx Re changing the icon buttons - used to be possible in...

'advanced filter/unique values only' inaccurate counts
i often have to use filters to reduce a column of numbers to just it's unique values. simple enough, but unfortunately excel's advanced filter doesn't always do the job and leaves out values or just doesn't filter correctly. i'm hoping there's a simple explanation for this and the filter can be made more reliable. any thoughts? The filters are reliable, so there may be a problem with the data. For example, a space at the end of a text string can cause two cells that look the same to be extracted by the filter. Perhaps you can give an example of what's left out, ...

set a value to another cell
how to set(not get) a cell value to another cell? is it possible? A cell formula can get, but only a macro can set. Some exceptions (manually): Scenarios, Goal Seek, Subtotals, Text to Columns and Advanced Filter, none of which is applicable to your situation. -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "news.microsoft.com" <whoknows@microsft.com> wrote in message news:%23kjDDZRoIHA.264@TK2MSFTNGP05.phx.gbl... > how to set(not get) a cell value to another cell? > is it pos...

Displaying add'l value in Combo box
I have a combo box that uses a table as its row source. When the combo box displays, I would like it to display an add'l value not in the table, e.g. "<All>" I find the easiest way is to provide a listfill function. Here's mine: Public Function AddAllToList(c As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant ' ListFill function - use as RowSourceType for listbox ' Set listbox.rowsource to correct sql ' Set listbox.tag to (eg) 2;<<All>> to override defaults: ' 1;( All ) Static rs As Recordset...

How to get 2 Max values?
I have following 2 tables (T1, T2) and rows: T1 (Column: Id) ---- 1 2 3 4 T2 (Columns: Id, Value) ---- 1, 1 1, 2 1, 3 1, 4 2, 1 2, 2 2, 3 2, 4 3, 1 3, 2 3, 3 3, 4 4, 1 4, 2 4, 3 4, 4 The result-set I need is: --------------------------------------- 1, 3 1, 4 2, 3 2, 4 3, 3 3, 4 4, 3 4, 4 i.e for each Id from T1, I need 2 max values from T2. How do I write the query? Will this work? Select t1.Id, ( Select t2.Value From t2 Where t2.Id = t1.Id Order By Value Desc Limit 2 ) From t1 I keep getting subquery returns more than one...

Finding the Value in the "last" cell?
I have an excel file that has a vba macro that grabs data from someplace else every few minutes and writes the data to the next empty line on a sheet "n". SInce the macro just appends the info to the sheet when event x is triggered I never know what the last line (row number) of the sheet will be. I need to have cell a1 (for example) give me the data is whatever is *currently* the most recent (or latest appended) data from column E. Thus, if the macro five minutes ago was on row 245 and E245 had the text, "FRED" and now event x triggers the macro so a new line of data is w...

Determining Change in a cell Value....
Please i need some Help... Number (Col A) Start (Col B) End (Col C) Tp1203 01/20/2009 02/01/2009 Tp1203 01/20/2009 02/01/2009 Tp1203 01/20/2009 02/01/2009 Sb507 05/20/2009 06/10/2009 Sd702 07/08/2010 08/20/2010 Tp1203 01/20/2009 02/01/2009 Tp5203 01/10/2010 01/30/2010 Sb507 05/20/2009 06/10/2009 Sb507 05/20/2009 06/10/2009 This is what I want to have as my final result Tp1203 01/20/2009 02/01/2009 Sb507 05/20/2009 06/10/2009 Sd702 07/08/2010 08/20/2010 Tp5203 01/10/2010 01/30/2010 How do I check the values in ...

Giving Null a value in a VBA stamtement
Hi all, I am writing some VBA code in Access03 in a fields after update event. The field types are decimal (18,1). I am trying to write a SQL statement which looks like this: Docmd.RUNSQL UPDATE tblStorageShip SET tblStorageShip.AmountLeftmg = ([tblStorageShip]![NumberLeftAliquotSize1]*[tblStorageShip]! [SizeUGAliquotSize1])+([tblStorageShip]! [NumberLeftAliquotSize2]*[tblStorageShip]![SizeUGAliquotSize2]); And if one of the fields is a NULL i get a NULL return for that record. Can I somehow set NULL to euqal 0 for this set of code instruction so anytime it wencounters a null while exec...

Workflow : Can't see fields in dynamic values
Hi everybody, I have two entities , one of them is having picklist values. I want from workflow to update the another entity ( a particular field) , when i chose a certain value in the picklist . I can not see all the fields in Dynamic values. I am unable to see the fields of Owner and Account (these are lookup fields). If Account and Roles. Job function = Account Manager then update Account. Account manager= Account and Role. Owner ( i can not find Owner in dynamic fields) any suggestion ? ...

Graphing STRANGENESS
I am graphing two series of data using EXCEL using a line chart. The first series displays correctly and the second series is displayed as the SUM of the data for the first and second series. Has anyone ever seen this before? What causes this? Thanks, Barb Reinhardt Someone far wiser than I (thanks Jon) pointed out that I had a stacked line with markers. Oops! "Barb Reinhardt" wrote: > I am graphing two series of data using EXCEL using a line chart. The first > series displays correctly and the second series is displayed as the SUM of > the data for the fi...

Change axis values
Excel 2002. Spreadsheet chart plots a cycloid, with x=r(t-SIN(t)) and y=r(1-COS(t)). The chart produced is correct. However, I would like the x-axis to display the values of angles (either degrees or radians) rather than the values calculated by x=r(t-SIN(t)). Ideas?? TIA Paddy I'm not sure the request makes sense, but here is what I would do. I would keep the same XY plot you currently have, hide the X axis (set everything on the Format Axis/Patterns tab to none), and generate a series that has points where you want ticks and labels, and use this as your X axis. Here are a...

PDF Form Field Values
Is there a way using vba to simply extract the user input values from a PDF form so they can be 'Uploaded' into a database? Thank you, QB I don't know about the PDF per se, but you can certainly do this in Word. http://gregmaxey.mvps.org/Extract_Form_Data.htm If you have MANY PDF files that you need to do this for, look into converting them Word docs: http://www.pdftoword.com/ Then follow the steps in the first link I posted. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "QB" wrote: ...

Autofill: can it detect change in value?
Is there a way to autofill down a column, where the autofill detects a new value and uses that value, until the next time it hits a new value? Simple example: I want this: To look like this: Dave Dave Dave Dave Bill Bill Bill John John Try the below 1. Select the data range in ColA (say A1:A100). 2. Press F5. From 'Goto window'>Special> from Options select 'Blanks' and hit OK 3. This will select all blanks. 4. Now press = (equal sign) 5. Then press Up Arrow to reference the cell just above 6. Now press Ctrl and Enter ...

Typed in value changes?
Hi Folks , I am sure that I must have done something screwy and changed some options around but I have not been able to figure out what the problem is. The issue is that whenever I type a value into a cell it automatically divides that number by 100 and that is the value that is then displayed in the cell (ie typing 3 into the cell results in 0.03 being displayed). This just happened all of a sudden and it does not matter if I am in a file I previously created or a new file and it does not matter what column or row I type the value in. It is worth noting that if I open up previously...

Error: Value in Formula of wrong data type
I have a worksheet that uses custom formulas on several inputs from that page. I've used it for a while and never had a problem, but made some changes elsewhere in the worksheet (not linked to this part) and now on many of the cells using these formulas, I get a '#VALUE!' error, stating 'Value in Formula of wrong data type.' The data type is the same it has always been, so I don't know why it's not working now (formula just uses if thens to compare different numbers and return values like 0, 1, or 2). What's even more perplexing is that several ...

Inserting default value in part of input mask
I have a DB where user enters CourseName and Then CourseNumber (Primary key and Unique).. I run a code to check and display a custom error message if the value in CourseNumber is duplicate. The CourseName are actually abbreviations like AFF or PSCRB, or MFA etc... CourseNumber contains three parts i.e. CourseSerialNumberInCurrentyear +CourseName+last2digitsofCurrentYrneeds for example 001AFF09 or 029MFA09 or 010MFA10.. because of different user using the db they enter the same course no. in different formats like 001AFF09 or 001AFF2009 or 001/AFF/09 or AFF/ 001/09 and so many w...

Index / Match / value in a range?
Hi there, I have three colums of data in a worksheet from A1 to C5, example; Column A Column B Column C ABC 5/2/10 02:00 Dog DEF 5/2/10 02:15 Cat ABC 5/2/10 03:47 Mouse GHI 5/2/10 01:20 Bird ABC 5/2/10 00:11 Horse What I need to do in a seperate worksheet in A1 is to look up a value in column A, find its lowest figure in column B and return the corresponding value from column C. Then in A2 vlook up the same value in column A, find the SECOND lowest figure in column B and return the...

HELP! sum value to be displayed in sheet 2 when data is in sheet 1
How do I do this? any ideas -- Message posted from http://www.ExcelForum.com =SUM(Sheet1!A1:A100) -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Muppet >" <<Muppet.18bcuz@excelforum-nospam.com> wrote in message news:Muppet.18bcuz@excelforum-nospam.com... > How do I do this? any ideas? > > > --- > Message posted from http://www.ExcelForum.com/ > =SUM(Sheet1!$A:$A) as an example for sum of Column A in Sheet1 -- Message posted from http://www.ExcelForum.com ...

lookup to see if multiple values exist
Hi, I am faced with this problem. I have data as follows: Col1 Col2 Col3 S1 H1 Value 1 S1 H2 Value 2 S2 H1 Value 3 S2 H2 Value 3 I need to roll this data up so that I get ColR1 ColR2 S1 "Multiple" S2 Value3 Basically if S1 has multiple values then shown "Multiple". If it has the same value for all occurrences, as is the case for S2, show that value. These are formulas with dynamic data which I need to calculate automatically. I cannot use a pivot table. I do not want to use a macro unless I h...

updating field based on the value of another
I could (maybe) figure this one out myself, but at best it would take hours (for me); could I simple ask for a little assist on this: I have several tables: tblJob, with fields: txtItem tblJobItemInstallNotes, with fields: txtItem, txtJobInstallNote (each item in tblJob may have several notes) tblItemCatalog, with fields: txtItem tblItemCatalogInstallNotes, with fields: txtItem, txtItemInstallNoteTitle (each catalog item in may have several notes, referenced by their title) tblInstallNotes, with fields: txtItemInstallNoteTitle, txtItemInstallNote - the relatio...

Populate a cell if values in cell 1 and cell 2 match cell 3 and 4
I need help with a formula please. I need to search through a column of data and populate a cell if it meets a condition. For example: IF FirstWorksheet!A2 = SecondWorksheet!ColumnAx and FirstWorksheet!B2 = SecondWorksheet!ColumnBx Then populate SecondWorksheet!Cx with value from FirstWorksheet!C2 I have to match up a column of data in one worksheet with data in 2 columns in another worksheet. Help plz. Thanks T- A multi-criteria index n match (array-entered) should work here In Sheet2, Copy n paste this into C2's formula bar, then array-enter the formula by pressing CTRL+...

How do I sum a range of sub-totals?
I have spreadsheet with various subtotals. I need a grand total at the end, how do I sum a range of subtotal? Why not use the Subtotals feature? http://www.officearticles.com/excel/getting_subtotals_in_microsoft_excel.htm ************ Anne Troy VBA Project Manager www.OfficeArticles.com "Rhiannon" <Rhiannon@discussions.microsoft.com> wrote in message news:F9F8520C-7C88-4422-8766-9F3F2B581163@microsoft.com... >I have spreadsheet with various subtotals. I need a grand total at the end, > how do I sum a range of subtotal? ...