INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Dates and ADODB.Command

Dates and ADODB.Command

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

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

CODE

    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.

CODE

    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

RE: Dates and ADODB.Command

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?

RE: Dates and ADODB.Command

(OP)
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

RE: Dates and ADODB.Command

(OP)
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

RE: Dates and ADODB.Command

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.


RE: Dates and ADODB.Command

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close