date compare results not working

K

Kevin Labore

Hi

the following SQL is obvisouly not working I simply wrote for viewing
What I want is for to generate a crosstab based on StartDate and EndDate (at
runtime)
so for example if a person enter 5/10/04(StartDate) and 6/10/05(EndDate) it
will only include those dates between 5/10 and 6/10 (for this year and last
year) but anything else (ie.no other dates in June (after 10th) or the other
dates between 6/10/04 and 5/10/05. I know I am missing the logic on this one

Below is SQL ---- Can some tell me how to go about this?

Thanks
Kevin
----------------------------------------------------
PARAMETERS StartDate DateTime, EndDate DateTime;
TRANSFORM Sum([DeptsSales Query].DeptSales) AS SumOfDeptSales
SELECT [DeptsSales Query].DeptDesc, Depts.OrderPerson, Avg([DeptsSales
Query].DeptSales) AS [AVG Of DeptSales], Sum([DeptsSales Query].DeptSales)
AS TotalDeptSales
FROM [DeptsSales Query] INNER JOIN Depts ON ([DeptsSales Query].DeptDesc =
Depts.DeptDesc) AND ([DeptsSales Query].DeptNum = Depts.DeptNum)
WHERE (((Month([DeptDate])) Between Month([StartDate]) And Month([EndDate]))
AND ((Day([deptdate])) Between Day([StartDate]) And Day([EndDate])))
GROUP BY [DeptsSales Query].DeptDesc, Depts.OrderPerson
ORDER BY [DeptsSales Query].DeptDesc, Depts.OrderPerson,
DatePart("yyyy",[deptdate],1,1) DESC
PIVOT DatePart("yyyy",[deptdate],1,1);
---------------------------------------------------------
 
G

Gary Walter

Hi Kevin,

I believe this is what you wanted:

WHERE
(
[DeptDate]>=[StartDate]
AND
[DeptDate]<[EndDate] + 1
)
OR
(
[DeptDate]>=DateSerial(Year([StartDate])-1, Month([StartDate]),
Day([StartDate]))
AND
[DeptDate]<DateSerial(Year([EndDate])-1, Month([EndDate]) , Day([EndDate]) +
1)
)

good luck,

gary
 
K

Kevin Labore

hi Gary

Thanks that is what I wanted. I remember seeing it done before but I was
drawing a blank.

thanks for the help
Kevin

Gary Walter said:
Hi Kevin,

I believe this is what you wanted:

WHERE
(
[DeptDate]>=[StartDate]
AND
[DeptDate]<[EndDate] + 1
)
OR
(
[DeptDate]>=DateSerial(Year([StartDate])-1, Month([StartDate]),
Day([StartDate]))
AND
[DeptDate]<DateSerial(Year([EndDate])-1, Month([EndDate]) , Day([EndDate])
+ 1)
)

good luck,

gary

the following SQL is obvisouly not working I simply wrote for viewing
What I want is for to generate a crosstab based on StartDate and EndDate
(at runtime)
so for example if a person enter 5/10/04(StartDate) and 6/10/05(EndDate)
it will only include those dates between 5/10 and 6/10 (for this year and
last year) but anything else (ie.no other dates in June (after 10th) or
the other dates between 6/10/04 and 5/10/05. I know I am missing the
logic on this one

Below is SQL ---- Can some tell me how to go about this?

Thanks
Kevin
----------------------------------------------------
PARAMETERS StartDate DateTime, EndDate DateTime;
TRANSFORM Sum([DeptsSales Query].DeptSales) AS SumOfDeptSales
SELECT [DeptsSales Query].DeptDesc, Depts.OrderPerson, Avg([DeptsSales
Query].DeptSales) AS [AVG Of DeptSales], Sum([DeptsSales
Query].DeptSales) AS TotalDeptSales
FROM [DeptsSales Query] INNER JOIN Depts ON ([DeptsSales Query].DeptDesc
= Depts.DeptDesc) AND ([DeptsSales Query].DeptNum = Depts.DeptNum)
WHERE (((Month([DeptDate])) Between Month([StartDate]) And
Month([EndDate])) AND ((Day([deptdate])) Between Day([StartDate]) And
Day([EndDate])))
GROUP BY [DeptsSales Query].DeptDesc, Depts.OrderPerson
ORDER BY [DeptsSales Query].DeptDesc, Depts.OrderPerson,
DatePart("yyyy",[deptdate],1,1) DESC
PIVOT DatePart("yyyy",[deptdate],1,1);
Click to expand...
Click to expand...
 
K

Kevin Labore

OOPs
I thought that would work
but if I enter 5/24/04 StartDate and 6/9/05 EndDate
it give me all dates between that range

What I want the column results to be is
5/24/04 thru 6/9/04 in the 2004 col
and 5/24/05 thru 6/9/05 in the 2005 col
the col would be the sum of said range and the compare those two ranges
against each other (via a report off the query)

this make sense?

Thanks
Kevin

Gary Walter said:
Hi Kevin,

I believe this is what you wanted:

WHERE
(
[DeptDate]>=[StartDate]
AND
[DeptDate]<[EndDate] + 1
)
OR
(
[DeptDate]>=DateSerial(Year([StartDate])-1, Month([StartDate]),
Day([StartDate]))
AND
[DeptDate]<DateSerial(Year([EndDate])-1, Month([EndDate]) , Day([EndDate])
+ 1)
)

good luck,

gary

the following SQL is obvisouly not working I simply wrote for viewing
What I want is for to generate a crosstab based on StartDate and EndDate
(at runtime)
so for example if a person enter 5/10/04(StartDate) and 6/10/05(EndDate)
it will only include those dates between 5/10 and 6/10 (for this year and
last year) but anything else (ie.no other dates in June (after 10th) or
the other dates between 6/10/04 and 5/10/05. I know I am missing the
logic on this one

Below is SQL ---- Can some tell me how to go about this?

Thanks
Kevin
----------------------------------------------------
PARAMETERS StartDate DateTime, EndDate DateTime;
TRANSFORM Sum([DeptsSales Query].DeptSales) AS SumOfDeptSales
SELECT [DeptsSales Query].DeptDesc, Depts.OrderPerson, Avg([DeptsSales
Query].DeptSales) AS [AVG Of DeptSales], Sum([DeptsSales
Query].DeptSales) AS TotalDeptSales
FROM [DeptsSales Query] INNER JOIN Depts ON ([DeptsSales Query].DeptDesc
= Depts.DeptDesc) AND ([DeptsSales Query].DeptNum = Depts.DeptNum)
WHERE (((Month([DeptDate])) Between Month([StartDate]) And
Month([EndDate])) AND ((Day([deptdate])) Between Day([StartDate]) And
Day([EndDate])))
GROUP BY [DeptsSales Query].DeptDesc, Depts.OrderPerson
ORDER BY [DeptsSales Query].DeptDesc, Depts.OrderPerson,
DatePart("yyyy",[deptdate],1,1) DESC
PIVOT DatePart("yyyy",[deptdate],1,1);
Click to expand...
Click to expand...
 
G

Gary Walter

OOPs
I thought that would work
but if I enter 5/24/04 StartDate and 6/9/05 EndDate
it give me all dates between that range

What I want the column results to be is
5/24/04 thru 6/9/04 in the 2004 col
and 5/24/05 thru 6/9/05 in the 2005 col
the col would be the sum of said range and the compare those two ranges
against each other (via a report off the query)

this make sense?
Click to expand...
Hi Kevin,

I understand...that's why I asked to "be ignored." :cool:

I have several reports in various dbs
where the user compares 2 intervals
of data in a report.

IMHO, it gets too complicated to use parameters
like you wanted to do.

I use a parameter form for the users to set the intervals.

Each interval gets a start and end date text box,
as well as a "NumDays" text box.

I use the calendar control and put little
command buttons next to the start/end
date text boxes (with 3 dots, i.e., elipses)
which set the date text boxes to calendar control.

This way I can make sure begin dates
are less than end dates, and both intervals
have same number of days in events behind
the text boxes.

The form (say "frmDateRange") is bound
to a table ("tblDateRange") with 4 fields
(StartDate1, EndDate1, StartDate2, EndDate2)
so next time they invoke the parameter
form it shows previous intervals. Each
FE gets their own "tblDateRange."

On most of these forms the EndDate2
text box (and NumDays2 text box) is
enabled/locked and is updated in code
after 3 other date values are selected.

I have a "Preview Report" command button
where I do a final check on date values before
pulling up report.

Most of these are more complicated in that
I redefine passthrough queries for each interval
using the form's dates, then use these in stored
queries that append the passthrough query data to
a report table ("tblInterval") that initially was
cleared of records.

In your case, I *might* just redefine a report
query....something like:

Dim strSQL As String
Dim varBeginDate1 As Variant
Dim varEndDate1 As Variant
Dim varBeginDate2 As Variant
Dim varEndDate2 As Variant
Dim strDocName As String

varBeginDate1 = Me!txtBeginDate1
varEndDate1 = Me!txtEndDate1
varBeginDate2 = Me!txtBeginDate2
varEndDate2 = Me!txtEndDate2


If Not (IsDate(varBeginDate1) And IsDate(varEndDate1) And IsDate(varBeginDate2) And IsDate(varEndDate2)) Then
MsgBox "You must enter valid beginning and ending dates for Intervals."
DoCmd.GoToControl "txtBeginDate1"
Exit Sub
Else
If (varBeginDate1 > varEndDate1) Or (varBeginDate2 > varEndDate2) Then
MsgBox "Ending dates must be greater than Beginning dates."
DoCmd.GoToControl "txtBeginDate1"
Exit Sub
Else

strSQL = "TRANSFORM Sum([DeptsSales Query].DeptSales) AS SumOfDeptSales " _
& "SELECT [DeptsSales Query].DeptDesc, Depts.OrderPerson, " _
& "Avg([DeptsSales Query].DeptSales) AS [AVG Of DeptSales], " _
& "Sum([DeptsSales Query].DeptSales) AS TotalDeptSales " _
& "FROM [DeptsSales Query] INNER JOIN Depts " _
& "ON ([DeptsSales Query].DeptDesc = Depts.DeptDesc) " _
& "AND ([DeptsSales Query].DeptNum = Depts.DeptNum) " _
& "WHERE " _
& "([DeptDate] >= #" & varBeginDate1 & "# " _
& "AND [DeptDate] < #" & varEndDate1 & "# + 1) " _
& "OR "
& "([DeptDate] >= #" & varBeginDate2 & "# " _
& "AND [DeptDate] < #" & varEndDate2 & "# + 1) " _
& "GROUP BY [DeptsSales Query].DeptDesc, Depts.OrderPerson " _
& "ORDER BY [DeptsSales Query].DeptDesc, Depts.OrderPerson, " _
& "DatePart("yyyy",[deptdate],1,1) DESC " _
& "PIVOT DatePart("yyyy",[deptdate],1,1);"

CurrentDb.QueryDefs("qryRpt").SQL = strSQL

strDocName = "rptIntervalComparison"
DoCmd.OpenReport stDocName, acPreview

End If

End If
 
G

Gary Walter

FYI, here be other typical code for parameter form:

Private Sub Form_Load()
Dim ctl As Control

'set calendar to today's date
Set ctl = Me!objCalendar
ctl.Value = Date

End Sub

Private Sub cmdSetBeginDate1_Click()
Dim ctl As Control
Set ctl = Me!objCalendar
If Me!txtEndDate1 >= ctl.Value Or IsNull(Me!txtEndDate1) Then
Me!txtBeginDate1 = ctl.Value
If IsDate(Me!txtEndDate1) Then
Me!txtNumDays1 = CDate(Me!txtEndDate1) - CDate(Me!txtBeginDate1)
Else
Me!txtNumDays1 = Null
End If
Else
MsgBox "Beginning Date must be earlier than Ending Date."
End If
End Sub

Private Sub cmdSetEndDate1_Click()
Dim ctl As Control
Set ctl = Me!objCalendar
If Me!txtBeginDate1 <= ctl.Value Or IsNull(Me!txtBeginDate1) Then
Me!txtEndDate1 = ctl.Value
If IsDate(Me!txtBeginDate1) Then
Me!txtNumDays1 = CDate(Me!txtEndDate1) - CDate(Me!txtBeginDate1)
Else
Me!txtNumDays1 = Null
End If
Else
MsgBox "Ending Date must be later than Beginning Date."
End If

End Sub

Private Sub cmdSetBeginDate2_Click()
Dim ctl As Control
Set ctl = Me!objCalendar
If Me!txtEndDate2 >= ctl.Value Or IsNull(Me!txtEndDate2) Then
Me!txtBeginDate2 = ctl.Value
If IsDate(Me!txtEndDate2) Then
Me!txtNumDays2 = CDate(Me!txtEndDate2) - CDate(Me!txtBeginDate2)
Else
Me!txtNumDays2 = Null
End If
Else
MsgBox "Beginning Date must be earlier than Ending Date."
End If
End Sub

Private Sub cmdSetEndDate2_Click()
Dim ctl As Control
Set ctl = Me!objCalendar
If Me!txtBeginDate2 <= ctl.Value Or IsNull(Me!txtBeginDate2) Then
Me!txtEndDate2 = ctl.Value
If IsDate(Me!txtBeginDate2) Then
Me!txtNumDays2 = CDate(Me!txtEndDate2) - CDate(Me!txtBeginDate2)
Else
Me!txtNumDays2 = Null
End If
Else
MsgBox "Ending Date must be later than Beginning Date."
End If

End Sub
 
K

Kevin Labore

HI Gary

thanks for all the help...it is a little complicated I agree.
The first way works actually but not the way you want (if you enter the years under 05 it works I believe)
I with with the other 2 examples

thanks for you help again.

Kevin

FYI, here be other typical code for parameter form:

Private Sub Form_Load()
Dim ctl As Control

'set calendar to today's date
Set ctl = Me!objCalendar
ctl.Value = Date

End Sub

Private Sub cmdSetBeginDate1_Click()
Dim ctl As Control
Set ctl = Me!objCalendar
If Me!txtEndDate1 >= ctl.Value Or IsNull(Me!txtEndDate1) Then
Me!txtBeginDate1 = ctl.Value
If IsDate(Me!txtEndDate1) Then
Me!txtNumDays1 = CDate(Me!txtEndDate1) - CDate(Me!txtBeginDate1)
Else
Me!txtNumDays1 = Null
End If
Else
MsgBox "Beginning Date must be earlier than Ending Date."
End If
End Sub

Private Sub cmdSetEndDate1_Click()
Dim ctl As Control
Set ctl = Me!objCalendar
If Me!txtBeginDate1 <= ctl.Value Or IsNull(Me!txtBeginDate1) Then
Me!txtEndDate1 = ctl.Value
If IsDate(Me!txtBeginDate1) Then
Me!txtNumDays1 = CDate(Me!txtEndDate1) - CDate(Me!txtBeginDate1)
Else
Me!txtNumDays1 = Null
End If
Else
MsgBox "Ending Date must be later than Beginning Date."
End If

End Sub

Private Sub cmdSetBeginDate2_Click()
Dim ctl As Control
Set ctl = Me!objCalendar
If Me!txtEndDate2 >= ctl.Value Or IsNull(Me!txtEndDate2) Then
Me!txtBeginDate2 = ctl.Value
If IsDate(Me!txtEndDate2) Then
Me!txtNumDays2 = CDate(Me!txtEndDate2) - CDate(Me!txtBeginDate2)
Else
Me!txtNumDays2 = Null
End If
Else
MsgBox "Beginning Date must be earlier than Ending Date."
End If
End Sub

Private Sub cmdSetEndDate2_Click()
Dim ctl As Control
Set ctl = Me!objCalendar
If Me!txtBeginDate2 <= ctl.Value Or IsNull(Me!txtBeginDate2) Then
Me!txtEndDate2 = ctl.Value
If IsDate(Me!txtBeginDate2) Then
Me!txtNumDays2 = CDate(Me!txtEndDate2) - CDate(Me!txtBeginDate2)
Else
Me!txtNumDays2 = Null
End If
Else
MsgBox "Ending Date must be later than Beginning Date."
End If

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab query challenge 0
Graph based on Report/Crosstab query 9
Compare and check date 3
#Error in Results 5
Counting Dates between a Start and End, Loop through records, providepeg counts 4
How do you pass parameters between queries in Microso 2
Date query not giving EndDate 6
Custom Function 6

Top