Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    18

    Unanswered: Hoping to finish DB today, last few problems..

    i) How do I add 60 minutes to a time in a query?

    e.g. End_Time: [Kick_Off_Time]+

    Then what..?

    ii) How do I query a table to see which customers have not made a booking in the last month?

    I would need CustomerID and BookingDate, but how do I work out if they HAVE NOT booked anything?

    iii)How do I add a month to a date?

    Cheers in advance..

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    for the first and third, use the DateAdd function
    For the second issue, create a query that lists all bookings for the past month (using dateAdd("m",-1,BookingDate) if needed).

    Next, create a second query that does an outer join between the customers and Bookings table showing all records from Customers. Put Is Null in the criterion for CustId in the Bookings table.

    Your other alternative is to use Not In (Select CustID from PastMonthBookingsQy) as your criterion. The first method is not updateable but is faster.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    For (i) and (iii) lok at DateAdd function specifically the "m" and "n" intervals respectively ...

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Actually, M Owen has it backwards. m is for month and n is for minutes.

    it should have been:
    For (i) and (iii) lok at DateAdd function specifically the "n" and "m" intervals respectively ...

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    OOPS! Long day ... Although in my defense I was not specifying to which question each interval relates to even tho it appears that way ... I gotta break out the ol english book again ... I'm slacking off it appears.

    - Mike

  6. #6
    Join Date
    Nov 2003
    Posts
    18
    I don't understand the DateAdd function, can you explain please.

  7. #7
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51
    look in your help. Access has a pretty good description of this function and how to use it.
    Bob

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    DateAdd: Function that returns a date.

    Parameters: Interval, Increment/decrement amount, Source Date

    Example:

    Dim MyDate As Date

    MyDate=DateAdd("m",2,Now)

    Returns: Date that is 2 months from now ...

    Got it?

  9. #9
    Join Date
    Nov 2003
    Posts
    18
    Originally posted by M Owen
    DateAdd: Function that returns a date.

    Parameters: Interval, Increment/decrement amount, Source Date

    Example:

    Dim MyDate As Date

    MyDate=DateAdd("m",2,Now)

    Returns: Date that is 2 months from now ...

    Got it?
    How would I use that in a query to get anything in the last month?

    >=DateAdd("m",-1,Now())

    ??

  10. #10
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    >=DateAdd("m",-1,Now()) is fine if there are no dates beyond today's; otherwise, to get everything from Nov 10 to Dec 10 today, in your criteria put:

    Between DateAdd("m",-1,Date()) and Date()

    If you want everything from November, then that is more work.

Posting Permissions

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