RE: Query or VBA code
- From: Dale Fye <dale.fye@xxxxxxxxxx>
- Date: Sat, 3 May 2008 10:12:01 -0700
Monique,
From your original post, and the responses, it is not entirely clear whetheryou 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.
"Monique" wrote:
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
- Follow-Ups:
- RE: Query or VBA code
- From: Monique
- RE: Query or VBA code
- References:
- Query or VBA code
- From: Monique
- Query or VBA code
- Prev by Date: Re: Transactions and concurrency
- Next by Date: Re: ranking data and updating a table
- Previous by thread: Re: Query or VBA code
- Next by thread: RE: Query or VBA code
- Index(es):
Relevant Pages
|
