How to put in formula that will add values of selected fields in criteria.

[USMCTL]

Hello, looked for question in the forum for previous post, didnt see anything. I am trying to make a distribution report. User prefers to use a file that has sales for every month. There is a field for each month sales. Normally, on my query I would have an sum field with the formula like [TABDDATA_IVVSUM]![IVSQ01]+[TABDDATA_IVVSUM]![IVSQ02]+[TABDDATA_IVVSUM]![IVSQ03]. Sum months 1-3 basically. Since I know how to use access, I would go in that query and just change the number of months and run query. However, I am making a form that is user friendly for my coworkers who have no clue about access and it would be overload for them to try to do that. On the form, i would like the, to be able to choose the months (fields IVSQxx + IVSQxx ect) on a list box, then have those months (values in the field) be summed as if it would in my formula field. How can I make this possible?

[Micron]

You're not saying the months are listbox columns (i.e. 4 months, 4 columns)? If so, that's not good. If they are list items and a user is picking from the list, you need to iterate over the selected items and build an expression with + in between the items. That assumes the items are field references or numbers. If you search 'loop over listbox selected items' you'll find lots of code examples.

[USMCTL]

They are columns. This is coming off a table that has a row for each account. User will select only one account he/she enters in text box. A column for month 1, month 2, month 3..ect. Each one has the number of items sold for that month for that row(acc) How do i make something like i would in the query for a formula builder [TABDDATA_IVVSUM]![IVSQ01]+[TABDDATA_IVVSUM]![IVSQ02]+[TABDDATA_IVVSUM]![IVSQ03] . Is it possible I could just run all sales for an acc,...using normal main form. Then a separate form or popup that allow user to add certain colums together?

[Micron]

Something's not right there. Probably lack of db normalization. You will have to loop over the field or columns which IIRC are both zero based.

[USMCTL]

I see I can make an expression in the list box. How to I let the user choose what months to add together?

[USMCTL]

I can do it in a query just cant figure out how to do it in a form. eek

[Micron]

You can retrieve list item column values using code, but you cannot select items from listbox columns using mouse/keyboard. You can only select listbox rows. If you select one or more rows, you can loop over the columns and string values together but trying to select only certain columns would be tricky. The whole thing would be so much easier if your design was normalized. Either it is not, or it is but something you've done with the listbox rowsource has "de-normalized" your data. The months should be in rows, or you ought to step back and create a better approach.

There is a field for each month sales.

That seems to be the crux of your problem. See at least the first 2:
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html