Add values between input dates

  • Thread starter Thread starter igorin
  • Start date Start date
I

igorin

Hello,

I need to have a query that adds the values between (and including) two
input dates (which will be provided by the user).

This query will go into a Form where the user will be able to view this
information for the date range he desires. The user should be able to input
the desired date range into text boxes. Then the information for the rest of
the field text boxes should populate with the requested totals.

If my presentation of the issue is not clear, please, let me know and I will
try to clear any doubts.

Thank you very mcuh for the help.
 
If I understand correctly, you want a user to enter a StartDate and an
EndDate into two textboxes on a form. You then want to insert records in a
table that correspond to the StartDate and EndDate, and every date in between.

1. To start with, create a new table (tbl_Numbers) with a single field
(lng_Number).
2. Insert 9 records into this table (0, 1, ..., 9)
3. Create a query (qry_Numbers) that looks like:

SELECT Hundreds.lng_Number * 100 + Tens.lng_Number * 10 + Ones.lng_Number as
lng_Number
FROM tbl_Numbers as Hundreds,
tbl_Numbers as Tens,
tbl_Numbers as Ones

This will create a list of numbers from 1 to 999 (almost 3 years).
4. Next, create an Append query (qry_InsertDates) that looks something like:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
INSERT INTO yourTable (DateField)
SELECT DateAdd("d",[lngNumber],[StartDate]) AS Expr1
FROM qry_Numbers
WHERE (((DateAdd("d",[lngNumber],[StartDate]))<=[EndDate]));

5. Finally, create a command button (cmd_InsertDates) on your form. In the
Click event of this command button, enter code similar to the following:

Private Sub cmd_InsertDates_Click

Dim qdf as dao.querydef

On Error goto InsertDatesError

'make sure the start and end date textboxes have valid dates
if len(me.txt_StartDate & "") = 0 then
msgbox "Enter a start date"
me.txt_StartDate.setfocus
exit sub
elseif len(me.txt_EndDate & "") = 0 then
msgbox "Enter a end date"
me.txt_EndDate.setfocus
exit sub
endif

Set qdf = currentdb.querydefs("qry_InsertDates")
qdf.parameters(0) = me.txt_StartDate
qdf.parameters(1) = me.txt_EndDate
qdf.execute dbfailonerror
set qdf = nothing
exit sub

InsertDatesError:
msgbox "error encountered, check the immediate window
debug.print err.number, err.description

End sub

I seriously doubt that this is exactly what you want. You probably want to
associate these dates with some other field as well, but this should give you
the general idea.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Hello, Dave, let me try to explain this differently:

I already have a table with fields and its corresponding values.

I want to create a form that should allow the user to input a start date and
an end date. Based on these dates the rest of the text boxes for the fields
in the table should show (i.e., auto-populate) the totals (sum) for these
fields for the requested time frame (including both dates).

To do this, if I'm not mistaken, I will need to create a query which I will
then use to create the form.

Has this made what I want to do a little bit more clear?

Thanks for the help!


Dale Fye said:
If I understand correctly, you want a user to enter a StartDate and an
EndDate into two textboxes on a form. You then want to insert records in a
table that correspond to the StartDate and EndDate, and every date in between.

1. To start with, create a new table (tbl_Numbers) with a single field
(lng_Number).
2. Insert 9 records into this table (0, 1, ..., 9)
3. Create a query (qry_Numbers) that looks like:

SELECT Hundreds.lng_Number * 100 + Tens.lng_Number * 10 + Ones.lng_Number as
lng_Number
FROM tbl_Numbers as Hundreds,
tbl_Numbers as Tens,
tbl_Numbers as Ones

This will create a list of numbers from 1 to 999 (almost 3 years).
4. Next, create an Append query (qry_InsertDates) that looks something like:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
INSERT INTO yourTable (DateField)
SELECT DateAdd("d",[lngNumber],[StartDate]) AS Expr1
FROM qry_Numbers
WHERE (((DateAdd("d",[lngNumber],[StartDate]))<=[EndDate]));

