Between dates


C

Chi

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
 
Ad

Advertisements

K

KARL DEWEY

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];
 
C

Chi

Hi Dewey,

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

KARL DEWEY said:
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 said:
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
 
F

fredg

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?
 
C

Chi

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 said:
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
.
 
F

fredg

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 said:
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.
 
Ad

Advertisements

C

Chi

Hi Fredg,

Thank you very much! I works very well.

fredg said:
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 said:
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
.
 
Ad

Advertisements

C

Chi

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

KARL DEWEY said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top