"Too few parameters. Expected..." - Parameter Query Issue

[TazAdams]

Hi All,

I am having an issue with a parameter query, let me try and explain...

I have a table (tblsitelogs) which contains records with dates on. I want to be able to query between 2 dates (from a form which the user specifies), and then export the data from the query, to an excel spreadsheet.

I browsed earlier and found this code to help me export the query to excel: http://www.databasejournal.com/features/msaccess/article.php/3563671. I am experienced in VB (although reletively new to Access), so I have adopted the code and tested it, and it works fine when run by itself.

Here is the query (qryReport)..

   "SELECT tblSiteLog.ExchangeCode, tblSiteLog.ExchangeName, tblJobDetails.Phase, tblSiteLog.JobType, tblSiteLog.JobItem, tblSiteLog.Engineer, tblSiteLog.LogEntryDate, tblSiteLog.Result, tblSiteLog.EntryDetails, tblSiteLog.EnteredBy" & _
        " FROM tblJobDetails INNER JOIN tblSiteLog ON tblJobDetails.JobID = tblSiteLog.JobID" & _
        " WHERE (((tblSiteLog.LogEntryDate) Between " & txtStartDate & " And " & txtEndDate & "));"

When I then run this code

   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("qryReport", dbOpenSnapshot)

I get an error on the bottom line, I get the error:

"Too few parameters. Expected 2".

I have researched and this appears to be an almost normal issue because of the query being a parameter-based query.

I've tried typing in the query in SQL, into VB and then running that instead, but it doesn't seem to filter, it will just display all results.

Feel free to ask more questions into this, I am probably only touching the surface of the problem here. Any help greatly appreciated!

Thanks,

Taz

 

[Alc]

If you put a message box after having constucted the SQL but before running and exporting it, does everything look exactly how it should?

 

[TazAdams]

Yes Alc, and it shows 100% correctly.

If I run the query manually then it runs fine too.

 

[Jon K]

If on a system using US date format e.g. m/d/yyyy, try delimiting the dates in the text boxes with the # signs:-

" WHERE tblSiteLog.LogEntryDate Between #" & txtStartDate & "# And #" & txtEndDate & "#;"


If on a system using non-US date format such as d/m/yyyy, try using the DateValue() function:-

" WHERE tblSiteLog.LogEntryDate Between DateValue('" & txtStartDate & "') And DateValue('" & txtEndDate & "')

The DateValue() function should also work on systems using US date formats.
.

 

[TazAdams]

Thanks for the ideas Jon.

I have tried using # to define dates beforehand and that didn't work, have just tried the DateValue() and that hasn't worked either!

The code I got was:

   sSQL = "SELECT tblSiteLog.ExchangeCode, tblSiteLog.ExchangeName, tblJobDetails.Phase, tblSiteLog.JobType, tblSiteLog.JobItem, tblSiteLog.Engineer, tblSiteLog.LogEntryDate, tblSiteLog.Result, tblSiteLog.EntryDetails, tblSiteLog.EnteredBy" & _
        " FROM tblJobDetails INNER JOIN tblSiteLog ON tblJobDetails.JobID = tblSiteLog.JobID" & _
        " WHERE (((tblSiteLog.LogEntryDate) Between DateValue(" & txtStartDate & ") And DateValue(" & txtEndDate & ")));"

And I'm getting the error:

"Data Type mismatch in criteria expression."

 

[EMP]

You have left out the single-quotes in the DateValue()

^

 

[TazAdams]

Thanks for all the help guys.

I've now sorted it with your help. It turned out that the formats were being interpritted as US dates instead of UK. I think I may have made a few changes code-wise to it to, anywho, anyone who has this problem, here is my fix:

      SplitStartDate = Split(txtStartDate, "/")
   SplitEndDate = Split(txtEndDate, "/")
   
   StartDate = SplitStartDate(1) & "/" & SplitStartDate(0) & "/" & SplitStartDate(2)
   EndDate = SplitEndDate(1) & "/" & SplitEndDate(0) & "/" & SplitEndDate(2)


