Query: return all records that dates fall between two dates fields
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.
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:
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.
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.
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.
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?
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.
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.