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!txtStartDat e 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!txtStartDat e 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.

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

Does this help?

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

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!txtStartDat e] =
[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

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!txtStartDat e
qdf.Parameters("AnotherDate") = Forms!frmSwictboard!frmDatabaseReports!txtEndDate

Break up your expression into 2 Parameter values first:
qdf.[Forms!frmSwictboard!frmDatabaseReports!txtStartDat e] =
[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!txtStartDat e]
(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.

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! :D