sSQL = "SELECT tblSiteLog.ExchangeCode, tblSiteLog.ExchangeName, tblJobDetails.Phase, tblSiteLog.JobType, tblSiteLog.JobItem, tblSiteLog.Engineer, tblSiteLog.LogEntryDate, tblSiteLog.Result, tblSiteLog.EntryDetails, tblSiteLog.EnteredBy" & _
        " FROM tblJobDetails INNER JOIN tblSiteLog ON tblJobDetails.JobID = tblSiteLog.JobID" & _
        " WHERE (((tblSiteLog.LogEntryDate) Between #" & StartDate & "# And #" & EndDate & "#)) ORDER BY tblSiteLog.LogEntryDate DESC;"


   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)

Thanks again!

Taz

 

[slaga9]

help!!similair problem...less brains.

Taz! I have been surfing the net for a week trying to find someone having the same issue as me. (I get the same error messages)

however, I know next to nothing about Access so I was hoping you might be able to give me some assistance as I think there may be more wrong with what im doing then the "expecting more parameters"

I am generating a parameter query on a form and exporting it too an excell document i have set up.

currently I have the form w/ combo boxes and a comand button to run the query.
I was then hoping to put in a second comand button that would export the query results. (i have put this together with the code found below, but it is not working)

First off... is this how you did yours? If not, is there a more effective way?

Secondly... if you have time I would really appreciate it if you could take a look at my code (see below) and put me in the right direction.

thanks alot
Sean



Public Function ExportRequest() As String
On Error GoTo err_Handler

' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTabTwo As Byte = 1
Const cStartRow As Byte = 11
Const cStartColumn As Byte = 1

DoCmd.Hourglass True

' set to break on all errors
Application.SetOption "Error Trapping", 0

' start with a clean file built from the template file
' sTemplate = CurrentProject.Path & "\salary recovery template.xls"
sOutput = CurrentProject.Path & "\salary recovery template.xls"
' If Dir(sOutput) <> "" Then Kill sOutput
' FileCopy sTemplate, sOutput

' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabTwo)
sSQL = "SELECT [Employee_ID].[Recovery No], [Employee_ID].[Account Name], [Employee_ID].[Gen Ledg Acnt No],"
sSQL = sSQL & "[Timesheettable1].[JobNumbers], [Employee_ID].[Type], [Employee_ID].[unknown],"
sSQL = sSQL & " [Employee_ID].[Ref No], [Timesheettable1].[Employee], "
sSQL = sSQL & "[Employee_ID].[Rate], [Timesheettable1].[Hours Worked], [Timesheettable1].[Hours Paid]"
sSQL = sSQL & " FROM [Job Number] INNER JOIN (Employee_ID INNER JOIN Timesheettable1 ON [Employee_ID].[Employee]=[Timesheettable1].[Employee]) ON [Job Number].[Job Number]=[Timesheettable1].[JobNumbers]"
sSQL = sSQL & " WHERE (([Employee_ID].[Recovery No]) Like '*' & [Forms]![srchfrm]![recovnumber] & '*') And ([Timesheettable1].[JobNumbers]) Like '*' & [Forms]![srchfrm]![jobnumbersrchfrm] & '*' And ([Timesheettable1].[Employee]) Like '*' & [Forms]![srchfrm]![employee] & '*' And ([Timesheettable1].[PayPeriodEnd]) Like '*' & [Forms]![srchfrm]![payday] & '*' And ([Job Number].[Job Site]) Like '*' & [Forms]![srchfrm]![site] & '*';"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("paraquery", dbOpenSnapshot)





' For this template, the data must be placed on the 11th row, first column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow


Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
' Me.lblMsg.Caption = "Exporting record #" & lRecords & " to salary recovery template.xls"
' Me.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop

ExportRequest = "Total of " & lRecords & " rows processed."
' Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."

exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

err_Handler:
ExportRequest = Err.Description
' Me.lblMsg.Caption = Err.Description
Resume exit_Here

End Function

Private Sub exportcmd_Click()
On Error GoTo err_Handler

MsgBox ExportRequest, vbInformation, "Finished"
Application.FollowHyperlink CurrentProject.Path & "\salary recovery template.xls"

exit_Here:
Exit Sub
err_Handler:
MsgBox Err.Description, vbCritical, "Error"
Resume exit_Here
End Sub