Query or VBA code

[Monique]

I have a table of orders called ‘tblDuesod’. I want to calculate the total
cost (EuroSodCost) of the orders which have a delivery date (DelivDate)
between 2 specific dates, then plug plug the result in the control of a
report. I want to do this for each half-month period thru the year.
I made individual queries but cannot figure out how to plug all the results
in a report.
I tried a single query with IIf() and got nowhere.
I turned to code. Here it is but it’s not working. It give me a '€0' in
the report control. I know the connection is working as it finds out that
the field is in euros. Can you help ?

Dim JuneOne As Currency

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim recordset2 As New ADODB.Recordset
recordset2.ActiveConnection = myConnection
recordset2.CursorType = adOpenStatic

recordset2.Open ("SELECT sum(EuroSodCost) AS [JuneOne] FROM tblDuesSod WHERE
(DelivDate) between #6/01/2008# and #6/16/2008#;")

Me![txtJune1] = JuneOne

recordset2.Close

 

[Evi]

You could group your report for 6 month periods in the Sorting/Grouping
box.in the report's design view

Group on your date field, choose Yes for Group Footer, Next to Group On
choose Month.
Next to Group Interval choose 6.


and put your
=Sum([EuroSodCost]) in the Group Footer

Or if you want to filter your query to show only a 6month period then you
could use the filter

Between #YourDate # AND DateSerial(Year(YourDate),
Month(YourDate)-6,Day(YourDate))

