Linking reports?

[June7]

I did suggest earlier should move the Sum([Total]) to report footer.



Enter 8/16/2011 into both textboxes. Press Enter from End Date box. Yes, will go back to the Start Date box, that is okay.

Now open the report. The DSum will calculate all values prior to 8/16/2011. The Sum([Total]) will calculated the values of records on the report.

Some functions and code require that values be delimited. Text would be apostrophe, dates are #, numbers don't require anything. The DSum doesn't work if the # delimiters are removed.

[Japandave]

Now open the report. The DSum will calculate all values prior to 8/16/2011. The Sum([Total]) will calculated the values of records on the report.

Ahh, now I see. It has been working all along. But I think I may have confused you so what.

I don't need "All" the previous values sumed, I just need one months prior.

Eg, Last months invoice (All products sold between 2011/Sep/16 and 2011/Oct/15) was $1,000. On the next months invoice total products sold dated between 2011/Oct/16 and 2011/Nov/15 is $2,000. In the header I need to have lasts months invoice total of $1,000 carried over to this months invoice and also have the total for this months. It will always have only the previous months total in the header. So next months header will have $2,000 and what ever that months total is.

Some functions and code require that values be delimited. Text would be apostrophe, dates are #, numbers don't require anything. The DSum doesn't work if the # delimiters are removed.

Thanks for the explanation.

[June7]

Okay, then fix the Where argument of the DSum. Do you want it to always be from the 16th to the 15th? You want the report to always be based on 16th to 15th? If this is the case, don't let users enter full date, and enter only the start month. The rest can be calculated.

[Japandave]

Okay, then fix the Where argument of the DSum. Do you want it to always be from the 16th to the 15th? You want the report to always be based on 16th to 15th? If this is the case, don't let users enter full date, and enter only the start month. The rest can be calculated.

Yes, always from the 16th to the 15th of each month. If I new how to modify the Dsum I would, but unfortunately I am not quite there yet. Do you know a good place to find tutorials? I have of coarse, got what I have leart so far off the net, but there seems to be few sites that deal with Expressions and VBA for Access.

[June7]

On form:
Two textboxes, one for month one for year, or one textbox with input mask: 00"/16/20"00;0;_

With the masked textbox, DSum would be:
PrevTotal: DSum("[Row8]*1.2","[Sheet1]","Row1 >= #" & DateAdd("m",-1,[Forms]![DateForm]![StartDate]) & "# AND Row1 <#" & [Forms]![DateForm]![StartDate]) & "#")

The criteria in the query would be:
Row1 >= [Forms]![DateForm]![StartDate] And Row1 < DateAdd("m","1",[Forms]![DateForm]![StartDate])

[Japandave]

On form:
Two textboxes, one for month one for year, or one textbox with input mask: 00"/16/20"00;0;_

With the masked textbox, DSum would be:
PrevTotal: DSum("[Row8]*1.2","[Sheet1]","Row1 >= #" & DateAdd("m",-1,[Forms]![DateForm]![StartDate]) & "# AND Row1 <#" & [Forms]![DateForm]![StartDate]) & "#")

The criteria in the query would be:
Row1 >= [Forms]![DateForm]![StartDate] And Row1 < DateAdd("m","1",[Forms]![DateForm]![StartDate])

I really tried getting this to work. I spent at least two hours today on this trying various things to get it to work. Could you possibly post a working example so I can see where I am going wrong?

[June7]

Okay, check this. I will remove after you respond or in a few days. I deleted data trying to get the file small enough not to require zip.

[Japandave]

Okay, check this. I will remove after you respond or in a few days. I deleted data trying to get the file small enough not to require zip.

Wow, really snowed with work of late, doing 16 hours days, but I am finaly on top of things again in hope.

Ok, June, thanks so much for the demo file, I have not looked at it yet. But I will have a look and get back to you.

[Japandave]

Hey June, thanks so much for that DB. I have looked at it and for some reason the previous month always seems to calculate one day less then what is in that month.

I imported some data that only had a value of 1 in row 8 for entry, so for the previous month, if it was a 30 day month the total will be 30days * 1 * 1.2= 36 . But I always end up with a total of 34.8, ie one day short. Same for a 31 day month, the total is supposed to be 37.2, but brings back a value of 36.

Cheers

Dave

[June7]

For some reason I thought the end date needed to not be inclusive.
Change the criteria in the report RecordSource to:
>=[Forms]![DateForm]![StartDate] And <= DateAdd("m","1",[Forms]![DateForm]![StartDate])

Or because BETWEEN AND is inclusive:
Between [Forms]![DateForm]![StartDate] And DateAdd("m","1",[Forms]![DateForm]![StartDate])

The point is, adjust the criteria expression until the results are what you want.