QueryDef Date Between problem

[Darrenc]

Hi, I'm having a problem with querydef!

I have looked at tried all sorts of different solutions and now i need your help.

I'm trying to export a query into excel, and this query has a parameter that looks at 2 date fields on a form.

Between Forms!frmSwictboard!frmDatabaseReports!txtStartDate and Forms!frmSwictboard!frmDatabaseReports!txtEndDate

I cannot for the life of me pass through this parameter using QueryDef.

This is the code i'm using to create the excel report.

Dim qdf As QueryDef
    Dim db As DAO.Database
    Dim rs As DAO.Recordset


        Set db = CurrentDb
        Set qdf = db.QueryDefs("qryrptAllOverdueIncidents")
        qdf.Parameters("IncDateRaised") = "Between Forms!frmSwictboard!frmDatabaseReports!txtStartDate And Forms!frmSwictboard!frmDatabaseReports!txtEndDate"
        Set rs = qdf.OpenRecordset
        
        'Start a new workbook in Excel
        Dim oApp As New Excel.Application
        Dim oBook As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        
        Set oBook = oApp.Workbooks.Add
        Set oSheet = oBook.Worksheets(1)
        
        'Add the field names in row 1
        Dim i As Integer
        Dim iNumCols As Integer
        iNumCols = rs.Fields.Count
            For i = 1 To iNumCols
            oSheet.Cells(1, i).Value = rs.Fields(i - 1).name
        Next
        
        'Add the data starting at cell A2
        oSheet.Range("A2").CopyFromRecordset rs
        
        'Format the header row as bold and autofit the columns
        With oSheet.Range("a1").Resize(1, iNumCols)
            .Font.Bold = True
            .EntireColumn.AutoFit
        End With
        
        oApp.Visible = True
        oApp.UserControl = True
        
    'Close the Database and Recordset
    rs.Close
    db.Close

Is it even possible to pass through a parameter like this using querydef?

Can anyone point out any obvious flaw!

Thanks.

 

[Alisa]

Maybe you need # around the dates . . . like #07/27/09#?

 

[pbaldy]

Does this help?

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

 

[jjturner]

You're trying to do too much. Your Parameter can only hold values, it can't perform operations like a WHERE clause, which is how you've set it up.

Break up your expression into 2 Parameter values first:

qdf.[Forms!frmSwictboard!frmDatabaseReports!txtStartDate] =
 [Forms]![frmSwictboard]![frmDatabaseReports]![txtStartDate]
qdf.[Forms!frmSwictboard!frmDatabaseReports!txtEndDate] =
 [Forms]![frmSwictboard]![frmDatabaseReports]![txtEndDate]

Then as long as your saved query has those form control references embedded in your "Between ... And ... " criteria expression, your querydef should be able to supply the values appropriately.

HTH,
John

 

[Galaxiom]

You are misunderstanding the use of parameters and treating them as a Condition or Where Clause. Think back to how they work in stored queries. Parameters are simply undefined terms which prompt for values. The only difference in the querydef situation is you must set them before you run the query.

The basic querydef statement needs to include a condition clause in this style:
BETWEEN SomeDate AND AnotherDate

The parameters are fed to the querydef like this:

qdf.Parameters("SomeDate") = Forms!frmSwictboard!frmDatabaseReports!txtStartDate
qdf.Parameters("AnotherDate") = Forms!frmSwictboard!frmDatabaseReports!txtEndDate

 

[Galaxiom]

Break up your expression into 2 Parameter values first:

qdf.[Forms!frmSwictboard!frmDatabaseReports!txtStartDate] =
 [Forms]![frmSwictboard]![frmDatabaseReports]![txtStartDate]
qdf.[Forms!frmSwictboard!frmDatabaseReports!txtEndDate] =
 [Forms]![frmSwictboard]![frmDatabaseReports]![txtEndDate]

I know this is what Microsoft shows as an example but it sucks because it obfuscates the whole point.
There is no good reason to ever call a parameter:
[Forms!frmSwictboard!frmDatabaseReports!txtStartDate]
(note the square brackets around the whole term to prevent it being interpreted as the actual textbox itself)
If the parameter was always going to be this textbox then you might as well forget the parameter and just refer directly to the control in the query.

The whole point of parameters in querydefs is to allow a variable source to be fed into a basic query serving multiple purposes. Name the parameters something simple like FirstDate and LastDate. It will be more far more readable both in the query and the VB code.

 

[Darrenc]

Thank you all for your responses!
I think I finally get querydef. I’ve always strugged to get my head around it!
I made the changes you suggested Galaxiom and it worked instantly!

Thanks again!
This forum has come to me rescue yet again!