Between dates

Hi

Would you please show me how to make a query that would list all the dates
occurring between two days, including Beginning Date and Eding Date?

Ex: 12/01/2009 to 12/04/2009
It will give me the list like this: 12/01/2009,12/02/2009, 12/03/2009,
12/04/2009

Thanks,

Chi

Create a table named CountNumber with field CountNUM containg numbers from 0
(zero) through your maximum spread.

SELECT DateAdd("d", [CountNUM], [Enter start date]) AS Date_List
FROM CountNumber
WHERE CountNUM Between [Enter start date] AND [Enter end date];

--
Build a little, test a little.


"Chi" wrote:

Hi

Would you please show me how to make a query that would list all the dates
occurring between two days, including Beginning Date and Eding Date?

Ex: 12/01/2009 to 12/04/2009
It will give me the list like this: 12/01/2009,12/02/2009, 12/03/2009,
12/04/2009

Thanks,

Chi

Hi Dewey,

Thank you very much! I will try it and let you know!!
Chi

"KARL DEWEY" wrote:

Create a table named CountNumber with field CountNUM containg numbers from 0
(zero) through your maximum spread.

SELECT DateAdd("d", [CountNUM], [Enter start date]) AS Date_List
FROM CountNumber
WHERE CountNUM Between [Enter start date] AND [Enter end date];

--
Build a little, test a little.


"Chi" wrote:

Hi

Would you please show me how to make a query that would list all the dates
occurring between two days, including Beginning Date and Eding Date?

Ex: 12/01/2009 to 12/04/2009
It will give me the list like this: 12/01/2009,12/02/2009, 12/03/2009,
12/04/2009

Thanks,

Chi

On Fri, 18 Dec 2009 12:33:01 -0800, Chi wrote:

Hi

Would you please show me how to make a query that would list all the dates
occurring between two days, including Beginning Date and Eding Date?

Ex: 12/01/2009 to 12/04/2009
It will give me the list like this: 12/01/2009,12/02/2009, 12/03/2009,
12/04/2009

Thanks,

Chi

For what purpose?
Creating a table of dates, using VBA, is quite simple.
Create a new table. Name it "tblDates"
Add one field, [TheDate], DateTime datatype.
Then copy and paste the following code into a module:

Public Sub FillDates()
' Will fill a Date Field with consecutive date values.
Dim dteDateFrom As Date
Dim dteDateTo As Date
Dim db As DAO.Database
Dim rs As Recordset
Dim intX As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tblDates")

dteDateFrom = InputBox("Enter Starting date")
dteDateTo = InputBox("Enter Ending date")
With rs
Do While intX = (dteDateTo - dteDateFrom)
.AddNew
!TheDate = DateAdd("d", intX, dteDateFrom)
.Update
intX = intX + 1
Loop
End With
Set db = Nothing
Set rs = Nothing
End Sub

You can then run this module, entering the 2 dates when prompted, and
it will fill the table with consecutive dates.
Add error handling as needed.
Now what?

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Good Morning Fredg,

Thank you for the response!
I created the "tblDates" that has one field, [TheDate], DateTime datatype.

would you show me more detail on how creating a module and how to run it?
Sorry I don't know VBA.

Thanks
Chi



"fredg" wrote:

On Fri, 18 Dec 2009 12:33:01 -0800, Chi wrote:

Hi

Would you please show me how to make a query that would list all the dates
occurring between two days, including Beginning Date and Eding Date?

Ex: 12/01/2009 to 12/04/2009
It will give me the list like this: 12/01/2009,12/02/2009, 12/03/2009,
12/04/2009

Thanks,

Chi

For what purpose?
Creating a table of dates, using VBA, is quite simple.
Create a new table. Name it "tblDates"
Add one field, [TheDate], DateTime datatype.
Then copy and paste the following code into a module:

Public Sub FillDates()
' Will fill a Date Field with consecutive date values.
Dim dteDateFrom As Date
Dim dteDateTo As Date
Dim db As DAO.Database
Dim rs As Recordset
Dim intX As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tblDates")

dteDateFrom = InputBox("Enter Starting date")
dteDateTo = InputBox("Enter Ending date")
With rs
Do While intX = (dteDateTo - dteDateFrom)
.AddNew
!TheDate = DateAdd("d", intX, dteDateFrom)
.Update
intX = intX + 1
Loop
End With
Set db = Nothing
Set rs = Nothing
End Sub

You can then run this module, entering the 2 dates when prompted, and
it will fill the table with consecutive dates.
Add error handling as needed.
Now what?

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.

On Mon, 21 Dec 2009 08:21:01 -0800, Chi wrote:

Good Morning Fredg,

Thank you for the response!
I created the "tblDates" that has one field, [TheDate], DateTime datatype.

would you show me more detail on how creating a module and how to run it?
Sorry I don't know VBA.

Thanks
Chi

"fredg" wrote:

On Fri, 18 Dec 2009 12:33:01 -0800, Chi wrote:

Hi

Would you please show me how to make a query that would list all the dates
occurring between two days, including Beginning Date and Eding Date?

