SQL Statement for Range of Dates

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

Guest

I need an SQL Statement for Reporting Date Ranges for a database i am
creating for a company i work for. The current Report Date Range doesn't
seem to work, Today was the first day i entered information into it and i ran
the report for yesterday's date and it brought up all of today's information.
No matter what date i put in there it seems to be bringing up all the
information regardless of date. Can anybody help me?
 
There you go. I think i have figured it out, Under the Private Sub
Form_Open(Cancel As Integer)
and whatnot there is no reference to anything.
Am i right in assuming that?

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub

Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub
 
I can't see the open report statement

docmd.OpenReport "ReportName",,, "[Order Date Name In the Table] >= #" &
[Beginning Order Date] & "# And [Order Date Name In the Table] <= #" &
[Ending Order Date] & "#"


JeremyH1982 said:
There you go. I think i have figured it out, Under the Private Sub
Form_Open(Cancel As Integer)
and whatnot there is no reference to anything.
Am i right in assuming that?

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub

Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub



Ofer said:
Can you post the SQL you created for the report?
Click to expand...
Click to expand...
 
I am trying to get a date range for all of the reports that i had created.
Not just one. Do i need to write a Report Date Range for all of the reports
that i want to report the date range?

Ofer said:
I can't see the open report statement

docmd.OpenReport "ReportName",,, "[Order Date Name In the Table] >= #" &
[Beginning Order Date] & "# And [Order Date Name In the Table] <= #" &
[Ending Order Date] & "#"


JeremyH1982 said:
There you go. I think i have figured it out, Under the Private Sub
Form_Open(Cancel As Integer)
and whatnot there is no reference to anything.
Am i right in assuming that?

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub

Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub



Ofer said:
Can you post the SQL you created for the report?

:

I need an SQL Statement for Reporting Date Ranges for a database i am
creating for a company i work for. The current Report Date Range doesn't
seem to work, Today was the first day i entered information into it and i ran
the report for yesterday's date and it brought up all of today's information.
No matter what date i put in there it seems to be bringing up all the
information regardless of date. Can anybody help me?
Click to expand...
Click to expand...
Click to expand...
 
You need to set each report criteria

Dim MyCrit as string
MyCrit = "[Order Date Name In the Table] >= #" & [Beginning Order Date] & "#
And [Order Date Name In the Table] <= #" & [Ending Order Date] & "#"

docmd.OpenReport "Repor1tName",,, MyCrit
docmd.OpenReport "Repor2tName",,, MyCrit
docmd.OpenReport "Repor3tName",,, MyCrit
docmd.OpenReport "Repor4tName",,, MyCrit
===================================================
The other option, if you use the same table for all the reports, or for few
of them, then you can put the criteria in a query, and then use this query
for all the reports, and then open the reports without the criteria

Select * From My Table Where [Order Date Name In the Table] Between
Forms![FormName]![Beginning Order Date] And Forms![FormName]![Ending Order
Date]


JeremyH1982 said:
I am trying to get a date range for all of the reports that i had created.
Not just one. Do i need to write a Report Date Range for all of the reports
that i want to report the date range?

Ofer said:
I can't see the open report statement

docmd.OpenReport "ReportName",,, "[Order Date Name In the Table] >= #" &
[Beginning Order Date] & "# And [Order Date Name In the Table] <= #" &
[Ending Order Date] & "#"


JeremyH1982 said:
There you go. I think i have figured it out, Under the Private Sub
Form_Open(Cancel As Integer)
and whatnot there is no reference to anything.
Am i right in assuming that?

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub

Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub



:

Can you post the SQL you created for the report?

:

I need an SQL Statement for Reporting Date Ranges for a database i am
creating for a company i work for. The current Report Date Range doesn't
seem to work, Today was the first day i entered information into it and i ran
the report for yesterday's date and it brought up all of today's information.
No matter what date i put in there it seems to be bringing up all the
information regardless of date. Can anybody help me?
Click to expand...
Click to expand...
Click to expand...
 
In the One Report i have

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Report Date Range", , , , , acDialog, "Sales By Product"
If Not IsLoaded("Report Date Range") Then
Cancel = True
End If
End Sub

Then for the Report Date Range i have

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub

Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub

