Pull report from start and end date

[Stockwell43]

Hello,

On my form I have a Start Date field and a End Date field to represent the
start and end of monthly premium coverage. The problem I am having is, how do
I pull a report using the start date and end date when they are two different
fields? I am use to the beginning and end date being from one field not two
so I am totally lost. If this can be accomplished, any help would be most
appreciated.

Thanks!!!

 

[Marshall Barton]

On my form I have a Start Date field and a End Date field to represent the
start and end of monthly premium coverage. The problem I am having is, how do
I pull a report using the start date and end date when they are two different
fields? I am use to the beginning and end date being from one field not two
so I am totally lost. If this can be accomplished, any help would be most
appreciated.

That's rather confusing.

What do you want to do with the start and end dates?

Note that **fields** are in tables and queries. The things
on forms are **controls**, so when you say "On my form I
have a Start Date field and a End Date field", it is either
irrelevant (because what's displayed on the form won't have
anything to do with a report) or you meant to convey some
information that I can not determine from your words.

 

[Stockwell43]

Ok, I have two controls on my form Start Date and End Date. I need to place
these two fields from the table to a query so that on a report, I can pull it
by inputting the Start Date and End Date. When I pull a query the two
controls from the form are on the query as two separate fields (not problem).
To show them as two fields on the report is fine (no problem) but I don't
know how to use the two fields as criteria for beginning and ending date for
pulling the report is the problem.

Is that making sense or am I totally explaining this wrong?

 

[Klatuu]

There are a number of ways to do this. Which will work the best for you will
depend on how your report's record source is structured and some other
factors. The easiest way would be to use the Where argument of the
OpenReport method. What you don't say is what date needs to be between the
start and end dates, so I will make one up. We will call it TermDate.
TermDate will need to be in the record source of the report.

strCriteria = "[TermDate] BETWEEN #" & Me.txtStartDate & "# AND #"
Me.txtEndDate"
Docmd.OpenReport "SomeReport", , , strCriteria

 

[Marshall Barton]

I think that makes sense, but let me rephrase it to make
sure I've got it.

You have two controls on the form that you want to
use to filter the report's data. The start and end date
controls are to be used to select report records if the
table start and end date interval overlaps the form
start and end date interval.

A common way to open a report is to use a command button on
the form. If my understanding of your question is correct,
the button's Click event procedure will use this kind of
logic:

Dim strDoc As String
Dim strWhere As String
strDoc = "name of the report"
strWhere = "[start date field] <= " & _
Format(Me.[end date control], "\#yyyy\/m\/d\#") & _
" And [end date field]" >= " & _
Format(Me.[start date control], "\#yyyy\/m\/d\#")
DoCmd.OpenReport strDoc, acViewPreview, , strWhere

 

[Stockwell43]

Yes, that is correct. It seemed simple when I created the database but now I
am unalbe to retrieve information using those two dates. They wanted them
separate like that because they needed to show the start date of hazard
insurance and the end date to show the time period of coverage for the month
so we can calculate the preimium for the insurance. If need be, they can use
the spreadsheet I created back when. That has all the calculations on it. I
just thought it would be easier to store the info in a database; It makes
sorting cleaner that a spreadsheet.

I think that makes sense, but let me rephrase it to make
sure I've got it.

You have two controls on the form that you want to
use to filter the report's data. The start and end date
controls are to be used to select report records if the
table start and end date interval overlaps the form
start and end date interval.

A common way to open a report is to use a command button on
the form. If my understanding of your question is correct,
the button's Click event procedure will use this kind of
logic:

Dim strDoc As String
Dim strWhere As String
strDoc = "name of the report"
strWhere = "[start date field] <= " & _
Format(Me.[end date control], "\#yyyy\/m\/d\#") & _
" And [end date field]" >= " & _
Format(Me.[start date control], "\#yyyy\/m\/d\#")
DoCmd.OpenReport strDoc, acViewPreview, , strWhere
--
Marsh
MVP [MS Access]

Ok, I have two controls on my form Start Date and End Date. I need to place
these two fields from the table to a query so that on a report, I can pull it
by inputting the Start Date and End Date. When I pull a query the two
controls from the form are on the query as two separate fields (not problem).
To show them as two fields on the report is fine (no problem) but I don't
know how to use the two fields as criteria for beginning and ending date for
pulling the report is the problem.

Is that making sense or am I totally explaining this wrong?

 

[Marshall Barton]

Did my suggested code do what you want?
--
Marsh
MVP [MS Access]

