Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Question 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!!
    Gotta to do some code

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There's a TON of ways to do this.

    Could you provide a bit more info on how the information is queried, what format you'd like it in etc?

  3. #3
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    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"

    Any help would be greatly appreciated!!!!!

    Michael
    Gotta to do some code

  4. #4
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    One Way

    I just need one of those ways
    Gotta to do some code

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Here's an easy method that spares you all the "Date Math":

    In your query, add a couple of fields:

    TheMonth:Format([DateField],"m")
    TheYear:Format([DateField],"yyyy")

    Then, you can use domain functions (among other things) to do the math on specific ranges.

    For example, to see Jan's sales last year, use:

    =DSum("[SalesNumber]","[SalesQuery]","[TheMonth] = " & intMonth & " AND TheYear = " & intYear)

    SalesNumer is the field with the data you want added
    SaleQuery is the name of the query with the info
    intMonth is a variable you placed the month in
    intYear as a variable you placd the year in

    Have fun!

  6. #6
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    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??

    Anyone's help would be greatly appreciated!
    Gotta to do some code

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    you can use the same statement as my last reply, just modify the criteria.

    For example, for YTD, your criteria is "This Year", "<=This Month":

    =DSum("[SalesNumber]","[SalesQuery]","[TheMonth] <= " & intMonth & " AND TheYear = " & intYear)

    For YTD last year, you need to consider the DAY as well. Add to your query:
    TheDay:Format([DateField],"d")

    And the domain function becomes:
    =DSum("[SalesNumber]","[SalesQuery]","[TheDay] <= " & intDay & " AND TheMonth = " & intMonth & " AND TheYear = " & intYear)

    Obviously, there are date functions, "DateAdd" and "DateDiff" to name a few, but sometimes simplicity is best.

    Have fun!

  8. #8
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    So close

    I can tell I am getting close!! Do I need to put in my DSum function something in place of intMonth and intYear?

    I have everything you listed but it shows up empty.

    =dsum("[InvoiceTotal]","[SalesJanuary2004Qry]",'[TheMonth]="&intMonth&" And [TheYear]="&intYear)

    Sorry if I seem dumb here, I am fairly new!

    Michael
    Gotta to do some code

  9. #9
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Question

    I tried to enter just:
    =DSum("[InvoiceTotal]","[WOSalesQry] ") and it would not give me a total for all invoices... even without the parameters???
    I don't know why DSum won't work on my report??

    Help!
    Gotta to do some code

  10. #10
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Question Quotes

    Am I missing puntuation marks? Or other mistypings possibly?

    Michael
    Gotta to do some code

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    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])

    and finally, add a new text box for "This YTD sales last year" and put the following in as the control's source:
    =DSum("[InvoiceTotal]","[SalesJanuary2004Qry]","([TheYear] = " & ([TheYear] - 1) & ") AND ([TheMonth] < " & [TheMonth] & " OR ([TheMonth] = " & [TheMonth] & " AND TheDay <= " & [TheDay] & "))")

    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.

    Good luck!

  12. #12
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Question No Luck

    I have entered the data you gave me. It just shows up blank???

    Here is my code you gave me:
    =DSum("[InvoiceTotal]","[SalesJanuary2004Qry]","[TheMonth]=" & [TheMonth] & "And[TheYear]=" & ([TheYear]-1))

    I entered it in to the text boxes control source

    What do I have wrong???
    Gotta to do some code

  13. #13
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Wink #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?

    Thanks in advance!
    Michael
    Gotta to do some code

  14. #14
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    The error means: a field name is missing and/or the syntax is wrong.

    To use:

    =DSum("[InvoiceTotal]","[SalesJanuary2004Qry]","[TheMonth]=" & [TheMonth] & "And[TheYear]=" & ([TheYear]-1))

    in a text box, you need to have fields named:
    InvoiceTotal, TheMonth and TheYear in SalesJanuary2004Qry

    and, have text boxes named:
    TheMonth and TheYear on your form.

    In addition, there is a space missing above; it should read:
    "And [TheYear]="
    I noticed that you compressed all of the spaces out of the code I offered; may I ask why?

    Finally, if your query only has sales for January 2004 as the name implies, then you won't get any results back for 2003.

    Hope this helps.

  15. #15
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    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!!
    Gotta to do some code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •