If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Query: return all records that dates fall between two dates fields

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 970
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.
Reply With Quote
  #3 (permalink)  
Old
King of Understatement
 
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:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #5 (permalink)  
Old
King of Understatement
 
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:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 970
Quote:
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.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 970
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.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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
Query: return all records that dates fall between two dates fields-query.gif  
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 970
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!
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: May 2009
Location: Crete, Greece
Posts: 90
Works like a dream! Thank you so much for your help, weejas and pootle flump.
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 970
You're welcome!
Thank you for letting us know!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On