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

Date Ranges In Access

P: 3
Hey guys,

Perhaps you can help a fellow programmer out.
I have an application that requires me to find the various dates between two date ranges.

I am given a startdate and an endDate and I want to find all the various dates in between to populate a table.

How do I do this?


Talis
Oct 15 '07 #1
Share this Question
Share on Google+
7 Replies


Scott Price
Expert 100+
P: 1,384
You have inadvertently posted your question in the Articles section. I have moved your question across to our main Access forum where more of our resident experts will be likely to see it.

MODERATOR
Oct 15 '07 #2

Scott Price
Expert 100+
P: 1,384
As a general overview, you'll write a Select query that includes as Where criteria the two dates...

I.e.
Expand|Select|Wrap|Line Numbers
  1. Select * From [Table1] Where [DateField] Between #1/1/2000# And #1/1/2010#
Welcome to the Scripts!

Regards,
Scott
Oct 15 '07 #3

P: 3
Thanks for responding Scott.

I think you misunderstood the question.
What I'm trying to find out is how to display all the dates between two dates that I'm given.

For example:
If I have dates October 10th (10/10/2007) and October 14 (14/10/2007) I want to see a list of dates between these two, like October 11th October 12th etc etc.

Can you help?

Talis
Oct 16 '07 #4

Scott Price
Expert 100+
P: 1,384
As in a calendar? Or listed out in a list format?

There are various options if you want to use a calendar, Stephen Lebans has one that works quite well available for free here: http://www.lebans.com/monthcalendar.htm.

As for a list of each date, I'll have to think about that one... There is no native function in Access/VBA (that I know of) that provides this, so we'd have to create one in VBA. How experienced are you in working with VBA?

Regards,
Scott
Oct 16 '07 #5

Scott Price
Expert 100+
P: 1,384
Just playing around tonight :-)

Try this custom function:

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Function ListDates(StartDate As Date, EndDate As Date) As String
  4.  
  5. Dim MyList As String
  6. Dim MyDate As String
  7. Dim temp As Integer
  8.  
  9. If StartDate >= EndDate Then
  10.     MsgBox "Please enter a Starting date that is earlier than the Ending date"
  11.     ElseIf StartDate < EndDate Then
  12.         temp = EndDate - StartDate
  13. End If
  14.  
  15. If temp = 1 Then
  16.     MsgBox "There are no days between the two dates selected"
  17.     ElseIf temp > 1 Then
  18.         temp = temp - 1 
  19.         MyDate = Format(StartDate + 1, "mmmm d")
  20.         MyList = MyDate
  21.             Do Until temp = 1
  22.                 MyList = MyList & vbCrLf & Format(CDate(MyDate) + 1, "mmmm d")
  23.                 MyDate = CDate(MyDate) + 1
  24.                 temp = temp - 1
  25.             Loop
  26. End If
  27. ListDates = MyList
  28. End Function
It goes into a standard code module named something different than the name of the function...

You can call this from a query, form, etc... just pass it the two dates and see what happens.

Regards,
Scott
Oct 16 '07 #6

P: 3
Scott,

Thanks for responding again.
No the calendar is not an option in the solution I'm looking for.
Your code was very helpful.
This is along the lines of what I was looking for as the list of dates between the StartDate and EndDate is criteria I'm going to use in another funtion or query. I'm going to try to modify the solution so that the list of dates are then stored into an array.

I actually was on my way to a solution like the one you posted.

I am a .NET programmer but I've been working in Access for a couple of months.
I'm not a guru like you.

I will post my modified solution.
Oct 16 '07 #7

Scott Price
Expert 100+
P: 1,384
Glad it's working for you!

Thanks for the 'guru' compliment :-) However, I'm just a self-taught hack. Actually looking in the near future to dabble a bit in vb.net myself. Might see you over in the .NET forum answering my questions later on, eh?

Regards,
Scott
Oct 16 '07 #8

Post your reply

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