Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting dates for a subform

    Access 97 - I'm creating a form where I would like to be able to display the number of nights a guest has been housed this month and one that shows last 7 days. For the last 7 days I created a Top 7 query and based a subform on the results. Is that the best way? I shut off navigation buttons, scroll bars, etc.

    Also, for the guests housed this month what would the logic be if the field were named DATE. I'm imaging I would somehow combine the DATEPART and NOW fields???

    Thanks!! Everyone is always so helpful - I don't like wasting space by sending back a thank you message if everything works fine!!

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting dates for a subform

    Just one comment regarding your logic, if you use Top 7 you will return the last 7 "dates" not necessarily the past week. If you truly want to interrogate the past 7 "days" you should use the DateAdd function "=DateAdd("d",-7,Now()" to determine the start of your query. And, yes I believe you're on the right track regarding current month, if you need more clarification, just post.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting dates for a subform

    I truly want to know the last 7 dates, not the last 7 days (or past week). Here's the logic I'm looking for. A homeless shelter will house you 7 nights in one month - they don't have to be consecutive. Thus, Top 7. But, I'm still looking for what dates have I been there just in this month. Is there some sort of DatePart logic I can use?

    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extracting dates for a subform

    Try something like this:

    Expr1: Month([VisitDate]) = Month(Date()) AND Year([VisitDate])=Year(Date())

    If you put that into a calculated field in a query, it will give you a true where your visit date field (don't call it DATE, please, that's a reserved word) is in the same month and year as the current date. Then in the criteria line below the field, put True. That will filter out any visits in a different month or year.

    If you wanted to specify the month and year to compare, put something like [Enter comparison date] in the expression instead of the date function, and the query will prompt you for the date to use as the comparison parameter.
    Charlotte

Posting Permissions

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