print todays report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have a form with a command button to print a report called "amy2" the
user may fill out 1 to many forms for any given day. an "amy2" report will
print a report grouped by date.. I have this code in the on click event. but
it prints all report for every day.. the date header has a field called
dat by day and prints the date in long format eg Monday January 3, 2005. on
the form we use the short format eg. 1/3/2005. I tried puting in the same
field [date] that the form populates in the table. but still print all
reports. can someone tell me what i am missing.
Private Sub Printamy2_Click()

On Error GoTo Err_Printamy2_Click

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date]=" & Me!Date

DoCmd.OpenReport strDocName, acNormal

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

End Sub
 
First thing is that you must include # around the literal date in the
WhereCondition string. It is also worth formatting it correctly, so the code
works in any country. Try:
strWhere = "[date] = " & Format(Me!Date, "\#mm\/dd\/yyyy\#")

If the date field contains a time component (e.g. if you used =Now() as the
default value where =Date() would have been better), try:
strWhere = "[date] >= " & Format(Me!Date, "\#mm\/dd\/yyyy\#") & _
" [date] < " & Format(Me!Date + 1, "\#mm\/dd\/yyyy\#")

Finally, Date is a reserved word in VBA (for the system date), and so not a
good choice of field name. There are contexts where Access will
misunderstand what you mean by Date and give you wrong results. Consider
renaming the field.
 
Allen,
I renamed my field [date] to [productiondate] rewrote the code as you
wrote. I did it both ways, my productiondate field as all my date fields
are just formated as a date field and in the form i use a mask so the user
only has to type in the mm dd yy. when i click on the print it prints all
54 pages when there are only 3 or 4 pages that have that specific date that
is on the form at that time.

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date2]=" & Format(Me!ProductionDate, "\#mm\/dd\/yyyy\#")

DoCmd.OpenReport strDocName, acNormal

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

your help is apreciated
thanks
bart
Allen Browne said:
First thing is that you must include # around the literal date in the
WhereCondition string. It is also worth formatting it correctly, so the code
works in any country. Try:
strWhere = "[date] = " & Format(Me!Date, "\#mm\/dd\/yyyy\#")

If the date field contains a time component (e.g. if you used =Now() as the
default value where =Date() would have been better), try:
strWhere = "[date] >= " & Format(Me!Date, "\#mm\/dd\/yyyy\#") & _
" [date] < " & Format(Me!Date + 1, "\#mm\/dd\/yyyy\#")

Finally, Date is a reserved word in VBA (for the system date), and so not a
good choice of field name. There are contexts where Access will
misunderstand what you mean by Date and give you wrong results. Consider
renaming the field.

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

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

Bart said:
Hi, I have a form with a command button to print a report called "amy2"
the
user may fill out 1 to many forms for any given day. an "amy2" report
will
print a report grouped by date.. I have this code in the on click event.
but
it prints all report for every day.. the date header has a field
called
dat by day and prints the date in long format eg Monday January 3, 2005.
on
the form we use the short format eg. 1/3/2005. I tried puting in the same
field [date] that the form populates in the table. but still print all
reports. can someone tell me what i am missing.
Private Sub Printamy2_Click()

On Error GoTo Err_Printamy2_Click

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date]=" & Me!Date

DoCmd.OpenReport strDocName, acNormal

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

End Sub
Click to expand...
Click to expand...
 
What happened to the WhereCondition bit in the OpenReport line?

Try:
DoCmd.OpenReport strDocName, acViewNormal, , strWhere

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

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

Bart said:
Allen,
I renamed my field [date] to [productiondate] rewrote the code as you
wrote. I did it both ways, my productiondate field as all my date fields
are just formated as a date field and in the form i use a mask so the
user
only has to type in the mm dd yy. when i click on the print it prints
all
54 pages when there are only 3 or 4 pages that have that specific date
that
is on the form at that time.

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date2]=" & Format(Me!ProductionDate, "\#mm\/dd\/yyyy\#")

DoCmd.OpenReport strDocName, acNormal

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

your help is apreciated
thanks
bart
Allen Browne said:
First thing is that you must include # around the literal date in the
WhereCondition string. It is also worth formatting it correctly, so the
code
works in any country. Try:
strWhere = "[date] = " & Format(Me!Date, "\#mm\/dd\/yyyy\#")