Yes, that is correct. It seemed simple when I created the database but now I
am unalbe to retrieve information using those two dates. They wanted them
separate like that because they needed to show the start date of hazard
insurance and the end date to show the time period of coverage for the month
so we can calculate the preimium for the insurance. If need be, they can use
the spreadsheet I created back when. That has all the calculations on it. I
just thought it would be easier to store the info in a database; It makes
sorting cleaner that a spreadsheet.

I think that makes sense, but let me rephrase it to make
sure I've got it.

You have two controls on the form that you want to
use to filter the report's data. The start and end date
controls are to be used to select report records if the
table start and end date interval overlaps the form
start and end date interval.

A common way to open a report is to use a command button on
the form. If my understanding of your question is correct,
the button's Click event procedure will use this kind of
logic:

Dim strDoc As String
Dim strWhere As String
strDoc = "name of the report"
strWhere = "[start date field] <= " & _
Format(Me.[end date control], "\#yyyy\/m\/d\#") & _
" And [end date field]" >= " & _
Format(Me.[start date control], "\#yyyy\/m\/d\#")
DoCmd.OpenReport strDoc, acViewPreview, , strWhere

Ok, I have two controls on my form Start Date and End Date. I need to place
these two fields from the table to a query so that on a report, I can pull it
by inputting the Start Date and End Date. When I pull a query the two
controls from the form are on the query as two separate fields (not problem).
To show them as two fields on the report is fine (no problem) but I don't
know how to use the two fields as criteria for beginning and ending date for
pulling the report is the problem.

Is that making sense or am I totally explaining this wrong?

:

Stockwell43 wrote:
On my form I have a Start Date field and a End Date field to represent the
start and end of monthly premium coverage. The problem I am having is, how do
I pull a report using the start date and end date when they are two different
fields? I am use to the beginning and end date being from one field not two
so I am totally lost. If this can be accomplished, any help would be most
appreciated.


That's rather confusing.

What do you want to do with the start and end dates?

Note that **fields** are in tables and queries. The things
on forms are **controls**, so when you say "On my form I
have a Start Date field and a End Date field", it is either
irrelevant (because what's displayed on the form won't have
anything to do with a report) or you meant to convey some
information that I can not determine from your words.

 

[Stockwell43]

Yes it did and thank you very much for staying with me on this!

Did my suggested code do what you want?
--
Marsh
MVP [MS Access]

Yes, that is correct. It seemed simple when I created the database but now I
am unalbe to retrieve information using those two dates. They wanted them
separate like that because they needed to show the start date of hazard
insurance and the end date to show the time period of coverage for the month
so we can calculate the preimium for the insurance. If need be, they can use
the spreadsheet I created back when. That has all the calculations on it. I
just thought it would be easier to store the info in a database; It makes
sorting cleaner that a spreadsheet.

I think that makes sense, but let me rephrase it to make
sure I've got it.

You have two controls on the form that you want to
use to filter the report's data. The start and end date
controls are to be used to select report records if the
table start and end date interval overlaps the form
start and end date interval.

A common way to open a report is to use a command button on
the form. If my understanding of your question is correct,
the button's Click event procedure will use this kind of
logic:

Dim strDoc As String
Dim strWhere As String
strDoc = "name of the report"
strWhere = "[start date field] <= " & _
Format(Me.[end date control], "\#yyyy\/m\/d\#") & _
" And [end date field]" >= " & _
Format(Me.[start date control], "\#yyyy\/m\/d\#")
DoCmd.OpenReport strDoc, acViewPreview, , strWhere

Stockwell43 wrote:
Ok, I have two controls on my form Start Date and End Date. I need to place
these two fields from the table to a query so that on a report, I can pull it
by inputting the Start Date and End Date. When I pull a query the two
controls from the form are on the query as two separate fields (not problem).
To show them as two fields on the report is fine (no problem) but I don't
know how to use the two fields as criteria for beginning and ending date for
pulling the report is the problem.

Is that making sense or am I totally explaining this wrong?

:

Stockwell43 wrote:
On my form I have a Start Date field and a End Date field to represent the
start and end of monthly premium coverage. The problem I am having is, how do
I pull a report using the start date and end date when they are two different
fields? I am use to the beginning and end date being from one field not two
so I am totally lost. If this can be accomplished, any help would be most
appreciated.


That's rather confusing.

What do you want to do with the start and end dates?

Note that **fields** are in tables and queries. The things
on forms are **controls**, so when you say "On my form I
have a Start Date field and a End Date field", it is either
irrelevant (because what's displayed on the form won't have
anything to do with a report) or you meant to convey some
information that I can not determine from your words.