Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    More Date Parameters (2002 SP-2)

    Just kicking this around (not quite sure how to go about it).
    I would like to create a report (because I suspect a continuous form won't work) that will compare current month (current year) data with current month (previous year) data. I would also like to to the same for current week. I don't quite get the date parameters. I have the following for current month (current year).
    Between (DateSerial(Year(Date()),Month(Date()),1)) And (DateSerial(Year(Date()),Month(Date()),31))
    but I dont get how to also include last year data. This report would be used to compare sales for the periods described.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More Date Parameters (2002 SP-2)

    The criteria you have for the current month are not correct, since not every month has 31 days. It should be

    Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)

    (I also removed some superfluous parentheses)

    The analogous criteria for this month last year:

    Between DateSerial(Year(Date())-1,Month(Date()),1) And DateSerial(Year(Date())-1,Month(Date())+1,0)

    If you want to display both this year's and last year's data, you must create separate queries for each, and then either create a query that combines these, or create two reports, one based on each query, and put those reports as subreports on a main report.

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: More Date Parameters (2002 SP-2)

    Hans:

    Geesh, how typical is it for me to create little "traps" for myself? What sort of luck does a bloke have to create a parameter (thinking it includes all possibilities) only to discover that just "happens" to be that they reflect the month in which they were created? Grrrrr (can you imagine the look on my face on 6/1?) Thanks for the correction, and guidance toward the the solution. Needless to say, I'll probably be back with hat in hand.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: More Date Parameters (2002 SP-2)

    (Hat in hand)
    Going with the subreport idea, I have to wonder if I'm headed in the right direction. In the attachment with sample data (and presuming I want to compare this month with this month last year) the results are rather difficult to read. I quess what I'm looking for is an "alignment" of data; the number of loads on 5/1/03 would appear on the same line as the loads on 5/1/04 and so on. Not only that, but I would want to compare far more than the type of load (up to about 25 criteria; ie. loads by customer, region, etc.) I've never done anything with PivotTable/Chart views, or exporting to Excel; might that be a more versatile approach?
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More Date Parameters (2002 SP-2)

    I don't think pivot tables would help here.

    Do you really want to make a day-by-day comparison? February 29 will be a problem - Access (or rather Windows) automatically interprets February 29 in a non-leap year as March 1. If you aggregate the data by month, you wouldn't have this problem.

    If you do want to report by date, a solution would be to display all dates in both years, with blanks or zeros for missing data. A way to do that is to create a table with all dates, and use this in queries, with an outer join from this table to the table you already have.

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: More Date Parameters (2002 SP-2)

    Hans:

    Actually no, but I presumed the same issue would come up regardless. Now that I give it more thought (and even as I work in a seasonal industry) I don't recall there ever being a week where no business was transacted, and certainly never an entire month. Even if the occasion arose where I might want to look at one particular date, I'm sure the continuous form would not be that burdensome. "If you aggregate the data by month, you wouldn't have this problem" First, I have no idea how to do that (and would really need to do it by week to make any sense of the data); second, I already had issues with trying to create a query joining the two distinct date parameter queries (hence the sub-report format). As the data comes from the same field (txtShConDate1) I couldn't even figure out how to have them appear as two different fields (Period1:[ShConDate1] and Period2:[ShConDate1] ??) - that's just a guess. Even so, I can see where I would need considerable assistance from the Lounge to accomplish the end (rather detailed) result.
    As you know, I have a tendency to drag the unaware into my rather dismal cave of ignorance from time to time. <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More Date Parameters (2002 SP-2)

    On second thought, crosstab queries may help. I have attached a zipped database that demonstrates how to use a table with a list of dates to create a series of queries to compare data from this year and last year.
    Attached Files Attached Files

  8. #8
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: More Date Parameters (2002 SP-2)

    Hans:

    Wow <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>, that's so good I haven't got a clue how it works. . . <img src=/S/munch.gif border=0 alt=munch width=19 height=17> (I'll be back)
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: More Date Parameters (2002 SP-2)

    Hans:

    Still trying to figure out how it all works, but am getting closer I think. It seems to me that, at some point, the tblDate is going to expire (but I guess one could extend it for the next 25 years or so). The equipment type (ie. Liquid or Flatbed) is really only two of several different types of equipment selected by combo (yet all fall within the general category of either "Liq*" or "Fla*"). Also, there would be several other categories to sort on (ie. customer, region, etc); I can imagine 25 or 30 different queries/reports to accomplish this. The date parameters could be just about anything, but I think weekly, monthly, quarterly, annually would be all that would be required (daily just seems excessive, as you had mentioned). In the end, and considering all the queries/reports required, I have to wonder if the increase in db size is worth the effort. Just so we're on the same page, I attached my slightly modified (field names, table name) db. Don't get me wrong, I think the report is magnificent, I just have to wonder how far I would have to go to generate all that I would want (philosophical I suppose).
    Attached Files Attached Files
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More Date Parameters (2002 SP-2)

    If you need dates before 1/1/2003, or in the future after 12/31/2004, you will have to add them to the table. The easiest way to do that is to create a series of dates in an Excel spreadsheet, using Excel's fill down feature, then copy and paste them into the Access table.

    If you really want all those reports, it's going to be a lot of work, it's up to you to decide if it's worth the trouble.

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: More Date Parameters (2002 SP-2)

    Hans:

    Another thought and, again, something I don't completely comprehend. I have this continuous form with a footer returning just about anything I would ever want to know about the orders represented. It does not, however, have the ability to display comparisons (ranges, but not two different ranges). I have a number of filters saved as queries from the Filter by Form function. I have just noticed that the saved filter is only retaining the date parameters, and none of the other categories of the "master" query the form is built on. When the form loads, is it actually using two queries (data and date parameters)? If this could be captured, might it be sent to a report? I just want to make some "dynamic" decisions based on historical data - a kind of snapshot. If I could out-put the footer info to a report (actually two reports - one for each range) I think I could resolve the issue without building all those other queries (I already have queries for filters). Speaking of "snapshot", I suppose I could do just that - and remain in my cave forever ignorant (at least that's something I already know how to do) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More Date Parameters (2002 SP-2)

    All your filter queries return qfltMasterAllFiltered.*, i,e, all fields from qfltMasterAllFiltered, and specify selection criteria for the dtmShConDate1 field.

    I'm afraid it's not clear to me what you actually propose in this post.

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: More Date Parameters (2002 SP-2)

    Hans:

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>. . .that's OK, it's not clear to me either.

    Anyway, I've got bigger problems now. The queries I used last week aren't working correctly this week. As a few different sources were used to create the parameter queries (and I don't really understand them) I'm really confused. I have DateSerial, DateAdd, and Date()-Weekday. It seems to me that I should at least try to understand one of the methods before going any further, but I'm really beginning to wonder if I'm not wearing out my welcome by now.

    Current week: Between Date()-Weekday(Date())-6+7 And Date()-Weekday(Date())+7 <font color=blue>correct</font color=blue>
    Current week last year: Between DateSerial(Year(Date())-1,Month(Date()),-6+7) And DateSerial(Year(Date())-1,Month(Date()),+7) <font color=blue>wrong</font color=blue>
    3 days prior to current, 7 in future: Between DateAdd("d",-3,Date()) And DateAdd("d",+7,Date()) <font color=blue>correct</font color=blue>

    I can't locate a comprehensive tutorial on how these methods work, or even which is prefered. Got any reference sources? Have I driven you over the edge yet?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More Date Parameters (2002 SP-2)

    You asked for a tutorial before in <post#=368316>post 368316</post#>, and my reply now is the same as then: as far as I know, there isn't one. You can look up the DateSerial, DateAdd and DateDiff functions in the online help for VBA, and study examples you can find here in the Lounge and elsewhere. Beyond that, it is a matter of logical thinking.

    The condition for current week this year (i.e. the week from Sunday through Saturday containing today) is:

    Between Date()-Weekday(Date())-6+7 And Date()-Weekday(Date())+7

    For today (05/10/2004), this results in "Between 05/09/2004 (Sunday) and 05/15/2004 (Saturday)"

    What is the current week last year?

    If you want the week from Sunday through Saturday containing the day exactly a year before today, you must replace Date() (that is, today) by the date exactly one year ago. That date is DateAdd("yyyy",-1,Date()). You must replace all four occurrences of Date() by this date:

    Between DateAdd("yyyy",-1,Date())-Weekday(DateAdd("yyyy",-1,Date()))-6+7 And DateAdd("yyyy",-1,Date())-Weekday(DateAdd("yyyy",-1,Date()))+7

    For today (05/10/2004), this results in "Between 05/04/2003 (Sunday) and 05/10/2003 (Saturday)"

    If you want the week whose first and last day are exactly one year before the first and last day of the current week this year, you must subtract one year from the original expression:

    Between DateAdd("yyyy",-1,Date()-Weekday(Date())-6+7) And DateAdd("yyyy",-1,Date()-Weekday(Date())+7)

    For today (05/10/2004), this results in "Between 05/09/2003 (Friday) and 05/15/2003 (Thursday)"

  15. #15
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: More Date Parameters (2002 SP-2)

    <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15> I'm trying; I really am.
    "Beyond that, it is a matter of logical thinking." Now I'm really in trouble! Thanks for your help; I'll try to figure all this out. Sorry for the repeat tutorial question, I had forgotten I had asked it (getting too stressed out I suppose).
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Page 1 of 2 12 LastLast

Posting Permissions

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