A Simple (I hope) Question re: Reports

  • Thread starter Thread starter Gord
  • Start date Start date
G

Gord

Ok, so I designed a form to track calls to my clients. I am supposed to
report my calls everyday. I have designed a report that has the fields from
the form that I need to send to my head office. But no matter what I try,
all records print, not just the ones from today.

Ideal Solution
It would be great if I could have a button on the form called "Generate
Report". When you click it, it asks you in a pop up box "For which date".
I enter a date in the same format they are in the records and ONLY those get
printed on the report.

Any suggestions?
 
Gord said:
Ok, so I designed a form to track calls to my clients. I am supposed to
report my calls everyday. I have designed a report that has the fields from
the form that I need to send to my head office. But no matter what I try,
all records print, not just the ones from today.

Ideal Solution
It would be great if I could have a button on the form called "Generate
Report". When you click it, it asks you in a pop up box "For which date".
I enter a date in the same format they are in the records and ONLY those get
printed on the report.
Click to expand...

What have you tried? You need to use the WHERE argument of DoCmd.OpenReport.

EXAMPLE:

(opens a report with field [RecordDate] matching what the user enters)

Dim MyDate as Variant

MyDate = InputBox("Please enter the desired date")

If IsDate(MyDate) = True Then
DoCmd.OpenReport "ReportName",,,"RecordDate = #" & CDate(MyDate) & "#"
End If


Dates are not stored in any format. That is only for display. Any criteria for
a date field must use an unambiguous date format or the US format of mm-dd-yy.

The above would only work if RecordDate has no time component. Again it does
not matter that you might be using a format without a time. There must actually
be no time (midnight actually) in the records or else you have to use a BETWEEN
clause or strip the time portion off of the field in your comparison (not
recommended because it would be slower).
 
I actually tried with filters to no success. Where would I put the lines
you listed below? (New to Access, as you can guess)

--

-------------------
Email me at:
gordonrmac
at
hotmail.com
Rick Brandt said:
Gord said:
Ok, so I designed a form to track calls to my clients. I am supposed to
report my calls everyday. I have designed a report that has the fields from
the form that I need to send to my head office. But no matter what I try,
all records print, not just the ones from today.

Ideal Solution
It would be great if I could have a button on the form called "Generate
Report". When you click it, it asks you in a pop up box "For which date".
I enter a date in the same format they are in the records and ONLY those get
printed on the report.
Click to expand...

What have you tried? You need to use the WHERE argument of DoCmd.OpenReport.

EXAMPLE:

(opens a report with field [RecordDate] matching what the user enters)

Dim MyDate as Variant

MyDate = InputBox("Please enter the desired date")

If IsDate(MyDate) = True Then
DoCmd.OpenReport "ReportName",,,"RecordDate = #" & CDate(MyDate) & "#"
End If


Dates are not stored in any format. That is only for display. Any criteria for
a date field must use an unambiguous date format or the US format of mm-dd-yy.

The above would only work if RecordDate has no time component. Again it does
not matter that you might be using a format without a time. There must actually
be no time (midnight actually) in the records or else you have to use a BETWEEN
clause or strip the time portion off of the field in your comparison (not
recommended because it would be slower).
Click to expand...
 
Oh, and I also want it to output to a DOC or TXT file. Does that change
things?

--

-------------------
Email me at:
gordonrmac
at
hotmail.com
Rick Brandt said:
Gord said:
Ok, so I designed a form to track calls to my clients. I am supposed to
report my calls everyday. I have designed a report that has the fields from
the form that I need to send to my head office. But no matter what I try,
all records print, not just the ones from today.

Ideal Solution
It would be great if I could have a button on the form called "Generate
Report". When you click it, it asks you in a pop up box "For which date".
I enter a date in the same format they are in the records and ONLY those get
printed on the report.
Click to expand...

What have you tried? You need to use the WHERE argument of DoCmd.OpenReport.

EXAMPLE:

(opens a report with field [RecordDate] matching what the user enters)

Dim MyDate as Variant

MyDate = InputBox("Please enter the desired date")

If IsDate(MyDate) = True Then
DoCmd.OpenReport "ReportName",,,"RecordDate = #" & CDate(MyDate) & "#"
End If


Dates are not stored in any format. That is only for display. Any criteria for
a date field must use an unambiguous date format or the US format of mm-dd-yy.

The above would only work if RecordDate has no time component. Again it does
not matter that you might be using a format without a time. There must actually
be no time (midnight actually) in the records or else you have to use a BETWEEN
clause or strip the time portion off of the field in your comparison (not
recommended because it would be slower).
Click to expand...
 
Gord said:
I actually tried with filters to no success. Where would I put the lines
you listed below? (New to Access, as you can guess)
Click to expand...

