If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Sum last years sales

I have a form that I enter dates into to bring up a sales report that figures the sales from the dates that I enter. On my report I have an unbound control that I would like to sum last years sales for the same month of my sales report.

Lets say the report is for January.The report would give us detail information for this January but, it would let our salesman see for example "What total sales we did last january".

I am sure there is an easy way, I just can't figure it out.

Also if there is a way to make an unbound control to keep the running total of sales or Year to Date sales. That would be nice!!

I use a form to enter dates. I hit preview form and it shows a sales report between the dates I entered.

Criteria in my Query that is under Invoice Date field:
Is Not Null And Between [Forms]![AFrmSalesReport]![BeginningDate] And [Forms]![AFrmSalesReport]![EndingDate]
It does this fine.

The salesmen enter dates 1/1/04 to 1/31/04 for January. I would like to have a control automatically sum Last years January sales so he can compare them.
Also it would be nice to have 2 controls for this years "year to date" compared to last years "year to date"

Thank You

Thank You so much! tcace I appreciate it!

What about setting up a "year to date" control? Could I have it figure a sum of sales based on the dates I put in my form, figure a year to date starting at the begining of 2004.
Say for example I want a sales report for March
I put in my form Beginning 3/1/04 date
and the my Ending 3/31/04 date
Then hit my preview report button and:

I have all the sales listed by date for march listed by salesmen,
then a control that has the last years march total,
then this years year to date,
then last years year to date?
Your last reply gave me the answer for last years march, what about year to date figures??

If you are using the equations in the controls on the form, which it sounds like you are, then it won't let you use variables.

There are 2 ways to do this:

1) Use a code based function (put the function in the code behind the report)

2) Use Text Boxes in place of your variables.

To do option #2, try this:

Have a Text Box for the day, month and year. Use TheDay, TheMonth and TheYear as the names of those text boxes. You can set these to Not Visible if you want.

Add a new text box for "This month's sales" and put the following in as the control's source:
=DSum("[InvoiceTotal]","[SalesJanuary2004Qry]","[TheMonth] = " & [TheMonth] &" And [TheYear] = " & [TheYear])

Add a new text box for "This month's sales last year" and put the following in as the control's source:
=DSum("[InvoiceTotal]","[SalesJanuary2004Qry]","[TheMonth] = " & [TheMonth] &" And [TheYear] = " & ([TheYear] - 1))

Add a new text box for "YTD sales this year" and put the following in as the control's source:
=DSum("[InvoiceTotal]","[SalesJanuary2004Qry]","[TheYear] = " & [TheYear])

The last one is the most complicated; here's what it asks:
"([TheYear] = 2003) AND ([TheMonth] < 4 OR ([TheMonth] = 4 AND TheDay <= 14))” if you copy it into Word and shake it down.

I noticed that you had a single quote where a double quote belonged - probably why it didn't work. I also added spaces; strictly speaking, spaces are usually not required because Access will put them in when it compiles it, but not always - if it can decifer where the spaces belong, it will fail.

Personally, I would use code based functions - they're more efficient, easier to develop and debug, and a lot more powerful.

#Error

One thing I found out was I was using a Listbox instead of a text box. I switched that. Now I get an #Error when I enter the code you gave me. I added TheMonth and TheYear fields from the SalesJanuary2004Qry. Did I need to create just text boxes with those names?

Sorry

I know you probably think I'm an idiot. I just have never had luck using Dsum in any document.

I am Sorry!

I am going to go input your original line of code with correct spacing.

I do have the texct boxes TheMonth and TheYear on my form. Actually, when I open the form the data fills in properly.

Originally I was just entering sales for the current month. Now I use a form to enter dates and it previews the report with the dates I enter in the parameters of the Query. Under the invoicedate field I have criteria that states:
Is Not Null And Between [Forms]![AFrmSalesReport]![BeginningDate] And [Forms]![AFrmSalesReport]![EndingDate]

I have another query (WOSalesQry) that has every work order we have written. I used your code (DSum) and IT WORKED!!! But it gave me total sales for work order we ever did???

So I am perplexed? I know your probably tired of trying to help, but i truely do appreciate it!!