Select month end data in form

[Alex Martinez]

Hello,

I like to use a command button in my form to have a calendar control pop-up
and select the month end date and a report will pop-up in preview and then I
would press the print button. Now I know have to generate the calendar
control and preview/print the report. I have a table that is called month
end which has one field called "monthEnd". How can I get the data I want
based on a field call "completed" and only get the data for the entire
month. For example I press the command and a calendar will pop-up and I
select "3/31/2006" which gets all the "completed" data from 3/1/2006 -
3/31/2006. Is this possible? Any tips will be appreciated. Thank you in
advance.

 

[Allen Browne]

The example below assumes the closing date is found in a control named
EndDate on your form. It creates a WhereCondition string to use with
OpenReport.

The code gets the start of the month by subtracting the day of the month and
adding 1. (That works even if the original date was not the last day of the
month.)

The WhereCondition is set from the first of this month to less than the
first of next month. If you use Between ... And ..., it does not include any
record from the last day of the month that has a time component in the
field, e.g. 3/31/2006 9:00:00am would not make it into the report.

The date formatting string ensures that the code works regardless of the
user's regional settings.

If no date is selected in the control, the report returns all records.

Dim strWhere As String
Dim dtStart As Date
Dim dtEnded As Date
Const strcJetDate = "\#mm\/dd\/yyyy\#"

With Me.EndDate
If IsDate(.Value) Then
dtStart = .Value - Day(.Value) + 1
dtEnded = DateAdd("m", 1, dtStart)
strWhere = "([SomeDateField] >= " & Format(dtStart, strcJetDate)
& _
") AND [SomeDateField] < " & Format(dtEnded, strcJetDate) &
")"
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End With

 

[Alex Martinez]

Thanks again Allen, I will try this.
Regards,

The example below assumes the closing date is found in a control named
EndDate on your form. It creates a WhereCondition string to use with
OpenReport.

The code gets the start of the month by subtracting the day of the month
and adding 1. (That works even if the original date was not the last day
of the month.)

The WhereCondition is set from the first of this month to less than the
first of next month. If you use Between ... And ..., it does not include
any record from the last day of the month that has a time component in the
field, e.g. 3/31/2006 9:00:00am would not make it into the report.

The date formatting string ensures that the code works regardless of the
user's regional settings.

If no date is selected in the control, the report returns all records.

Dim strWhere As String
Dim dtStart As Date
Dim dtEnded As Date
Const strcJetDate = "\#mm\/dd\/yyyy\#"

With Me.EndDate
If IsDate(.Value) Then
dtStart = .Value - Day(.Value) + 1
dtEnded = DateAdd("m", 1, dtStart)
strWhere = "([SomeDateField] >= " & Format(dtStart,
strcJetDate) & _
") AND [SomeDateField] < " & Format(dtEnded, strcJetDate) &
")"
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I like to use a command button in my form to have a calendar control
pop-up and select the month end date and a report will pop-up in preview
and then I would press the print button. Now I know have to generate the
calendar control and preview/print the report. I have a table that is
called month end which has one field called "monthEnd". How can I get
the data I want based on a field call "completed" and only get the data
for the entire month. For example I press the command and a calendar
will pop-up and I select "3/31/2006" which gets all the "completed" data
from 3/1/2006 - 3/31/2006. Is this possible? Any tips will be
appreciated. Thank you in advance.