Ex: 12/01/2009 to 12/04/2009
It will give me the list like this: 12/01/2009,12/02/2009, 12/03/2009,
12/04/2009

Thanks,

Chi

For what purpose?
Creating a table of dates, using VBA, is quite simple.
Create a new table. Name it "tblDates"
Add one field, [TheDate], DateTime datatype.
Then copy and paste the following code into a module:

Public Sub FillDates()
' Will fill a Date Field with consecutive date values.
Dim dteDateFrom As Date
Dim dteDateTo As Date
Dim db As DAO.Database
Dim rs As Recordset
Dim intX As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tblDates")

dteDateFrom = InputBox("Enter Starting date")
dteDateTo = InputBox("Enter Ending date")
With rs
Do While intX = (dteDateTo - dteDateFrom)
.AddNew
!TheDate = DateAdd("d", intX, dteDateFrom)
.Update
intX = intX + 1
Loop
End With
Set db = Nothing
Set rs = Nothing
End Sub

You can then run this module, entering the 2 dates when prompted, and
it will fill the table with consecutive dates.
Add error handling as needed.
Now what?

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.


Highlight the sub procedure in my first reply (everything from Public
Sub FillDates() through End Sub). Copy the highlighted code by
pressing Ctrl + C or Edit + Copy.

Then, in your database, click on Modules + New.

When the code window opens the first two lines of the code window
should read

Option Explicit
Option Compare Database

If those 2 lines are not there, write them at the top of the code
window.

Then, place your cursor within the code window (under those top 2
lines) and Paste the code, using Ctrl+V or Edit + Paste, etc.
Exit the code window. When prompted, name the module "mdlFillDates".

When ready to run this code, place the cursor anywhere within the
procedure between Public SubFillDates and End Sub.
Press F5.
When prompted, enter the Start and End dates.
The table will fill with dates.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Hi Fredg,

Thank you very much! I works very well.

"fredg" wrote:

On Mon, 21 Dec 2009 08:21:01 -0800, Chi wrote:

Good Morning Fredg,

Thank you for the response!
I created the "tblDates" that has one field, [TheDate], DateTime datatype.

would you show me more detail on how creating a module and how to run it?
Sorry I don't know VBA.

Thanks
Chi

"fredg" wrote:

On Fri, 18 Dec 2009 12:33:01 -0800, Chi wrote:

Hi

Would you please show me how to make a query that would list all the dates
occurring between two days, including Beginning Date and Eding Date?

Ex: 12/01/2009 to 12/04/2009
It will give me the list like this: 12/01/2009,12/02/2009, 12/03/2009,
12/04/2009

Thanks,

Chi

For what purpose?
Creating a table of dates, using VBA, is quite simple.
Create a new table. Name it "tblDates"
Add one field, [TheDate], DateTime datatype.
Then copy and paste the following code into a module:

Public Sub FillDates()
' Will fill a Date Field with consecutive date values.
Dim dteDateFrom As Date
Dim dteDateTo As Date
Dim db As DAO.Database
Dim rs As Recordset
Dim intX As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tblDates")

dteDateFrom = InputBox("Enter Starting date")
dteDateTo = InputBox("Enter Ending date")
With rs
Do While intX = (dteDateTo - dteDateFrom)
.AddNew
!TheDate = DateAdd("d", intX, dteDateFrom)
.Update
intX = intX + 1
Loop
End With
Set db = Nothing
Set rs = Nothing
End Sub

You can then run this module, entering the 2 dates when prompted, and
it will fill the table with consecutive dates.
Add error handling as needed.
Now what?

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.


Highlight the sub procedure in my first reply (everything from Public
Sub FillDates() through End Sub). Copy the highlighted code by
pressing Ctrl + C or Edit + Copy.

Then, in your database, click on Modules + New.

When the code window opens the first two lines of the code window
should read

Option Explicit
Option Compare Database

If those 2 lines are not there, write them at the top of the code
window.

Then, place your cursor within the code window (under those top 2
lines) and Paste the code, using Ctrl+V or Edit + Paste, etc.
Exit the code window. When prompted, name the module "mdlFillDates".

When ready to run this code, place the cursor anywhere within the
procedure between Public SubFillDates and End Sub.
Press F5.
When prompted, enter the Start and End dates.
The table will fill with dates.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.

Hi Karl,
Thank you very much for your help!
Chi

"KARL DEWEY" wrote:

Create a table named CountNumber with field CountNUM containg numbers from 0
(zero) through your maximum spread.

SELECT DateAdd("d", [CountNUM], [Enter start date]) AS Date_List
FROM CountNumber
WHERE CountNUM Between [Enter start date] AND [Enter end date];

--
Build a little, test a little.


"Chi" wrote:

Hi

Would you please show me how to make a query that would list all the dates
occurring between two days, including Beginning Date and Eding Date?

Ex: 12/01/2009 to 12/04/2009
It will give me the list like this: 12/01/2009,12/02/2009, 12/03/2009,
12/04/2009

Thanks,

Chi