Paul,
I apologize for my earlier the logic error. I was at work, and was
writing "untested air code". Try:
PARAMETERS [dtSelected] DateTime;
SELECT Sum(IIf(Day(DateRec)=Day(dtSelected) AND Month(DateRec) =
Month(dtSelected),PalletCount,0)) AS DayPalletCount, _
Sum(IIf(DatePart("ww",DateRec)=DatePart("ww",[dtSelected]),PalletCount,0))
AS WeekPalletCount, _
Sum(IIf(Month(DateRec)=Month([dtSelected]),PalletCount,0))
AS MonthPalletCount
FROM RecyHistory
WHERE Format([DateRec],"yyyy")=Format([dtSelected],"yyyy");
Let me explain what this is doing.
1. The WHERE clause limits the records that are being considered to those
where the Year in your table matches the year in the DateRec control on
your form. Is that control bound to a field in your table or is it
unbound? You might find it good advise to change the name of your
controls from the default (the name of the control source) to a more
friendly reference (if it is a text field, preface it with txt_; if it is
a combo box, use cb or cb; lb for listbox, etc.) this makes it easier to
read your code as you can easily tell whether you are referencing a
control on the form, or a datafield of the form. This WHERE clause could
cause a problem with the WeekPalletCount for that week each year that has
days from December and January. We can come back to this is this is an
issue.
2. The first Sum line sums the PalletCount values from your table where
the month and day of the field (DateRec) is the same as that in your
DateRec control.
3. The second Sum line sums the PalletCount values from your table where
the week of the DateRec field is the same as that of the DateRec control
4. The third Sum line sums the PalletCount for those records that are in
the same month as your DateRec control.
Once this is working, you should be able to refer to these as
rs("DayPalletCount"), rs("WeekPalletCount"), and rs("MonthPalletCount")
HTH
Dale
Paul said:
Hello Dale, I tried to get this to work but had problems with the
multiple sums.
I did get the day total to add up but did not get the week or month to
work. Below is what I currently have.
****Query*SQL VIEW*************************************************
PARAMETERS [dateSelected] DateTime;SELECT RecyHistory.DateRec,
Sum(RecyHistory.PalletCount) AS SumOfPalletCount
FROM RecyHistory
WHERE (((RecyHistory.DateRec)=[DateSelected]))
GROUP BY RecyHistory.DateRec;
**************************************************************************************************************
Private Sub DateRec_GotFocus()
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
If Not IsDate(Me.DateRec) Then
MsgBox " this value needs to be a date"
Me.DateRec.SetFocus
Exit Sub
End If
Set qdf = CurrentDb.QueryDefs("QTY")
qdf.Parameters(0) = CDate(Me.DateRec)
Set rs = qdf.OpenRecordset
If rs.EOF Then
Me.Text16 = 0
Else
Me.Text16 = rs("SumOfPalletCount")
rs.Close
Set rs = Nothing
qdf.Close
Set qdf = Nothing
End If
End Sub
Dale Fye said:
Paul,
Assuming dtSelected is the date selected through your calendar control,
you
need an aggregate query (qry_DayWeekMonthTotals) that looks something
like
the following:
PARAMETERS [dtSelected] DateTime;
SELECT SUM(IIF(Day([Date Received]) = Day([dtSelected]), Qty, 0)) as
DayQty, _
SUM(IIF(Datepart("ww", [Date Received]) = Datepart("ww",
[dtSelected]), Qty, 0) as WeekQty, _
SUM(IIF(Month([Date Received]) = Month([dtSelected]), Qty, 0)
as
MonthQty
FROM yourTable
WHERE Format(DateReceived, "yyyymm") = Format([dtSelected], "yyyymm")
If you create this query and run it you will be prompted for dtSelected
(BTW, you need to create a parameter [dtSelected] and define it as
date/time.
If you don't know how to do this, post back).
Once you have the query working properly, you can implement this in
code.
Since you have indicated you have a calendar control of some sort that
you
are going to use to select the date, I would assume you have some sort
of a
text field with the date. I'll assume that you can type in the textbox,
and
I'll put the code in the AfterUpdate event of that textbox.
Private Sub txt_CalDate_AfterUpdate
Dim qdf as dao.querydef
Dim rs as dao.recordset
if not isdate(me.txt_CalDate) then
msgbox "This value must be a date!"
me.txt_CalDate.Setfocus
exit sub
end if
Set qdf = currentdb.querydefs("qry_DayWeekMonthTotals")
qdf.Parameters(0) = cdate(me.txt_CalDate)
set rs = qdf.openrecordset
if rs.eof then
me.txt_DayQty = 0
me.txt_WeekQty = 0
me.txt_MonthQty = 0
Else
me.txt_DayQty = rs("DayQty")
me.txt_WeekQty = rs("WeekQty")
me.txt_MonthQty = rs("MonthQty")
rs.close
set rs = nothing
qdf.close
set qdf = nothing
End Sub
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
Email address is not valid.
Please reply to newsgroup only.
:
Hello There,
I have a table with "date Recieved" and "QTY" headers.
In the form I would like textboxes to display quantity's for day
totals,
week totals and month totals from a date selected in a calender.
I cannot begin to figure out how to code this correctly but here is the
jist
of what I have.
For each item in [date Recieved]
If item = calender0.value then
daytotal = ?
else if item = date("ww", calender0.value) then
weektotal = ?
else if item = month(calender0.value) then
monthtotal = ?
end if
next item
? = I am also not sure how to get the "QTY" based on it's "date
Recieved" to
add up correctly.
Thanks in advance.