If the date field contains a time component (e.g. if you used =Now() as
the
default value where =Date() would have been better), try:
strWhere = "[date] >= " & Format(Me!Date, "\#mm\/dd\/yyyy\#") & _
" [date] < " & Format(Me!Date + 1, "\#mm\/dd\/yyyy\#")

Finally, Date is a reserved word in VBA (for the system date), and so not
a
good choice of field name. There are contexts where Access will
misunderstand what you mean by Date and give you wrong results. Consider
renaming the field.

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

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

Bart said:
Hi, I have a form with a command button to print a report called "amy2"
the
user may fill out 1 to many forms for any given day. an "amy2" report
will
print a report grouped by date.. I have this code in the on click
event.
but
it prints all report for every day.. the date header has a field
called
dat by day and prints the date in long format eg Monday January 3,
2005.
on
the form we use the short format eg. 1/3/2005. I tried puting in the
same
field [date] that the form populates in the table. but still print all
reports. can someone tell me what i am missing.
Private Sub Printamy2_Click()

On Error GoTo Err_Printamy2_Click

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date]=" & Me!Date

DoCmd.OpenReport strDocName, acNormal

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
ok added to the open report command.
now i get a message that says printing amy2 report to my printer and
imediatly another message box that ask for the date2. when i type in the
date2 like this as a string 010305 i get the correct result. I believe i
am close here must be something about how the date fields look at eachother.
here is the code as it is now. Thanks again
On Error GoTo Err_Printamy2_Click

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date2]=" & Format(Me!ProductionDate, "\#mm\/dd\/yyyy\#")
DoCmd.OpenReport strDocName, acViewNormal, , strWhere

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

End Sub

Allen Browne said:
What happened to the WhereCondition bit in the OpenReport line?

Try:
DoCmd.OpenReport strDocName, acViewNormal, , strWhere

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

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

Bart said:
Allen,
I renamed my field [date] to [productiondate] rewrote the code as you
wrote. I did it both ways, my productiondate field as all my date fields
are just formated as a date field and in the form i use a mask so the
user
only has to type in the mm dd yy. when i click on the print it prints
all
54 pages when there are only 3 or 4 pages that have that specific date
that
is on the form at that time.

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date2]=" & Format(Me!ProductionDate, "\#mm\/dd\/yyyy\#")

DoCmd.OpenReport strDocName, acNormal

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

your help is apreciated
thanks
bart
Allen Browne said:
First thing is that you must include # around the literal date in the
WhereCondition string. It is also worth formatting it correctly, so the
code
works in any country. Try:
strWhere = "[date] = " & Format(Me!Date, "\#mm\/dd\/yyyy\#")

If the date field contains a time component (e.g. if you used =Now() as
the
default value where =Date() would have been better), try:
strWhere = "[date] >= " & Format(Me!Date, "\#mm\/dd\/yyyy\#") & _
" [date] < " & Format(Me!Date + 1, "\#mm\/dd\/yyyy\#")

Finally, Date is a reserved word in VBA (for the system date), and so not
a
good choice of field name. There are contexts where Access will
misunderstand what you mean by Date and give you wrong results. Consider
renaming the field.

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

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

Hi, I have a form with a command button to print a report called "amy2"
the
user may fill out 1 to many forms for any given day. an "amy2" report
will
print a report grouped by date.. I have this code in the on click
event.
but
it prints all report for every day.. the date header has a field
called
dat by day and prints the date in long format eg Monday January 3,
2005.
on
the form we use the short format eg. 1/3/2005. I tried puting in the
same
field [date] that the form populates in the table. but still print all
reports. can someone tell me what i am missing.
Private Sub Printamy2_Click()

On Error GoTo Err_Printamy2_Click

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date]=" & Me!Date

DoCmd.OpenReport strDocName, acNormal

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
If the report pops up a box asking you to fill in a parameter value for
Date2, then there is no field named "Date2" in the source query for the
report. Fix the query, or supply the correct name.

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

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

Bart said:
ok added to the open report command.
now i get a message that says printing amy2 report to my printer and
imediatly another message box that ask for the date2. when i type in the
date2 like this as a string 010305 i get the correct result. I believe
i
am close here must be something about how the date fields look at
eachother.
here is the code as it is now. Thanks again
On Error GoTo Err_Printamy2_Click

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date2]=" & Format(Me!ProductionDate, "\#mm\/dd\/yyyy\#")
DoCmd.OpenReport strDocName, acViewNormal, , strWhere

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

End Sub

Allen Browne said:
What happened to the WhereCondition bit in the OpenReport line?

