DSUM with Table Name from a control box

[bigdonk]

I am currently using:



=DSum("Sales1","2014","ID>0")+DSum("Sales2","2014" ,"ID>0")

in a control on a form to get me a quick total.

However, there is a combo box control to select a year at the top of the form. There is a different table for each year instead of all the data being on a single table with a year field where I could easily sort with criteria. There is an event macro to change the source table after updating the combo box. Rather than combining all the tables into one like I think it should be (causing a lot of work) I was hoping there was a way to choose the table from the results of a control box. I have tried everything I know and can't get it to work so I figured I would ask.

What I am trying to accomplish:

=DSum("Sales1",[" & Me.Year & "],"ID>0")+DSum("Sales2",[" & Me.Year & "],"ID>0")

I want the table in the DSUM to be base on the value of a control box [Year] on a form [Sales].

I hope I explained that correctly. I am tempted to combine all the years into a single table and then I can use criteria based on date, but I fear it will cause a ripple effect of other things that will need correcting.

Thanks for your help,
Don

[orange]

Fields with names like Sales1 Sales2 are typical of a bad table structure. But we don't know you, your business nor the application. I recommend you research Normalization.
Also Year is an access reserved word.

[bigdonk]

I simplified the wording for the post. The fields are appropriately named. Structure on the other hand I am not crazy about.

I am aware Year is reserved. I was just trying to show what I was trying to accomplish. I was using [" & Me.FiscalSales & "] and other variations and could not get it to work.

Before I make drastic changes, I was just wondering if its even possible to choose the table from a control on a form.

[John_G]

Hi -

I don't think there should be a problem. The DSum requires 2 or three string-type parameters, but they don't need to be hard-coded. I use a string variable for the WHERE parameter all the time.

Something like =DSum("Sales1", Me!Year ,"ID>0")+DSum("Sales2", Me!Year ,"ID>0") should work, provided me!Year is a string containing a table name (which you said it is).

I just ran these in the immediate pane, and as you see, it works fine.

x="Statement_Text"
?dcount("*",x)
140104

HTH

John

[bigdonk]

Its really strange as I can't get it to work. I am going to take a little look at combining the tables into 1 and see what kind of damage it does as I would be able to easily fix this with criteria choices.

[bigdonk]

I was able to successfully merge the tables and am able to accomplish everything like I wanted. I appreciate all your help.