Results 1 to 13 of 13
  1. #1
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90

    Query: return all records that dates fall between two dates fields

    Hi everyone.

    I have been wrestling with this problem for a whole day and now I'm out of ideas, please could somebody help me.

    I work in a travel agent, and am designing the 'invoices in' db. I have a table with Arrival_Date and Departure_Date fields, referring to when tourists arrive and when they depart. Invoices are paid for all bookings within a certain specified period, so I need to retrieve all records where both start and end dates fall within that period, say, 30/4/09 - 31/05/09; and then mark them all as 'paid'.

    1. This needs a parameter query, doesn't it?
    2. I'm using european date conventions.
    3. I would like to enter the parameters in a form (via a combo box), but am regarding that as step 2 at the moment, until I can get the query to work.
    4. The best way for the results to be returned would be on a continuous form, so they can be individually checked 'paid'... or...
    5. The Dream Scenario - these returned invoices are automatically marked 'paid' by checking checkbox.

    I can follow instructions with VBA but I am self-taught in Access, so there are gaps in my knowledge, please be patient. Many, many thanks.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    971
    In no particular order...
    1. Yes, this will need a parameter query. Assuming that your table does not allow departure dates before arrival dates, something like this should work:
    Code:
    SELECT [Fields]
    FROM [Table]
    WHERE (((Arrival_Date) BETWEEN #[First date]# AND #[Second date]#)
    AND ((Departure_Date) BETWEEN #[First date]# AND #[Second date]#))
    However, this will prompt for four dates.
    3. To avoid this happening, build a custom form with text boxes (or combo boxes, if you have restricted lists of valid dates) for the dates to be entered. Then you could replace [First date] and [Second date] in the query with [Forms]![Form_Name]![txtFromDate] and [Forms]![Form_Name]![txtToDate] (change the control names to match your conventions).
    2. Always pass date values as strings, with the month component in letters. Access and VBA will do their level best to screw you over by treating any ambiguous date as US format. To avoid this, hold the date as a string as much as possible. (This is something that hard and bitter experience has taught me!)
    4 and 5. Assuming that your table has a yes/no field for "Paid", this bit is easy. Build your form on the above query, adding a statement within the WHERE clause to exclude paid invoices. Add a button to the dates form to open this new form, and it will display the relevant records. Then you can click off the paid ones and close the form.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Assuming Arrival Date cannot be later than Departure Date (if it can then you probably have other problems ):
    Code:
    WHERE Departure_Date <= [Second Date] AND Arrival_Date >= [First Date]
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Thank you for reply.

    I have not instructed my table to dis-allow departure dates before arrival dates - is it really necessary, and how do I do this?

    Where do I enter the code you suggested? I'm sorry, I need a bit of help.

    I have built the custom form as suggested. However, you say 'always pass date values as strings' and I don't know what this is, or how to do it.

    I'm sorry to come back to this all confused, I have tried to do it.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Quote Originally Posted by LisaP
    I have not instructed my table to dis-allow departure dates before arrival dates - is it really necessary
    You tell me. Database design is determined by your business rules. If this is a business rule, then yes. Otherwise, no. In your business, is it acceptable for a departure to occur before an arrival?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Well, there will be only one user for this db so the chances of that kind of data entry error are quite low. If one invoice is dated incorrectly, it will be found elsewhere in the business system. If it is necessary to get my date queries to work then I want to do it, otherwise I might not bother - do please remember that I am an amateur trying to do a professional's job with Access, and trying to do it quickly too. Non-critical corners may be cut.

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    971
    I am an amateur trying to do a professional's job with Access
    Like so many of us!

    By 'pass date values as strings', I mean that rather than supply dates as 01/04/09, you use 01 April 2009. This avoids problems with dates that are valid in European and American formats. The controls on the form to hold the dates should therefore be set to Long Date, and variables in VBA to hold them should be strings rather than dates.

  8. #8
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Ok so now I'm a bit excited because I think I'm on to something. Have changed date formats to 'medium date' because 'long date' includes the time. Also found a silly mistake in the table, the date fields were set to text. *blush*

    Please tell me where to put the code you mentioned in your first post?

    Many many tks

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    971
    The code is a vague form of the SQL statement that will populate the form. You'll need to replace [Fields] with a list of the fields that you want to show in the form, and [Table] with the table name.
    Alternatively, you can use the Query designer to drag and drop the fields that you need, and just enter the form control names as criteria below the date fields.

  10. #10
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Good morning. I'm sorry, I just can't figure out where to put that code, I've put it here as shown on the image, but it doesn't work (of course not!)

    You mention 'form', the code DOES need to go in a query, and not in a calculated text box on a form, doesn't it?

    I've made the form with the combo boxes for dates, they are: [Forms]![formDate]![ArrivalDate] and [Forms]![formDate]![DepartureDate].

    I do hope you can help again.
    Attached Thumbnails Attached Thumbnails query.gif  

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    971
    If you're using the Query designer, drag the arrival date and departure date into the grid as well. Looking back, I think I prefer pootle_flump's idea, so once you've done that, put the following criteria in:
    Arrival date - ">= [Forms]![formDate]![ArrivalDate]"
    Departure date - "<= [Forms]![formDate]![DepartureDate]"

    If you run the query without formDate open, you'll be prompted to supply the arrival and departure dates as per a normal parameter query.

    Good luck!

  12. #12
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Works like a dream! Thank you so much for your help, weejas and pootle flump.

  13. #13
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    971
    You're welcome!
    Thank you for letting us know!

Posting Permissions

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