It would go in the code that is going to open the report (typically behind a
command button). I have never used the filter argument of DoCmd.OpenReport (the
third argument) so I can't comment on why that might or might not have worked
for you.

If you want to output to a file then you have to use DoCmd.OutputTo instead of
DoCmd.OpenReport. Unfortunately, OutputTo doesn't allow for dynamic filtering.
There are several work-arounds including basing the report on a query that gets
its criteria from your form so the filtering is then built in to the query
instead of the report.

Your query would look something like...

SELECT * FROM SomeTable
WHERE RecordDate = Forms!NameOfForm!NameOfControl
 
What about generating the report, and then sending it to MS Word? Or does
it have to go direct to a printer from the command button?
 
Gord said:
What about generating the report, and then sending it to MS Word? Or does
it have to go direct to a printer from the command button?
Click to expand...

The RTF conversion process when sending reports to Word is unreliable. You will
lose all graphical elements (that's a given) and often will have messed up
placement of tabs, line returns, as well as text truncation.
 
Well, do you have any suggestions?

The report is emailed to my boss at the end of day, as of now I just type a
list in MS Word as I go. I would like the database option, that way I have
a record, and I can add details. I supposed I could just generate a report
sorted by the date, and manually delete everything before today's calls.
 
Gord said:
Well, do you have any suggestions?

The report is emailed to my boss at the end of day, as of now I just type a
list in MS Word as I go. I would like the database option, that way I have
a record, and I can add details. I supposed I could just generate a report
sorted by the date, and manually delete everything before today's calls.
Click to expand...

If it's a simple tabular list you could send it as an Excel or text file.
Simple lists will even be fairly reliable outputting to word. It's when you get
into more complex reports with headers/footers and controls that grow that most
of the problems show up.

Snapshot format will let you send a file that looks exactly the same as your
Access report.
 
Ok, so I go to design view for my form, create a button then what? I assume
I point it to my already laid out report, but where do I copy paste that
info you gave me in your first post? Using Access 2003.
 
I tried to use the ApplyFilter Macro on Click for the button, but no good.
This should be an easy way to get rid of all the records whose date does not
match today's (or the one I enter) and then send these records to a report.

If I apply a filter by date, it does get rid of the other records, but as
soon as I preview a report, the other records are listed as well...
 
Gord said:
Ok, so I go to design view for my form, create a button then what? I assume
I point it to my already laid out report, but where do I copy paste that
info you gave me in your first post? Using Access 2003.
Click to expand...

In the Click event property of the button select "[Event Procedure]" and then
press the build button [...] to the right of that entry. This will take you to
the VBA code window displaying the module behind the form and already positioned
between the lines that define the start and end of the button's click event.

Any code you want to execute when the button is clicked goes between those two
lines.
 
So it shows me this:

Private Sub Command80_Click()
On Error GoTo Err_Command80_Click

Dim stDocName As String

stDocName = "Client Data - Main"
DoCmd.OutputTo acReport, stDocName

Exit_Command80_Click:
Exit Sub

Err_Command80_Click:
MsgBox Err.Description
Resume Exit_Command80_Click

End Sub

What do I take out, and what do I put in?

--

-------------------
Email me at:
gordonrmac
at
hotmail.com
Rick Brandt said:
Gord said:
Ok, so I go to design view for my form, create a button then what? I assume
I point it to my already laid out report, but where do I copy paste that
info you gave me in your first post? Using Access 2003.
Click to expand...

In the Click event property of the button select "[Event Procedure]" and then
press the build button [...] to the right of that entry. This will take you to
the VBA code window displaying the module behind the form and already positioned
between the lines that define the start and end of the button's click event.

Any code you want to execute when the button is clicked goes between those two
lines.
Click to expand...
 
Gord said:
So it shows me this:

Private Sub Command80_Click()
On Error GoTo Err_Command80_Click

Dim stDocName As String

stDocName = "Client Data - Main"
DoCmd.OutputTo acReport, stDocName

Exit_Command80_Click:
Exit Sub

Err_Command80_Click:
MsgBox Err.Description
Resume Exit_Command80_Click

End Sub

What do I take out, and what do I put in?
Click to expand...

I don't understand the question. That code will produce a prompt to allow you
to specify the output format and then the file name and location for outputting
a report to a file. Is that what you want to do?

None of that has anything to do with getting the report to be filtered on a
specific criteria though. Wasn't that the original question?
 
That code is from where I added a button, and then went to the expression
builder....I assume it's the default code, and I was wondering how to change
it..

I think I found a work around, considering I already do 3 mail merges from
this database, I can just do a 4th for my daily reporting... Many thanks..
 
Back
Top