Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Unanswered: Query Last years month

    I would like to use this years month sales in a report or form to get last years sales for the same month using the month and year. How can I use SQL in a query to get this information?

    last years

    Michael
    Gotta to do some code

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    somewhere in your application you have used Date() or Now() to get the month/year for the current month year.

    do exactly the same, but where you had date() put
    DateAdd("y", -1, Date())


    ...similarly if you used Now()


    izy
    currently using SS 2008R2

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

    Oops

    I am sorry I was not clear. I have a beginning date and an ending date that I enter in to a form. ie. 1/1/04 to 1/31/04
    So for example I would like the code to look up the month and year in the beginning or ending date and provide last years January sales.

    Sorry I was not very clear!


    Thanks for the quick response also...



    Michael

    Also I need it to recognize Null until the beginning date has been filled in!!
    Last edited by surfacesys; 04-19-04 at 11:49.
    Gotta to do some code

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the same answer applies.

    oldBeginDate = DateAdd("y", -1, BeginDate)
    oldEndDate = DateAdd("y", -1, EndDate)

    ...and do whatever you do for the current month but with these "old" values

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    recognize null doesn't speak to me.

    you mean do nothing if there is a null??

    private sub whateverIsRunningThings()
    if isnull(beginDate) or isnull(endDate)
    'do nothing
    else
    ...run your code/query/whatever
    endif

    izy
    currently using SS 2008R2

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

    Minus a day

    I entered the code you gave me:
    =DateAdd("y",-1,[EndingDate])
    Example I enter 1/31/04
    I gives me a response back of 1/30/04

    Beginning does the same thing??
    Gotta to do some code

  7. #7
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    I enter -365 but with this year being a leap year, things can really get messed up.

    Help?

    I looked in Access 2000 help and it gave the exact thing you did in the sample except it used "m" instead of year? I am not sure what I am doing wrond?
    Gotta to do some code

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    well, let's stick to stuff that seems to work: try
    dateadd("m", -12, [EndingDate])

    this ought to be the same as
    "y", -1
    but i don't check these things before i write 'em.

    izy
    currently using SS 2008R2

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

    Smile Fantastic!!!

    Thank You soooooo much!!!

    I always see you helping people on this forum! We appreciate your help.

    Just another question, how would I go about getting say... the YTD (year to date sales) for this year and last?


    Michael
    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
  •