Filter a Report

[injanib via AccessMonster.com]

I have a report that has two fields called "ManifestNumber" (number field)
and "RecievedOn" (Date/Time).
Then I have another form with two buttons. "Print New Manifest" and "Reprint
A Manifest".

Both of these buttons opens my report, but I like the first button, "Print
New Manifest", to filter the report for "RecievedOn" field that contains
todays date.

The second button asks to enter a Manifest Number. Then it should match this
number with the "ManifestNumber" field of the report and filter it for that
number.

Help!

 

[Duane Hookom]

I would add a text box txtReceivedOn with a default value of Date() and a
combo box cboManifest of distinct manifest numbers. Then modify the code
behind your buttons like:

Dim strWhere as String
strWhere = "ReceivedOn=#" & Me.txtReceivedOn & "#"
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere

and the other

Dim strWhere as String
strWhere = "ManifestNumber=" & Me.cboManifest
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere

 

[injanib via AccessMonster.com]

Thanks for the help, but my date field on the report is set to =date() in the
control source. I have set the input mask to display date in MM/DD/YYYY
format. When I use the method you told me I get a syntax error. Does it have
to do with inconsistent date format?

The other one works perfect.

I would add a text box txtReceivedOn with a default value of Date() and a
combo box cboManifest of distinct manifest numbers. Then modify the code
behind your buttons like:

Dim strWhere as String
strWhere = "ReceivedOn=#" & Me.txtReceivedOn & "#"
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere

and the other

Dim strWhere as String
strWhere = "ManifestNumber=" & Me.cboManifest
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere

I have a report that has two fields called "ManifestNumber" (number field)
and "RecievedOn" (Date/Time).

[quoted text clipped - 10 lines]

Help!

 

[Duane Hookom]

Do you have a field in your report's record source named "ReceivedOn"? Or, is
it spelled RecievedOn?

Filtering a report should not have anything to do with a control on your
report with a control source of =Date()

--
Duane Hookom
Microsoft Access MVP

Thanks for the help, but my date field on the report is set to =date() in the
control source. I have set the input mask to display date in MM/DD/YYYY
format. When I use the method you told me I get a syntax error. Does it have
to do with inconsistent date format?

The other one works perfect.

I would add a text box txtReceivedOn with a default value of Date() and a
combo box cboManifest of distinct manifest numbers. Then modify the code
behind your buttons like:

Dim strWhere as String
strWhere = "ReceivedOn=#" & Me.txtReceivedOn & "#"
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere

and the other

Dim strWhere as String
strWhere = "ManifestNumber=" & Me.cboManifest
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere

I have a report that has two fields called "ManifestNumber" (number field)
and "RecievedOn" (Date/Time).

[quoted text clipped - 10 lines]

Help!

 

[injanib via AccessMonster.com]

Yes there is, but I still get the same error.

Do you have a field in your report's record source named "ReceivedOn"? Or, is
it spelled RecievedOn?

Filtering a report should not have anything to do with a control on your
report with a control source of =Date()

Thanks for the help, but my date field on the report is set to =date() in the
control source. I have set the input mask to display date in MM/DD/YYYY

[quoted text clipped - 22 lines]

 

[Duane Hookom]

What is the exact code that you use? What is the name of your date field?
When you view the record source of your report in datasheet view, is the date
field left or right aligned?

--
Duane Hookom
Microsoft Access MVP

Yes there is, but I still get the same error.

Do you have a field in your report's record source named "ReceivedOn"? Or, is
it spelled RecievedOn?

Filtering a report should not have anything to do with a control on your
report with a control source of =Date()

Thanks for the help, but my date field on the report is set to =date() in the
control source. I have set the input mask to display date in MM/DD/YYYY

[quoted text clipped - 22 lines]

Help!

 

[injanib via AccessMonster.com]

The name of the field in the record source is "ReceivedOn" and the date is
right aligned with short date format. The name of the unbound textbox is
"txtDate" with default value of 'Date()' like you said.

Here is the code. It opens the report, but the report is blank even if I
enter new records.

Private Sub TodaysReport_Click()

Dim stDocName As String
Dim strWhere As String

stDocName = "Report"

strWhere = "ReceivedOn=#" & Me.txtDate & "#"
DoCmd.OpenReport stDocName, acPreview, , strWhere


End Sub

What is the exact code that you use? What is the name of your date field?
When you view the record source of your report in datasheet view, is the date
field left or right aligned?

Yes there is, but I still get the same error.

[quoted text clipped - 9 lines]

 

[Duane Hookom]

You stated earlier, "my date field on the report is set to =date()". Didn't
you mean on your form? What error message are you getting when you try to
open your report? Are from from some country where the date format is not
mm/yy/ddd?
--
Duane Hookom
Microsoft Access MVP

The name of the field in the record source is "ReceivedOn" and the date is
right aligned with short date format. The name of the unbound textbox is
"txtDate" with default value of 'Date()' like you said.

Here is the code. It opens the report, but the report is blank even if I
enter new records.

Private Sub TodaysReport_Click()

Dim stDocName As String
Dim strWhere As String

stDocName = "Report"

strWhere = "ReceivedOn=#" & Me.txtDate & "#"
DoCmd.OpenReport stDocName, acPreview, , strWhere


End Sub

What is the exact code that you use? What is the name of your date field?
When you view the record source of your report in datasheet view, is the date
field left or right aligned?

Yes there is, but I still get the same error.

[quoted text clipped - 9 lines]

Help!

 

[injanib via AccessMonster.com]