5. Finally, create a command button (cmd_InsertDates) on your form. In the
Click event of this command button, enter code similar to the following:

Private Sub cmd_InsertDates_Click

Dim qdf as dao.querydef

On Error goto InsertDatesError

'make sure the start and end date textboxes have valid dates
if len(me.txt_StartDate & "") = 0 then
msgbox "Enter a start date"
me.txt_StartDate.setfocus
exit sub
elseif len(me.txt_EndDate & "") = 0 then
msgbox "Enter a end date"
me.txt_EndDate.setfocus
exit sub
endif

Set qdf = currentdb.querydefs("qry_InsertDates")
qdf.parameters(0) = me.txt_StartDate
qdf.parameters(1) = me.txt_EndDate
qdf.execute dbfailonerror
set qdf = nothing
exit sub

InsertDatesError:
msgbox "error encountered, check the immediate window
debug.print err.number, err.description

End sub

I seriously doubt that this is exactly what you want. You probably want to
associate these dates with some other field as well, but this should give you
the general idea.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



igorin said:
Hello,

I need to have a query that adds the values between (and including) two
input dates (which will be provided by the user).

This query will go into a Form where the user will be able to view this
information for the date range he desires. The user should be able to input
the desired date range into text boxes. Then the information for the rest of
the field text boxes should populate with the requested totals.

If my presentation of the issue is not clear, please, let me know and I will
try to clear any doubts.

Thank you very mcuh for the help.
Click to expand...
Click to expand...
 
Based on these dates the rest of the text boxes for the fields in the
table should show (i.e., auto-populate) the totals (sum) for these fields for
the requested time frame (including both dates).

Where is the data for the fields to come from? Within some table? What is
being totaled?

Post some sample data from table and what you would want in the text boxes.

--
KARL DEWEY
Build a little - Test a little


igorin said:
Hello, Dave, let me try to explain this differently:

I already have a table with fields and its corresponding values.

I want to create a form that should allow the user to input a start date and
an end date. Based on these dates the rest of the text boxes for the fields
in the table should show (i.e., auto-populate) the totals (sum) for these
fields for the requested time frame (including both dates).

To do this, if I'm not mistaken, I will need to create a query which I will
then use to create the form.

Has this made what I want to do a little bit more clear?

Thanks for the help!


Dale Fye said:
If I understand correctly, you want a user to enter a StartDate and an
EndDate into two textboxes on a form. You then want to insert records in a
table that correspond to the StartDate and EndDate, and every date in between.

1. To start with, create a new table (tbl_Numbers) with a single field
(lng_Number).
2. Insert 9 records into this table (0, 1, ..., 9)
3. Create a query (qry_Numbers) that looks like:

SELECT Hundreds.lng_Number * 100 + Tens.lng_Number * 10 + Ones.lng_Number as
lng_Number
FROM tbl_Numbers as Hundreds,
tbl_Numbers as Tens,
tbl_Numbers as Ones

This will create a list of numbers from 1 to 999 (almost 3 years).
4. Next, create an Append query (qry_InsertDates) that looks something like:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
INSERT INTO yourTable (DateField)
SELECT DateAdd("d",[lngNumber],[StartDate]) AS Expr1
FROM qry_Numbers
WHERE (((DateAdd("d",[lngNumber],[StartDate]))<=[EndDate]));

5. Finally, create a command button (cmd_InsertDates) on your form. In the
Click event of this command button, enter code similar to the following:

Private Sub cmd_InsertDates_Click

Dim qdf as dao.querydef

On Error goto InsertDatesError

'make sure the start and end date textboxes have valid dates
if len(me.txt_StartDate & "") = 0 then
msgbox "Enter a start date"
me.txt_StartDate.setfocus
exit sub
elseif len(me.txt_EndDate & "") = 0 then
msgbox "Enter a end date"
me.txt_EndDate.setfocus
exit sub
endif

Set qdf = currentdb.querydefs("qry_InsertDates")
qdf.parameters(0) = me.txt_StartDate
qdf.parameters(1) = me.txt_EndDate
qdf.execute dbfailonerror
set qdf = nothing
exit sub

