[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Using Between Clause in ADO parameter query

Posted on 2006-11-15
8
Medium Priority
?
557 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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
 
LVL 58

Accepted Solution

by:
harfang earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

656 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