eg
Between #20/04/2008# And
DateSerial(Year(#20/04/2008#),Month(#20/04/2008#)-6,Day(#20/04/2008#))

and put your sums in the report footer
Evi

 

[John Spencer]

Since I don't know the underlying query for the report, all I can
suggest is this query that will return the bi-monthly sums. You should
be able to adapt this to your report.

SELECT Format(DelivDate,"yyyy-mm") as YrMonth
, IIF(Day(DelivDate)<16,1,2) as MonthSection
, Sum(EuroSodCost) as PeriodSum
FROM tblDuesSod
WHERE DelivDate Between #1/1/2008# and #6/30/2008#
GROUP BY Format(DelivDate,"yyyy-mm")
, IIF(Day(DelivDate)<16,1,2)


If I were doing it the way you are trying I would try changing the
assign line to read
Me![txtJune1] = Recordset2!JuneOne



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Dale Fye]

Monique,

From your original post, and the responses, it is not entirely clear whether
you are generating bi-weekly or bi-monthly queries. However you are doing
it, it appears that you have a bunch of these queries and are trying to
kludge them together. I would recommend against this, and recommend a
technique similar to what John recommended. A single query to determine the
values for all of the periods in question. Then, use the where clause to
determine which weekly periods actually end up in your report.

Johns response will give you a report with 2 periods per month, but will not
give you records that are in 2 week intervals, if that is what you want. If
that is what you want, then you could use a formula something like:

(Datepart("ww", [DelivDate])-1)\2

to group by. The down side of this is that the first period of the year
would almost never have 14 days in it, unless the year started on a Sunday.

Another way to group this report would be to do something like:

(datediff("d", Dateserial(year(date), 1, 1), [DelivDate])-1)\14

This would give you 2 week periods starting on Jan 1 or any particular year,
but would result in a leftover day (or 2 on leap year) at the end of the
year; so you would have to figure out how to handle that.

A third way to approach this would be to create a table that defines the
dates (this could be complete dates, or could just be the month and day) that
are in each 2 week period. You could include a yes/no field in this table,
to identify which periods you would want to include in the report.

The stucture of this table might be tbl_Periods:

PeriodID, IsSelected, Start_Month, Start_Day, End_Month, End_Day
1 -1 1 1 1
14
2 -1 1 15 1
28
3 -1 1 29 2
11

and your SQL string might look like:

strSQL = "SELECT PeriodID, " _
& "sum(EuroSodCost) AS SumEuroSodCost " _
& "FROM tblDuesSod, tbl_Period as P" _
& "WHERE [DelivDate] >= " _
& "Dateserial(Year(date), P.Start_Month, P.Start_Day) " _
& "AND [DelivDate] <= "_
& "Dateserial(Year(date), P.End_Month, P.End_Day) " _
& "AND P.IsSelected = -1"

Then, you could determine which periods to include by setting the IsSelected
field to true or false.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a table of orders called ‘tblDuesod’. I want to calculate the total
cost (EuroSodCost) of the orders which have a delivery date (DelivDate)
between 2 specific dates, then plug plug the result in the control of a
report. I want to do this for each half-month period thru the year.
I made individual queries but cannot figure out how to plug all the results
in a report.
I tried a single query with IIf() and got nowhere.
I turned to code. Here it is but it’s not working. It give me a '€0' in
the report control. I know the connection is working as it finds out that
the field is in euros. Can you help ?

Dim JuneOne As Currency

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim recordset2 As New ADODB.Recordset
recordset2.ActiveConnection = myConnection
recordset2.CursorType = adOpenStatic

recordset2.Open ("SELECT sum(EuroSodCost) AS [JuneOne] FROM tblDuesSod WHERE
(DelivDate) between #6/01/2008# and #6/16/2008#;")

Me![txtJune1] = JuneOne

recordset2.Close

 

[Monique]

Thank you all, this was very helpful. I finally chose to keep my code with
the line correction John suggested and it works beautifully. But I learned a
lot from your very comprehensive suggestions.

Monique,

From your original post, and the responses, it is not entirely clear whether
you are generating bi-weekly or bi-monthly queries. However you are doing
it, it appears that you have a bunch of these queries and are trying to
kludge them together. I would recommend against this, and recommend a
technique similar to what John recommended. A single query to determine the
values for all of the periods in question. Then, use the where clause to
determine which weekly periods actually end up in your report.

Johns response will give you a report with 2 periods per month, but will not
give you records that are in 2 week intervals, if that is what you want. If
that is what you want, then you could use a formula something like:

(Datepart("ww", [DelivDate])-1)\2

to group by. The down side of this is that the first period of the year
would almost never have 14 days in it, unless the year started on a Sunday.

Another way to group this report would be to do something like:

(datediff("d", Dateserial(year(date), 1, 1), [DelivDate])-1)\14

This would give you 2 week periods starting on Jan 1 or any particular year,
but would result in a leftover day (or 2 on leap year) at the end of the
year; so you would have to figure out how to handle that.

A third way to approach this would be to create a table that defines the
dates (this could be complete dates, or could just be the month and day) that
are in each 2 week period. You could include a yes/no field in this table,
to identify which periods you would want to include in the report.

The stucture of this table might be tbl_Periods:

PeriodID, IsSelected, Start_Month, Start_Day, End_Month, End_Day
1 -1 1 1 1
14
2 -1 1 15 1
28
3 -1 1 29 2
11

and your SQL string might look like:

strSQL = "SELECT PeriodID, " _
& "sum(EuroSodCost) AS SumEuroSodCost " _
& "FROM tblDuesSod, tbl_Period as P" _
& "WHERE [DelivDate] >= " _
& "Dateserial(Year(date), P.Start_Month, P.Start_Day) " _
& "AND [DelivDate] <= "_
& "Dateserial(Year(date), P.End_Month, P.End_Day) " _
& "AND P.IsSelected = -1"

Then, you could determine which periods to include by setting the IsSelected
field to true or false.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a table of orders called ‘tblDuesod’. I want to calculate the total
cost (EuroSodCost) of the orders which have a delivery date (DelivDate)
between 2 specific dates, then plug plug the result in the control of a
report. I want to do this for each half-month period thru the year.
I made individual queries but cannot figure out how to plug all the results
in a report.
I tried a single query with IIf() and got nowhere.
I turned to code. Here it is but it’s not working. It give me a '€0' in
the report control. I know the connection is working as it finds out that
the field is in euros. Can you help ?

Dim JuneOne As Currency

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim recordset2 As New ADODB.Recordset
recordset2.ActiveConnection = myConnection
recordset2.CursorType = adOpenStatic

recordset2.Open ("SELECT sum(EuroSodCost) AS [JuneOne] FROM tblDuesSod WHERE
(DelivDate) between #6/01/2008# and #6/16/2008#;")

Me![txtJune1] = JuneOne

recordset2.Close