By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,651 Members | 1,908 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,651 IT Pros & Developers. It's quick & easy.

Finding Oct-2007 record within 2 Date fields

P: 2
Hello... I have a big dilemna and I've been struggling for days how to do it

I have 2 date fields. StartDate and EndDate.

I am trying to get all records where the StartDate is Oct-2007 or EndDate is in Oct-2007 as well as records that may be INBETWEEN StartDate and EndDate in Oct-2007 (example: StartDate = July-2007 and EndDate=Dec-2007)

since I don't know Visual Basic i need to write this in a query or filter it on a report. Any help is GREATLY APPRECIATED!

If you could send this to me as an IIIF statement that would be great.
Oct 12 '07 #1
Share this Question
Share on Google+
3 Replies


nico5038
Expert 2.5K+
P: 3,072
You'll need to test the EndDate to be >= 1-oct-2007 and the StartDate to be <= 31-oct-2007.
As Access uses the US dateformat in queries this will look like:
Expand|Select|Wrap|Line Numbers
  1. select * from tblX where StartDate <= #10/31/2007# and EndDate >= #10/01/2007#;
  2.  
Getting the idea ?

Nic;o)

Hmm, almost forgot to mention the fact that this won;t work when the EndDate can be empty (Null). Be sure all EndDates (and StartDates) are filled !
Oct 12 '07 #2

Scott Price
Expert 100+
P: 1,384
Good afternoon and welcome to the Scripts!

What have you tried so far? If you could post us the SQL statement that you are working with, we'll try to give a hand.

Regards,
Scott
Oct 12 '07 #3

NeoPa
Expert Mod 15k+
P: 31,487
You'll need to test the EndDate to be >= 1-oct-2007 and the StartDate to be <= 31-oct-2007.
As Access uses the US dateformat in queries this will look like:
Expand|Select|Wrap|Line Numbers
  1. select * from tblX where StartDate <= #10/31/2007# and EndDate >= #10/01/2007#;
  2.  
Getting the idea ?

Nic;o)

Hmm, almost forgot to mention the fact that this won;t work when the EndDate can be empty (Null). Be sure all EndDates (and StartDates) are filled !
In case you didn't notice (as the logic isn't obvious to all), Nico's reply does what you need it to. If you do have the possibility of nulls (unfilled) date fields in your recordset then post back explaining how you would like those records to be considered (StartDate=Null ==> earliest possible date or maybe StartDate=Null ==> same as EndDate) and we can show you how to set up the WHERE clause to handle that.
Oct 12 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.