it is set to =date() in the form, however, not in the field's property. The
line [ReceivedOn] =Date() runs in the AfterUpdate event property of the field
prior to the date field, and the form is created in the U.S. I don't get any
error when it opens the report. It just pulls a blank report with no record.
It does that even if I enter new records to make sure there are records for
the date it filters for.

You stated earlier, "my date field on the report is set to =date()". Didn't
you mean on your form? What error message are you getting when you try to
open your report? Are from from some country where the date format is not
mm/yy/ddd?

The name of the field in the record source is "ReceivedOn" and the date is
right aligned with short date format. The name of the unbound textbox is

[quoted text clipped - 25 lines]

 

[Duane Hookom]

This information is new "[ReceivedOn] =Date() runs in the AfterUpdate event
property of the field prior to the date field". Are you editing a record and
setting its date hoping to print the record displayed on the form? If so, you
need to save the record in order to have the ReceivedOn updated in the record
prior to printing.
--
Duane Hookom
Microsoft Access MVP

it is set to =date() in the form, however, not in the field's property. The
line [ReceivedOn] =Date() runs in the AfterUpdate event property of the field
prior to the date field, and the form is created in the U.S. I don't get any
error when it opens the report. It just pulls a blank report with no record.
It does that even if I enter new records to make sure there are records for
the date it filters for.

You stated earlier, "my date field on the report is set to =date()". Didn't
you mean on your form? What error message are you getting when you try to
open your report? Are from from some country where the date format is not
mm/yy/ddd?

The name of the field in the record source is "ReceivedOn" and the date is
right aligned with short date format. The name of the unbound textbox is

[quoted text clipped - 25 lines]

Help!

 

[injanib via AccessMonster.com]

If I open the report manually without trying to apply any filter it will open
with no problem. I will see all the dates entered here by the code. Even the
date for the records entered today. Doesn't that say that the form's data is
saved when it shows on the report.The problem only arises when I use the
button to filter the report for todays record. it opens a blank report.

This information is new "[ReceivedOn] =Date() runs in the AfterUpdate event
property of the field prior to the date field". Are you editing a record and
setting its date hoping to print the record displayed on the form? If so, you
need to save the record in order to have the ReceivedOn updated in the record
prior to printing.

it is set to =date() in the form, however, not in the field's property. The
line [ReceivedOn] =Date() runs in the AfterUpdate event property of the field

[quoted text clipped - 12 lines]

 

[Duane Hookom]

Again, if the date has just be set in the form, you need to save the record.
When you leave the form to go to the database window, this will save the
record. You should use a line of code to save the record prior to the line of
code that opens the report.

--
Duane Hookom
Microsoft Access MVP

If I open the report manually without trying to apply any filter it will open
with no problem. I will see all the dates entered here by the code. Even the
date for the records entered today. Doesn't that say that the form's data is
saved when it shows on the report.The problem only arises when I use the
button to filter the report for todays record. it opens a blank report.

This information is new "[ReceivedOn] =Date() runs in the AfterUpdate event
property of the field prior to the date field". Are you editing a record and
setting its date hoping to print the record displayed on the form? If so, you
need to save the record in order to have the ReceivedOn updated in the record
prior to printing.

it is set to =date() in the form, however, not in the field's property. The
line [ReceivedOn] =Date() runs in the AfterUpdate event property of the field

[quoted text clipped - 12 lines]

Help!

 

[injanib via AccessMonster.com]

Back to this,

I couldn't figure this out. The code you provided does not run from the same
form as where the records are added. It runs from a different form. When I
add records they are saved before I colse the form and go to the other form
to print report. Even when I open the table I can see all the new records
which means they have been saved.

I used the code for a none Date/Time field on the report and I get what I
want. Only when I use it on a Date/Time field I get the problem. The formats
and input masks of the date in record source, report and the unbound
textfield are the same. But why do I get a blank report???????????????

Again, if the date has just be set in the form, you need to save the record.
When you leave the form to go to the database window, this will save the
record. You should use a line of code to save the record prior to the line of
code that opens the report.

If I open the report manually without trying to apply any filter it will open
with no problem. I will see all the dates entered here by the code. Even the

[quoted text clipped - 11 lines]

 

[Duane Hookom]

Have you tried using a date range? Does your date field actually store a date
with a time? You might need to use something like:

Dim strWhere as String
strWhere = "DateValue(ReceivedOn)=#" & Me.txtReceivedOn & "#"
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP

Back to this,

I couldn't figure this out. The code you provided does not run from the same
form as where the records are added. It runs from a different form. When I
add records they are saved before I colse the form and go to the other form
to print report. Even when I open the table I can see all the new records
which means they have been saved.

I used the code for a none Date/Time field on the report and I get what I
want. Only when I use it on a Date/Time field I get the problem. The formats
and input masks of the date in record source, report and the unbound
textfield are the same. But why do I get a blank report???????????????

Again, if the date has just be set in the form, you need to save the record.
When you leave the form to go to the database window, this will save the
record. You should use a line of code to save the record prior to the line of
code that opens the report.

If I open the report manually without trying to apply any filter it will open
with no problem. I will see all the dates entered here by the code. Even the

[quoted text clipped - 11 lines]

Help!

 

[injanib via AccessMonster.com]

That worked. Thanks a bunch.

Have you tried using a date range? Does your date field actually store a date
with a time? You might need to use something like:

Dim strWhere as String
strWhere = "DateValue(ReceivedOn)=#" & Me.txtReceivedOn & "#"
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere

Back to this,

[quoted text clipped - 19 lines]