Solved

Using Between Clause in ADO parameter query

Posted on 2006-11-15
8
526 Views
Last Modified: 2008-01-09
I need to output a query to an Excel sheet.  This query as several calculated fields and is outputed to an Excel templat, where the data populates specific fields.  That part works fine.  However, now the client want to be able to specify a date range (based on disposition date) using a start and end date.  I've created a dialog form that allows users to enter a start and end dates.  However I need to be able to pass this data a parameters to the output query.     I'm not entired sure how to pass  the parameters with a Between clause to the query.  I would appreciate any help.

Dim strSql As String
        Dim strPath As String
        Dim rst As ADODB.Recordset
        Dim cmd As ADODB.Command
        Dim wb As New Excel.Application
        Dim prmStartDate As ADODB.Parameter
        Dim prmEndDate As ADODB.Parameter

        'Instantiate recordset and command objects
        Set rst = New ADODB.Recordset
        Set cmd = New ADODB.Command

        'Sets the cmd CommandType property to utilize queries via the adCmdStoredProc
        'and set the connection to the current connection.  cmd.CommandText is set to the name
        'of the query that will be used to create the recordset
        cmd.CommandType = adCmdStoredProc
        cmd.ActiveConnection = CurrentProject.Connection
        cmd.CommandText = "qryPSoftExport"
       

   'Creates the actual recordset based on "qryPSoftExport that will be used to populate
        'the EVMCR.xls worksheet.
        Set rst = cmd.Execute(, Array(Forms!frmDatePSoftDialog!txtStartDate.Value), adCmdStoredProc)
       
 


  'The following code loads the excel sheet    
       
        strPath = "\\datapfsv06\tcs02\TCS Finance & Admin\FPO\Supplier Information\EVMCRs\EVMCRtest.xls"
           
       'Opens the EVMCR worksheet at the location specified by the variable strPath
        wb.Workbooks.Open FileName:=strPath
        wb.Visible = True
   
        'Copies the contents of the recordset rs and populates the spreadsheet “EVMCR.xls”
        wb.Sheets("EVMCR").Range("A22").CopyFromRecordset rst
   
        'Saves the newly populated workbook and destroys all object variables
        'by setting them equal to “Nothing”
             
     
    'Kill wb.DefaultFilePath & Application.Path
    Kill wb.DefaultFilePath & wb.Application.PathSeparator & "RESUME.XLW"

        wb.Save
        wb.Workbooks.Close
        Set wb = Nothing
        Set rst = Nothing
        If MsgBox("Do you wish to view the exported results", vbYesNo, "View Exported Results") = vbYes Then
            GetExcel
        Else
            DoCmd.Echo True, ""
            DoCmd.SetWarnings True
            Exit Sub
        End If
    Else
        MsgBox "Spreadsheet creation has been canceled"
    End If

    DoCmd.Echo True, ""
    DoCmd.SetWarnings True
0
Comment
Question by:chtullu135
  • 4
  • 4
8 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 17952590
Hello chtullu135

Your code seems fine as such, but does not really relate to your question, which seems to be about SQL more than about VBA...

Let's imagine that your query looks like this:

    PARAMETERS dtParamFrom DateTime, dtParamTo DateTime;
    SELECT * FROM SomeTable WHERE dtTheDate Between dtParamFrom And dtParamTo;