Try:
DoCmd.OpenReport strDocName, acViewNormal, , strWhere

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

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

Bart said:
Allen,
I renamed my field [date] to [productiondate] rewrote the code as you
wrote. I did it both ways, my productiondate field as all my date
fields
are just formated as a date field and in the form i use a mask so the
user
only has to type in the mm dd yy. when i click on the print it prints
all
54 pages when there are only 3 or 4 pages that have that specific date
that
is on the form at that time.

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date2]=" & Format(Me!ProductionDate,
"\#mm\/dd\/yyyy\#")

DoCmd.OpenReport strDocName, acNormal

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

your help is apreciated
thanks
bart
:

First thing is that you must include # around the literal date in the
WhereCondition string. It is also worth formatting it correctly, so
the
code
works in any country. Try:
strWhere = "[date] = " & Format(Me!Date, "\#mm\/dd\/yyyy\#")

If the date field contains a time component (e.g. if you used =Now()
as
the
default value where =Date() would have been better), try:
strWhere = "[date] >= " & Format(Me!Date, "\#mm\/dd\/yyyy\#") & _
" [date] < " & Format(Me!Date + 1, "\#mm\/dd\/yyyy\#")

Finally, Date is a reserved word in VBA (for the system date), and so
not
a
good choice of field name. There are contexts where Access will
misunderstand what you mean by Date and give you wrong results.
Consider
renaming the field.

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

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

Hi, I have a form with a command button to print a report called
"amy2"
the
user may fill out 1 to many forms for any given day. an "amy2"
report
will
print a report grouped by date.. I have this code in the on click
event.
but
it prints all report for every day.. the date header has a field
called
dat by day and prints the date in long format eg Monday January 3,
2005.
on
the form we use the short format eg. 1/3/2005. I tried puting in the
same
field [date] that the form populates in the table. but still print
all
reports. can someone tell me what i am missing.
Private Sub Printamy2_Click()

On Error GoTo Err_Printamy2_Click

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date]=" & Me!Date

DoCmd.OpenReport strDocName, acNormal

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks again, Allen.
I located the date field reinserted it into the form. not called date2 but
called [productiondate] this should be the last little flaw. when the
report prints a blank page comes out first then the 2 or 3 pages of the
report print correctly. I looked into my report there are no blank pages.
what do you think? Otherwise everthing else is working fine. thanks for
your help and support.

Bart. not so frustrated anymore.

Allen Browne said:
If the report pops up a box asking you to fill in a parameter value for
Date2, then there is no field named "Date2" in the source query for the
report. Fix the query, or supply the correct name.

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

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

Bart said:
ok added to the open report command.
now i get a message that says printing amy2 report to my printer and
imediatly another message box that ask for the date2. when i type in the
date2 like this as a string 010305 i get the correct result. I believe
i
am close here must be something about how the date fields look at
eachother.
here is the code as it is now. Thanks again
On Error GoTo Err_Printamy2_Click

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date2]=" & Format(Me!ProductionDate, "\#mm\/dd\/yyyy\#")
DoCmd.OpenReport strDocName, acViewNormal, , strWhere

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

End Sub

Allen Browne said:
What happened to the WhereCondition bit in the OpenReport line?

Try:
DoCmd.OpenReport strDocName, acViewNormal, , strWhere

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

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

Allen,
I renamed my field [date] to [productiondate] rewrote the code as you
wrote. I did it both ways, my productiondate field as all my date
fields
are just formated as a date field and in the form i use a mask so the
user
only has to type in the mm dd yy. when i click on the print it prints
all
54 pages when there are only 3 or 4 pages that have that specific date
that
is on the form at that time.

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date2]=" & Format(Me!ProductionDate,
"\#mm\/dd\/yyyy\#")

DoCmd.OpenReport strDocName, acNormal

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

your help is apreciated
thanks
bart
:

First thing is that you must include # around the literal date in the
WhereCondition string. It is also worth formatting it correctly, so
the
code
works in any country. Try:
strWhere = "[date] = " & Format(Me!Date, "\#mm\/dd\/yyyy\#")

If the date field contains a time component (e.g. if you used =Now()
as
the
default value where =Date() would have been better), try:
strWhere = "[date] >= " & Format(Me!Date, "\#mm\/dd\/yyyy\#") & _
" [date] < " & Format(Me!Date + 1, "\#mm\/dd\/yyyy\#")

