Query or VBA code

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

.

Relevant Pages

  • RE: Query or VBA code
    ... the line correction John suggested and it works beautifully. ... Johns response will give you a report with 2 periods per month, ... Dim myConnection As ADODB.Connection ...
    (microsoft.public.access.queries)
  • Re: Dim oRpt As New CrystalReport1() failing
    ... I do not work with strongly typed reports - I create report files ... Dim paramFields As New ParameterFields ... > Dim paramFields As New ParameterFields ... > reference to "Dim oRpt as New CrystalReport(), ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Dim oRpt As New CrystalReport1() failing
    ... I do not work with strongly typed reports - I create report files ... Dim paramFields As New ParameterFields ... > Dim paramFields As New ParameterFields ... > reference to "Dim oRpt as New CrystalReport(), ...
    (microsoft.public.vb.crystal)
  • pausing and resume
    ... hello i am using this code for generating word report, ... Dim appWord As Word.Application ... Dim rowCount As Long ... 'create a table to display the testscript names and their Outcome ...
    (microsoft.public.word.vba.general)
  • RE: Multiply qty of filtered records
    ... If you should want to try doing this with a query (which is probably the more ... etc in the bottom part of the design view. ... I do know how to get this information in a Report, ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)