Command button to open a report with a year's data

[TonyWilliams via AccessMonster.com]

I have a report that shows statistical data for each month (txtmonth) I have
a command button on a form that when clicked opens the report for the month
selected on the form. I want to produce another form with a button that would
open the report to show the total of records, using the same report, for the
whole year. I think I ought to be able to do that by something like "open all
records where the year of txtmonth = the year input on my form"
can someone give me some guidance on how I would code the OnClick of this new
button?
Thanks
Tony

 

[TonyWilliams via AccessMonster.com]

I've tried this code but get a Type mismath error?
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim stDocName As String
Dim stDate As Date
stDocName = "rpthighvaluereport"
stDate = Format(txtmonth, "\#/yyyy\#")

DoCmd.OpenReport stDocName, acPreview, , stDate = Format(Me.txtyear, "\
#/yyyy\#")

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

Anyone help here?
Thanks
Tony

 

[John W. Vinson]

I've tried this code but get a Type mismath error?
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim stDocName As String
Dim stDate As Date
stDocName = "rpthighvaluereport"
stDate = Format(txtmonth, "\#/yyyy\#")

DoCmd.OpenReport stDocName, acPreview, , stDate = Format(Me.txtyear, "\
#/yyyy\#")

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

A Date/Time field is actually stored as a double float number, a count of days
and fractions of a day (times) since midnight, December 30, 1899. As such it
must be a precise date. #2010# is NOT such a date!

Assuming that the field stDate in your report's recordsource is in fact a
date/time field, try

DoCmd.OpenReport stDocName, acPreview, , "stDate >=
DateSerial(Year(Me!txtYear), 1, 1) AND stDate < DateSerial(Year(Me!txtYear) +
1, 1, 1)

to get all records where the date field is on or later than January 1 of the
year portion of the date in txtDate, and earlier than January 1 in the next
year.

 

[TonyWilliams via AccessMonster.com]

Thank you John. I tried your suggested code and still got aType mismatch
error on this line:
stDate = Format(txtmonth, "\#/yyyy\#")

Private Sub Command5_Click()
Dim stDocName As String
Dim stDate As Date
stDocName = "rpthighvaluereportYTD"
stDate = Format(txtmonth, "\#/yyyy\#")

DoCmd.OpenReport stDocName, acPreview, , "stDate >= DateSerial(Year(Me!
txtYear), 1, 1) AND stDate < DateSerial(Year(Me!txtYear) + 1, 1, 1)"

End Sub

I can confirm that the txtmonth is a datefield and the field txtyear on my
form is unbound and formatted as yyyy. Presumably I can format an unbound
control as yyyy?

Thanks again for your help
Tony

I've tried this code but get a Type mismath error?
Private Sub Command5_Click()

[quoted text clipped - 14 lines]

MsgBox Err.Description
Resume Exit_Command5_Click

A Date/Time field is actually stored as a double float number, a count of days
and fractions of a day (times) since midnight, December 30, 1899. As such it
must be a precise date. #2010# is NOT such a date!

Assuming that the field stDate in your report's recordsource is in fact a
date/time field, try

DoCmd.OpenReport stDocName, acPreview, , "stDate >=
DateSerial(Year(Me!txtYear), 1, 1) AND stDate < DateSerial(Year(Me!txtYear) +
1, 1, 1)

to get all records where the date field is on or later than January 1 of the
year portion of the date in txtDate, and earlier than January 1 in the next
year.

 

[TonyWilliams via AccessMonster.com]

John I've been trying different versions of your code and came up with this:
Private Sub Command5_Click()
Dim stDocName As String

stDocName = "rpthighvaluereportYTD"

DoCmd.OpenReport stDocName, acPreview, , "[txtmonthlabela] >= DateSerial
(Year([Forms]![Form1]![txtYear]), 1, 1) AND [txtmonthlabela] < DateSerial
(Year([Forms]![Form1]![txtYear]) + 1, 1, 1)"

End Sub

However although this opens the report it gives me a report for evry month in
the year whereas I wanted one report showing a TOTAL for the year. I suspect
there is some underlying problem in that do I have to amend the record source
queries of the report and its sub reports to give me totals?

Thanks Tony

I've tried this code but get a Type mismath error?
Private Sub Command5_Click()

[quoted text clipped - 14 lines]

MsgBox Err.Description
Resume Exit_Command5_Click

A Date/Time field is actually stored as a double float number, a count of days
and fractions of a day (times) since midnight, December 30, 1899. As such it
must be a precise date. #2010# is NOT such a date!

Assuming that the field stDate in your report's recordsource is in fact a
date/time field, try

