Duane
The report contains the "Date" field
No all the reports open with all dates from earlist to current date
SQL View
SELECT [Telephone Checklist].CustomerID, [Telephone Checklist].Date,
[Telephone Checklist].[Driver's First Name], [Telephone
Checklist].[Driver's Last Name], [Telephone Checklist].DateOfAccident,
[Telephone Checklist].[Registration Number], [Telephone
Checklist].ClaimFormReceived, [Telephone Checklist].ClaimCode, [Telephone
Checklist].[AccidentDamageDetails (Whats wrong with car)], [Telephone
Checklist].[Policy Number], [Telephone Checklist].Recoverable, [Telephone
Checklist].RBQNumbers, [Telephone Checklist].Employer
FROM [Telephone Checklist] INNER JOIN Bodyshops ON [Telephone
Checklist].BodyshopID = Bodyshops.[Bodyshop Name]
WHERE ((([Telephone Checklist].[Policy Number])="RBQ124587"))
ORDER BY [Telephone Checklist].CustomerID;
The "Date" field is actually date type set to short date 20/10/05 format
Regards Bob
Duane Hookom said:
Does your report recordsource contain the field [Date]?
Do you get any prompts when you attempt to run the report?
What is the SQL view of your report's recordsource?
Is your [Date] field actually date type?
--
Duane Hookom
MS Access MVP
Duane I have cut and pasted the code in to mine and added the strWhere
in but it opens all the reports as before with all dates not the
selected dates. I must be doing something wrong.
The "Date" field is in the qry but not on the form or the reports. Its
the field I used the [Enter The Start Date] And [Enter The Finish Date]
in the qry, I have deleted this from all the report qrys criteria is
that correct?
Bob
You didn't include the strWhere as the where clause in
DoCmd.OpenReport.
Also make sure you get the appropriate spaces into your expression.
Dim stDocName As String
Dim strWhere As String
strWhere ="[Date] between #"& Me.txtStartDate& _
"# AND #" & Me.txtEndDate & "#"
stDocName = "rpt1"
DoCmd.OpenReport stDocName, acPreview, , strWhere
DoCmd.RunCommand acCmdZoom75
--
Duane Hookom
MS Access MVP
--
How do I pick up the "Date" field? do I do it as follows;
[TelephoneCheckList]![Date] TelephoneChecklist being the table and
Date being the field we need to query by (I have tried that but it
didn't work)
I do not understand that part of the code the [SomeDateField] isn't
it support to pick up the dates from the txtStartDate & txtEndDate
field on my form "frmReportPrinter" ? but then the "Date" field needs
to be in there somewhere because that's the date we qry by.
Thanks for your help Bob
John I have done that it now runs but it takes no notice of the
txtStartDate and txtEndDate on the form it just gives every date
from the first input date to today's date.
Pardon me for jumping in.
strWhere ="[Date]between#"& Me.txtStartDate&"# AND #" &
Me.txtEndDate & "#"
Should all be on ONE LINE. The newsreader helpfully inserted a new
line there
You can also enter it as
strWhere ="[Date]between#"& Me.txtStartDate&"# AND #" & _
Me.txtEndDate & "#"
The Space and Underline tell VBA that this is a continued line.
Are there any other lines that are RED. If so, they also need to
be fixed, but the fix is not necessarily the same. The red just
means the compiler doesn't like the syntax of the line.
Duane thanks for your help
I have entered the following:
Dim stDocName As String
Dim strWhere As String
strWhere ="[Date]between#"& Me.txtStartDate&"# AND #" &
Me.txtEndDate & "#"
stDocName = "rpt1"
DoCmd.OpenReport stDocName, acPreview
DoCmd.RunCommand acCmdZoom75
But I get a compile error expected end of statement, it highlights
the strWhere line "# AND #" part is in red
I take it the SomeDateField is the Date field that's common to all
the report query's?
Bob
Remove the criteria from your report's record source, add a
couple text boxes to your form to allow users to enter the dates,
and modify your code:
Dim stDocName As String
Dim strWhere as String
strWhere = "[SomeDateField] Between #" & _
Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"
stDocName = "rpt1"
DoCmd.OpenReport stDocName, acPreview, , strWhere
DoCmd.RunCommand acCmdZoom75
stDocName = "rpt2"
DoCmd.OpenReport stDocName, acPreview, , strWhere
DoCmd.RunCommand acCmdZoom75
stDocName = "rpt3"
'etc
--
Duane Hookom
MS Access MVP
--
Hi I use the following code to pull off 6 reports, with
"Between [Enter The Start Date] And [Enter The Finish Date]" in
the query date field of each report qry. It all works but is it
possible to set it up in some way so that the start and finish
dates only have to be input once rather that 6 times? I have
several multiple reports to pull off and its easy to make a
mistake with the dates.
Dim stDocName As String
stDocName = "rpt1"
DoCmd.OpenReport stDocName, acPreview
DoCmd.RunCommand acCmdZoom75
stDocName = "rpt2"
DoCmd.OpenReport stDocName, acPreview
DoCmd.RunCommand acCmdZoom75
stDocName = "rpt3"
DoCmd.OpenReport stDocName, acPreview
DoCmd.RunCommand acCmdZoom75
stDocName = "rpt4"
DoCmd.OpenReport stDocName, acPreview
DoCmd.RunCommand acCmdZoom75
stDocName = "rpt5"
DoCmd.OpenReport stDocName, acPreview
DoCmd.RunCommand acCmdZoom75
stDocName = "rpt6"
DoCmd.OpenReport stDocName, acPreview
DoCmd.RunCommand acCmdZoom75
Any help would be very much apprecaited.
Bob