InsertDatesError:
msgbox "error encountered, check the immediate window
debug.print err.number, err.description

End sub

I seriously doubt that this is exactly what you want. You probably want to
associate these dates with some other field as well, but this should give you
the general idea.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



igorin said:
Hello,

I need to have a query that adds the values between (and including) two
input dates (which will be provided by the user).

This query will go into a Form where the user will be able to view this
information for the date range he desires. The user should be able to input
the desired date range into text boxes. Then the information for the rest of
the field text boxes should populate with the requested totals.

If my presentation of the issue is not clear, please, let me know and I will
try to clear any doubts.

Thank you very mcuh for the help.
Click to expand...
Click to expand...
Click to expand...
 
Thank you, Karl,

I will post the question anew with an exaple to illustrate it.

:-)



karl dewey said:
table should show (i.e., auto-populate) the totals (sum) for these fields for
the requested time frame (including both dates).

Where is the data for the fields to come from? Within some table? What is
being totaled?

Post some sample data from table and what you would want in the text boxes.

--
KARL DEWEY
Build a little - Test a little


igorin said:
Hello, Dave, let me try to explain this differently:

I already have a table with fields and its corresponding values.

I want to create a form that should allow the user to input a start date and
an end date. Based on these dates the rest of the text boxes for the fields
in the table should show (i.e., auto-populate) the totals (sum) for these
fields for the requested time frame (including both dates).

To do this, if I'm not mistaken, I will need to create a query which I will
then use to create the form.

Has this made what I want to do a little bit more clear?

Thanks for the help!


Dale Fye said:
If I understand correctly, you want a user to enter a StartDate and an
EndDate into two textboxes on a form. You then want to insert records in a
table that correspond to the StartDate and EndDate, and every date in between.

1. To start with, create a new table (tbl_Numbers) with a single field
(lng_Number).
2. Insert 9 records into this table (0, 1, ..., 9)
3. Create a query (qry_Numbers) that looks like:

SELECT Hundreds.lng_Number * 100 + Tens.lng_Number * 10 + Ones.lng_Number as
lng_Number
FROM tbl_Numbers as Hundreds,
tbl_Numbers as Tens,
tbl_Numbers as Ones

This will create a list of numbers from 1 to 999 (almost 3 years).
4. Next, create an Append query (qry_InsertDates) that looks something like:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
INSERT INTO yourTable (DateField)
SELECT DateAdd("d",[lngNumber],[StartDate]) AS Expr1
FROM qry_Numbers
WHERE (((DateAdd("d",[lngNumber],[StartDate]))<=[EndDate]));

5. Finally, create a command button (cmd_InsertDates) on your form. In the
Click event of this command button, enter code similar to the following:

Private Sub cmd_InsertDates_Click

Dim qdf as dao.querydef

On Error goto InsertDatesError

'make sure the start and end date textboxes have valid dates
if len(me.txt_StartDate & "") = 0 then
msgbox "Enter a start date"
me.txt_StartDate.setfocus
exit sub
elseif len(me.txt_EndDate & "") = 0 then
msgbox "Enter a end date"
me.txt_EndDate.setfocus
exit sub
endif

Set qdf = currentdb.querydefs("qry_InsertDates")
qdf.parameters(0) = me.txt_StartDate
qdf.parameters(1) = me.txt_EndDate
qdf.execute dbfailonerror
set qdf = nothing
exit sub

InsertDatesError:
msgbox "error encountered, check the immediate window
debug.print err.number, err.description

End sub

I seriously doubt that this is exactly what you want. You probably want to
associate these dates with some other field as well, but this should give you
the general idea.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hello,

I need to have a query that adds the values between (and including) two
input dates (which will be provided by the user).

This query will go into a Form where the user will be able to view this
information for the date range he desires. The user should be able to input
the desired date range into text boxes. Then the information for the rest of
the field text boxes should populate with the requested totals.

If my presentation of the issue is not clear, please, let me know and I will
try to clear any doubts.

Thank you very mcuh for the help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top