DoCmd.OpenReport stDocName, acPreview, , "stDate >=
DateSerial(Year(Me!txtYear), 1, 1) AND stDate < DateSerial(Year(Me!txtYear) +
1, 1, 1)

to get all records where the date field is on or later than January 1 of the
year portion of the date in txtDate, and earlier than January 1 in the next
year.

 

[John W. Vinson]

John I've been trying different versions of your code and came up with this:
Private Sub Command5_Click()
Dim stDocName As String

stDocName = "rpthighvaluereportYTD"

DoCmd.OpenReport stDocName, acPreview, , "[txtmonthlabela] >= DateSerial
(Year([Forms]![Form1]![txtYear]), 1, 1) AND [txtmonthlabela] < DateSerial
(Year([Forms]![Form1]![txtYear]) + 1, 1, 1)"

End Sub

However although this opens the report it gives me a report for evry month in
the year whereas I wanted one report showing a TOTAL for the year. I suspect
there is some underlying problem in that do I have to amend the record source
queries of the report and its sub reports to give me totals?

Ummmm...

Yes.

Access is giving you what you're asking for - the report as written with the
criteria you supplied. The telepathic interface isn't going to be available
for a few more releases. Your question was about date criteria, and that's the
question I answered.

Either use the Report's Sorting and Grouping to group by
Year([txtmontlabela]), or rewrite the query upon which the report is based to
do the totalling that you desire.

 

[TonyWilliams via AccessMonster.com]

Thanks John and I apologise for my total lack of experience here. I feel like
I'm drowning in treacle at the moment, trying to understand what I'm trying
to do and how Access can help me.
I will have look at how I can use both your ideas.
You must occasioonally get frustrated when people like me seem to go at these
things in a non systematic way, all I can say is thank goodness there are
guys like you around to help us!
I really don't want to labour the point, but at 65 I really do find my
thought processes aren't as good as they used to be and everything just seems
to be just that little bit harder to get to grips with!
Thanks again, I may well post some results back If I can understand what I'm
going to do.

Tony

John I've been trying different versions of your code and came up with this:
Private Sub Command5_Click()

[quoted text clipped - 12 lines]

there is some underlying problem in that do I have to amend the record source
queries of the report and its sub reports to give me totals?

Ummmm...

Yes.

Access is giving you what you're asking for - the report as written with the
criteria you supplied. The telepathic interface isn't going to be available
for a few more releases. Your question was about date criteria, and that's the
question I answered.

Either use the Report's Sorting and Grouping to group by
Year([txtmontlabela]), or rewrite the query upon which the report is based to
do the totalling that you desire.

 

[John W. Vinson]

Thanks John and I apologise for my total lack of experience here. I feel like
I'm drowning in treacle at the moment, trying to understand what I'm trying
to do and how Access can help me.
I will have look at how I can use both your ideas.
You must occasioonally get frustrated when people like me seem to go at these
things in a non systematic way, all I can say is thank goodness there are
guys like you around to help us!
I really don't want to labour the point, but at 65 I really do find my
thought processes aren't as good as they used to be and everything just seems
to be just that little bit harder to get to grips with!
Thanks again, I may well post some results back If I can understand what I'm
going to do.

I sympathize (I'm 63 myself and very much feel it!) In fact I sometimes lack
the patience I used to have... my apologies!

Rereading your original post you did say you wanted totals. But yes, to change
the report from one which has one line for each item to one which gives
totals, you must indeed either change the Query upon which the report is based
to a Totals query, or change the Report (to do the totalling in its Sorting
and Grouping, and simply don't display anything in the Details section, only
in the group footer).

 

[TonyWilliams via AccessMonster.com]

A kindred spirit I sense! Isn't getting old annoying? My brain wants to work
like a 21 year old but I can't get it to.

Anyway thanks for the tip and apologies totally unnecessary I'm just glad
you're there!
Tony

Thanks John and I apologise for my total lack of experience here. I feel like
I'm drowning in treacle at the moment, trying to understand what I'm trying

[quoted text clipped - 8 lines]

Thanks again, I may well post some results back If I can understand what I'm
going to do.

I sympathize (I'm 63 myself and very much feel it!) In fact I sometimes lack
the patience I used to have... my apologies!

Rereading your original post you did say you wanted totals. But yes, to change
the report from one which has one line for each item to one which gives
totals, you must indeed either change the Query upon which the report is based
to a Totals query, or change the Report (to do the totalling in its Sorting
and Grouping, and simply don't display anything in the Details section, only
in the group footer).

 

[John W. Vinson]

Isn't getting old annoying?

It sure beats the alternative!