No matter what date i put in, everything comes up. On another report though
i put in a specific date and just whatever is in that date range comes up. I
don't see what i am doing wrong.
The report that reports correctly is written as:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Report Date Range", , , , , acDialog, "Sales by Employee"
If Not IsLoaded("Report Date Range") Then
Cancel = True
End If
End Sub

And the Report Date Range is the same as above:

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub

Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub

I don't see a difference between the two, but one reports correctly and the
top one doesn't. I do appreciate the help. Thank You
 
Check the Record source of the two reports, to see if in the first report
there is a reference to the fields in the report

Can you post the SQL of the record source of the report
 
SELECT DISTINCTROW [LastName] & ", " & [FirstName] AS [Employee Name],
Sum([Sales by Employee Subquery].[Total Sales]) AS [Total Sales],
Sum(CLng([Sales by Employee Subquery].[Total Sales]*[Sales by Employee
Subquery].[SalesTaxRate]*100)/100) AS [Sales Tax], Sum([Sales by Employee
Subquery].[FreightCharge]) AS [Total Freight], Sum([Sales by Employee
Subquery].[Total Units]) AS [Total Units] FROM Employees INNER JOIN [Sales by
Employee Subquery] ON [Employees].[EmployeeID]=[Sales by Employee
Subquery].[EmployeeID] WHERE ((([Sales by Employee
Subquery].[OrderDate])>=[forms]![Report Date Range]![Beginning Order Date]
And ([Sales by Employee Subquery].[OrderDate])<=[forms]![Report Date
Range]![Ending Order Date])) GROUP BY [LastName] & ", " & [FirstName];

This one works the way it's supposed to. now i will give you an example of
one that doesn't work the way it's supposed to and no matter what date i
enter it reports everything.

SELECT [Customers].[CompanyName], [Customers].[CustomerID], [Order
Details].[ProductID], [Order Details].[Quantity], [Order Details].[UnitPrice]
FROM (Customers INNER JOIN Orders ON
[Customers].[CustomerID]=[Orders].[CustomerID]) INNER JOIN [Order Details] ON
[Orders].[OrderID]=[Order Details].[OrderID];

Ofer said:
Check the Record source of the two reports, to see if in the first report
there is a reference to the fields in the report

Can you post the SQL of the record source of the report

JeremyH1982 said:
In the One Report i have

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Report Date Range", , , , , acDialog, "Sales By Product"
If Not IsLoaded("Report Date Range") Then
Cancel = True
End If
End Sub

Then for the Report Date Range i have

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub

Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub

No matter what date i put in, everything comes up. On another report though
i put in a specific date and just whatever is in that date range comes up. I
don't see what i am doing wrong.
The report that reports correctly is written as:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Report Date Range", , , , , acDialog, "Sales by Employee"
If Not IsLoaded("Report Date Range") Then
Cancel = True
End If
End Sub

And the Report Date Range is the same as above:

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub

Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub

I don't see a difference between the two, but one reports correctly and the
top one doesn't. I do appreciate the help. Thank You
Click to expand...
Click to expand...
 
I found the problem. It's kind of a stupid mistake but still it's a mistake,
and if you wouldn't of asked me to post the Record Source i would of never
found it.
Thanks to you all of my reports give correct information in the date ranges
i specify
Thank You
Jeremy
Computer Project Coordinator / Network Administrator

Ofer said:
Check the Record source of the two reports, to see if in the first report
there is a reference to the fields in the report

Can you post the SQL of the record source of the report

JeremyH1982 said:
In the One Report i have

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Report Date Range", , , , , acDialog, "Sales By Product"
If Not IsLoaded("Report Date Range") Then
Cancel = True
End If
End Sub

Then for the Report Date Range i have

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub

Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub

No matter what date i put in, everything comes up. On another report though
i put in a specific date and just whatever is in that date range comes up. I
don't see what i am doing wrong.
The report that reports correctly is written as:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Report Date Range", , , , , acDialog, "Sales by Employee"
If Not IsLoaded("Report Date Range") Then
Cancel = True
End If
End Sub

And the Report Date Range is the same as above:

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub

Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub

I don't see a difference between the two, but one reports correctly and the
top one doesn't. I do appreciate the help. Thank You
Click to expand...
Click to expand...
 
Back
Top