If you run this query, it will prompt you for dtParamFrom and dtParamTo, in that order, and use them as criteria. These are the parameters. If I want to pass the dates 1st of April and 30th of April, I would use this line of code (from your sample)

    Set rst = cmd.Execute(, Array(#1 april#, #30 april#), adCmdStoredProc)

As I see it, your code currently passes only one parameter. Simply add the second to the query, and use the parameter names you have created in your query in a Between clause.

Hope this helps!

(°v°)
0
 

Author Comment

by:chtullu135
ID: 17957344
Hello,

I modified my code, as you suggested and the resulting spreadsheet is still displaying all the records.  I decided to divide and test each portion of my code.  I decided to test what records were being returned by rst.  I discovered that testing for rst.eof showed that no records were being returned and this was confirmed by the results of the rst loop which did not print any records.  Could it be that I am using a select clause on a query as the command text?

 Set rst = cmd.Execute(, Array(dtStartDate, dtStartDate), adCmdText)
        'Set rst = cmd.Execute(Parameters:=Array(dtStartDate, dtEndDate))
        If rst.EOF Then
            MsgBox "No records"
        End If
       
        Do Until rst.EOF
            Debug.Print rst(0)
            rst.MoveNext
        Loop


 Dim strSql As String
        Dim strPath As String
        Dim rst As New ADODB.Recordset
        Dim cmd As New ADODB.Command
        Dim wb As New Excel.Application
        Dim prmStartDate As New ADODB.Parameter
        Dim prmEndDate As New ADODB.Parameter
        Dim dtStartDate As Variant
        Dim dtEndDate As Variant

        'Instantiate recordset and command objects
       

        'Sets the cmd CommandType property to utilize queries via the adCmdStoredProc
        'and set the connection to the current connection.  cmd.CommandText is set to the name
        'of the query that will be used to create the recordset
       
        dtStartDate = Forms!frmDatePSoftDialog!txtStartDate
        dtEndDate = Forms!frmDatePSoftDialog!txtEndDate
       
       
        cmd.ActiveConnection = CurrentProject.Connection
        cmd.CommandText = "Select * FROM qryPSoftExport Where [Disposition Date] Between ? And ?"
        cmd.CommandType = adCmdText
       
        prmStartDate.Type = adDate
        cmd.Parameters.Append prmStartDate
       
        prmEndDate.Type = adDate
        cmd.Parameters.Append prmEndDate
       
        cmd.Parameters(0) = dtStartDate
        cmd.Parameters(1) = dtEndDate
                 

        'Creates the actual recordset based on "qryPSoftExport that will be used to populate
        'the EVMCR.xls worksheet.
               
        Set rst = cmd.Execute(, Array(dtStartDate, dtStartDate), adCmdText)
        'Set rst = cmd.Execute(Parameters:=Array(dtStartDate, dtEndDate))
        If rst.EOF Then
            MsgBox "No records"
        End If
       
        Do Until rst.EOF
            Debug.Print rst(0)
            rst.MoveNext
        Loop
       
0
 
LVL 58

Expert Comment

by:harfang
ID: 17957706
chtullu135,

When you post long extracts of code like this, I get confused and can't quite know of which part you are talking about.

At first, you test a query (can't tell which one) with two parameters (also not explained) and discover that you have no records... What can I say?

Elsewhere on the code, you have an incomplete query with question marks. You probably want:

cmd.CommandText = "Select * FROM qryPSoftExport Where [Disposition Date] Between Param1 And Param2"

The names you use as parameters are of little importance, as long as they don't conflict with SQL reserved words. In the command object, you will pass them in sequence as an array, that's all.

I'll make up a full working sample later tonight.

Cheers!
(°v°)
0
 

Author Comment

by:chtullu135
ID: 17958153
My apologies for the confusion.

Here is a breakdown of the code

      I created two date variables "dtStartDate" and "dtEndDate" to hold the values of the user inputed start and End dates

       dtStartDate = Forms!frmDatePSoftDialog!txtStartDate
       dtEndDate = Forms!frmDatePSoftDialog!txtEndDate


Next I set up my command object.  I used the question marks in the command text as place holders for the parameters and set the command type to adCmdText
       
        cmd.ActiveConnection = CurrentProject.Connection
        cmd.CommandText = "Select * From qryPSoftExport Where [Disposition Date] Between ? and ?"
        cmd.CommandType = adCmdText

I then set the parameter datatype of two parameter variables (prmStartDate, and prmEndDate) to adDate.  After setting the datatype of each variable, I used the append method to append each variable to the parameters collection of the command object "cmd"

        prmEndDateprmStartDate.Type = adDate
        cmd.Parameters.Append prmStartDate
       
        prmEndDate.Type = adDate
        cmd.Parameters.Append prmEndDate

I then passed the previously created inputed date variables "dtStartDate" amd "dtEndDate" as arguments to the Array function.  I set the rst object variable by running the Execute method of the previously instantiated command object "cmd"

      Set rst = cmd.Execute(, Array(dtStartDate, dtEndDate), adCmdText)

As a check to determine what if any records were indeed contained in the recordset "rst",  I used an if statement, usind the EOF method of the rst object to determine if any records where in the recordset rst
        If rst.EOF Then
             MsgBox "No records"
        End If

The following loop was used in a previous troubleshooting attempt, to determine the contents of the recordset.  When I ran the code, the results of the debug.print command indicated that there were no records in the recordset rst.  I then used the preceeding if statement to verify that the recordset rst was indeed empty.

        Do Until rst.EOF
            Debug.Print rst(0)
            rst.MoveNext
        Loop




       




0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 58

Accepted Solution

by:
harfang earned 500 total points
ID: 17959307
chtullu135,

I understand a little better now. You are mixing two methods to manage parameters (look up help on the .Parameters collection of the command object). Either you use the collection, or you provide parameters through the .Execute method. What you are doing, in fact, is adding new parameters to your query, not defining those you need.

The following works, provided you have a copy of the Northwind Orders table in your database:

Sub TestParam()

    Dim strSql As String
    Dim rst As ADODB.Recordset
   
    strSql = "PARAMETERS foo date, bar date;" _
        & "Select * from orders where orderdate between foo and bar"
   
    With New ADODB.Command
        .ActiveConnection = CurrentProject.Connection
        .CommandText = strSql
        Set rst = .Execute(Parameters:=Array(#4/1/1997#, #4/15/1997#))
    End With
   
    Do Until rst.EOF
        Debug.Print rst!OrderDate
        rst.MoveNext
    Loop

End Sub

As an alternative, you can also examine the automatically populated .Parameters collection, and assign values for each. For example, the central lines could read:

    With New ADODB.Command
        .ActiveConnection = CurrentProject.Connection
        .CommandText = strSql
        .Parameters.Refresh   ' not really needed for Access tables
        .Parameters(0) = #4/1/1997#
        .Parameters(1) = #4/15/1997#
        Set rst = .Execute
    End With

With the exact same result.

I hope this helps you to sort it out.

(°v°)
0
 

Author Comment

by:chtullu135
ID: 17960655
Hello,

Thanks for the info.  I was mixing two methods  I chose the following method and I am able to output the specified date range.  In addition, it appears that I set up my strSql string incorrectly.  I should have specified the parameters before the select statement.  I'm now able to return the correct records.  
 
strSql = "PARAMETERS startdate date, enddate date;" & "Select * From qryPSoftExport Where [Disposition Date] Between startdate and enddate"
       
cmd.ActiveConnection = CurrentProject.Connection
        cmd.CommandText = strSql
        Set rst = cmd.Execute(Parameters:=Array(#11/2/2006#, #11/20/2006#))
   
   
    Do Until rst.EOF
        Debug.Print rst("Disposition Date")
        rst.MoveNext
    Loop

However, when I replace the dates in the Array with variables dtStartDate amd dtEndDate which are populated from the date input form, no records are returned.
As you can see in the partial code snippet, I've replaced the hard-coded date arguments with variables.          

         Dim dtStartDate as Date
        Dim dtEndDate as Date
       
        dtStartDate = me.txtStartDate      'Inputed via date input form
        dtEndDate = me.txtEndDate

        Set rst = cmd.Execute(Parameters:=Array(dtEndDate, dtStartDate))

I tested the values of the parameters via the following debug.print statements to verify that the values were being passed to the array.  The proper values were printed out to the debug window, indicating that the array was storing the correct values.  Perhaps they are not being stored as dates?

Debug.Print cmd.Parameters(0)
    Debug.Print cmd.Parameters(1)
   
   
   
0
 

Author Comment

by:chtullu135
ID: 17960718
Hello,

I found the problem.  I forgot to use the format function on each of the date variables.  As soon as I did, the array interpreted the date variables the way I wanted it to

Set rst = cmd.Execute(Parameters:=Array(Format(dtStartDate, "Short Date"), Format(dtEndDate, "Short Date")))

Thanks again for all your help.
0
 
LVL 58

Expert Comment

by:harfang
ID: 17960848
Welcome! And I'm glad it now works. I must admit I would never have formatted the dates in the array, this seems too risky (what if my computer is set to european date format -- it is! -- and they are evaluated as US dates, as SQL does...), so I might not have found that last problem...
Anyway, good luck with your project!
(°v°)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now