Finally, Date is a reserved word in VBA (for the system date), and so
not
a
good choice of field name. There are contexts where Access will
misunderstand what you mean by Date and give you wrong results.
Consider
renaming the field.

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

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

Hi, I have a form with a command button to print a report called
"amy2"
the
user may fill out 1 to many forms for any given day. an "amy2"
report
will
print a report grouped by date.. I have this code in the on click
event.
but
it prints all report for every day.. the date header has a field
called
dat by day and prints the date in long format eg Monday January 3,
2005.
on
the form we use the short format eg. 1/3/2005. I tried puting in the
same
field [date] that the form populates in the table. but still print
all
reports. can someone tell me what i am missing.
Private Sub Printamy2_Click()

On Error GoTo Err_Printamy2_Click

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date]=" & Me!Date

DoCmd.OpenReport strDocName, acNormal

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the help Allen,
I found the glitch on the blank page.
there was a small tiny space after a page break in the forms it created a
blank page at the very end of all the reports. problem solved reports are
printing great thanks to your help.


Bart said:
Thanks again, Allen.
I located the date field reinserted it into the form. not called date2 but
called [productiondate] this should be the last little flaw. when the
report prints a blank page comes out first then the 2 or 3 pages of the
report print correctly. I looked into my report there are no blank pages.
what do you think? Otherwise everthing else is working fine. thanks for
your help and support.

Bart. not so frustrated anymore.

Allen Browne said:
If the report pops up a box asking you to fill in a parameter value for
Date2, then there is no field named "Date2" in the source query for the
report. Fix the query, or supply the correct name.

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

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

Bart said:
ok added to the open report command.
now i get a message that says printing amy2 report to my printer and
imediatly another message box that ask for the date2. when i type in the
date2 like this as a string 010305 i get the correct result. I believe
i
am close here must be something about how the date fields look at
eachother.
here is the code as it is now. Thanks again
On Error GoTo Err_Printamy2_Click

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date2]=" & Format(Me!ProductionDate, "\#mm\/dd\/yyyy\#")
DoCmd.OpenReport strDocName, acViewNormal, , strWhere

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

End Sub

:

What happened to the WhereCondition bit in the OpenReport line?

Try:
DoCmd.OpenReport strDocName, acViewNormal, , strWhere

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

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

Allen,
I renamed my field [date] to [productiondate] rewrote the code as you
wrote. I did it both ways, my productiondate field as all my date
fields
are just formated as a date field and in the form i use a mask so the
user
only has to type in the mm dd yy. when i click on the print it prints
all
54 pages when there are only 3 or 4 pages that have that specific date
that
is on the form at that time.

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date2]=" & Format(Me!ProductionDate,
"\#mm\/dd\/yyyy\#")

DoCmd.OpenReport strDocName, acNormal

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

your help is apreciated
thanks
bart
:

First thing is that you must include # around the literal date in the
WhereCondition string. It is also worth formatting it correctly, so
the
code
works in any country. Try:
strWhere = "[date] = " & Format(Me!Date, "\#mm\/dd\/yyyy\#")

If the date field contains a time component (e.g. if you used =Now()
as
the
default value where =Date() would have been better), try:
strWhere = "[date] >= " & Format(Me!Date, "\#mm\/dd\/yyyy\#") & _
" [date] < " & Format(Me!Date + 1, "\#mm\/dd\/yyyy\#")

Finally, Date is a reserved word in VBA (for the system date), and so
not
a
good choice of field name. There are contexts where Access will
misunderstand what you mean by Date and give you wrong results.
Consider
renaming the field.

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

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

Hi, I have a form with a command button to print a report called
"amy2"
the
user may fill out 1 to many forms for any given day. an "amy2"
report
will
print a report grouped by date.. I have this code in the on click
event.
but
it prints all report for every day.. the date header has a field
called
dat by day and prints the date in long format eg Monday January 3,
2005.
on
the form we use the short format eg. 1/3/2005. I tried puting in the
same
field [date] that the form populates in the table. but still print
all
reports. can someone tell me what i am missing.
Private Sub Printamy2_Click()

On Error GoTo Err_Printamy2_Click

Dim strDocName As String
Dim strWhere As String
Me.Refresh

strDocName = "amy2"
strWhere = "[date]=" & Me!Date

DoCmd.OpenReport strDocName, acNormal

Exit_Printamy2_Click:
Exit Sub

Err_Printamy2_Click:
MsgBox Err.Description
Resume Exit_Printamy2_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top