Dates and ADODB.Command

[dpaulson]

I have a problem that I can't seem to correct. I have the following code

Dim cmdGSTSalesAmount As ADODB.command
Dim prm As ADODB.Parameter
Dim rsGSTAmount as ADODB.recordset
Dim StartDate As Date
Dim EndDate As Date

and then

    Set cmdGSTSalesAmount = New ADODB.command
    cmdGSTSalesAmount.ActiveConnection = cnAccounting
    cmdGSTSalesAmount.CommandText = "SELECT sum(TotalGSTSales) as MonthTotalGSTSales,sum(TotalNonGSTSales) as MonthTotalNonGSTSales, sum(GSTAmount) as MonthGSTTotal, sum(DebitTotal) as MonthInvoiceAmount FROM Invoices WHERE Printed = True AND  DebitTransType = 'I' AND InvoiceDate BETWEEN ? AND ?"
    cmdGSTSalesAmount.CommandType = adCmdText
    Set prm = cmdGSTSalesAmount.CreateParameter("Start", adDate, adParamInput)
    cmdGSTSalesAmount.Parameters.Append prm
    Set prm = cmdGSTSalesAmount.CreateParameter("End", adDate, adParamInput)
    cmdGSTSalesAmount.Parameters.Append prm

and farther down
Startdate is set to a value.Debug.print Startdate is 01/04/2007 in dd/mm/yy format
EndDate is set to a value. Debug.print Enddate is 30/06/2007 in dd/mm/yy format. This code follows.

    cmdGSTSalesAmount.Parameters("Start").Value = StartDate
    cmdGSTSalesAmount.Parameters("End").Value = EndDate
    Set rsGSTAmount = cmdGSTSalesAmount.Execute

The first time the .execute statements is issued, it returns the right values. The second time it is issued, it seems to think then start date is mm/dd/yy format and returns the wrong values. However, if I have 2 'Set rsGSTAmount = cmdGSTSalesAmount.Execute' statements in a row, it returns the right values each time.

This is used with an access database and InvoiceDate is a DateTime value.


David Paulson

 

[SBerthold]

I don't know what you mean by "second time". Are you calling the complete proceedure a second time?

What happens if you hard-code in the dates and call it twice?

 

[dpaulson]

What I meant is when the part that sets the parameters "Start" and "end" are set with a new value and a new recordset is retrieved. If I hard-code the value just above where I set the parameter values with startdate = dateserial(2007,4,1) and enddate = dateserial(2007,6,30), It gets the the correct values upon initial run, but when the dates are reset, in this case back to the same dates, the recordset returns the values from Jan 4th 2007 to Jun 30 2007.

David Paulson

 

[dpaulson]

Another interesting thing is when I hard-code enddate = #30-06-2007# (obviusly in format dd/mm/yy) the ide resets this value to #6/30/2007# which is a mm/dd/yy format. The database when looked at with visdata shows that the datetime is stored in a dd/mm/yy format.

David Paulson

 

[SBerthold]

What the database shows as a date format doesn't matter in this case (MS ACCESS).

The vb IDE uses only US date format for hard-coded dates - this is correct.

Code the date explicitly, at each point where the param is being set, in the following manner:

= CDate("30-06-2007")

Also, close the recordset prior to re-using it.

 

[dpaulson]

I finally figured it out.
The problem comes from having more than one command. When only one command is used, the correct dates are used on each time the command is executed. When more then one command is executed is when this problem occurs. I have successfully corrected this with the 'cmdCommandName.prepared = true' statement. I'm not exactly sure what this statement really does, but it is important to have.

David Paulson