Too few parameters Expected 1 error 1

[varadha72]

Hi ,

can Someone help me understand this query. while I am trying to execute this query in the VB6 Code in Access, I am getting the following error- Too Few Parameters. Expected 1. Please help.

RunSQL = "select * from (dbo_SOURCE_RUN_DATA) where rundate between (#" & start_dt & "#) and (#" & end_dt & "#)

 

[varadha72]

My Apologies the Query is;

RunSQL = "SELECT DISTINCT (T.File_Name), M.FileNameForForm, T.row_count, T.exception_count, Min(T.run_Date) AS rundate, M.sortorder FROM dbo_SOURCE_RUN_DATA AS T, Master AS M, dbo_SOURCE_RUN_DATA AS A WHERE '(((T.file_name) Like '*' & M.filename & '*') And ((A.FILE_NAME)=T.FILE_NAME) And ((A.ROW_COUNT)=T.ROW_COUNT) And ((A.EXCEPTION_COUNT)=T.EXCEPTION_COUNT) And rundate between #" & start_dt & "# and #" & end_dt & "# And ((T.result)=0) And M.SortOrder=1)' GROUP BY T.row_count, T.exception_count, M.sortorder, T.File_name, M.FileNameForForm ORDER BY M.sortorder, M.FileNameForForm"

Also I tied to break this query into multiple lines but got error so I put in one line, if someone can show me the right way to break this query into multiple lines that will be great as well.

 

[varadha72]

I tried this query also, but I getting the error "Too Few Parameters. Expected 1. Error. Please help.

RunSQL = "SELECT DISTINCT (T.File_Name), M.FileNameForForm, T.row_count, T.exception_count," & _
"Min(T.run_Date) AS rundate, M.sortorder " & _
"FROM dbo_SOURCE_RUN_DATA AS T, Master AS M, dbo_SOURCE_RUN_DATA AS A " & _
"WHERE '(((T.file_name) Like '*' & M.filename & '*') " & _
"And ((A.FILE_NAME)=T.FILE_NAME) And ((A.ROW_COUNT)=T.ROW_COUNT) " & _
"And ((A.EXCEPTION_COUNT)=T.EXCEPTION_COUNT) " & _
"And rundate between #" & start_dt & "# and #" & end_dt & "# " & _
"And ((T.result)=0) And M.SortOrder=1)' " & _
"GROUP BY T.row_count, T.exception_count, M.sortorder, T.File_name, M.FileNameForForm " & _
"ORDER BY M.sortorder, M.FileNameForForm

 

[dhookom]

There was an extra apostrophe following "WHERE". Another extra apostrophe following "And M.SortOrder=1". Also, the rundate can't be resolved so part of the WHERE clause might have to move to the HAVING clause.

Try:

RunSQL = "SELECT DISTINCT (T.File_Name), M.FileNameForForm, T.row_count, T.exception_count, " & _
 "Min(T.run_Date) AS rundate, M.sortorder " & _
 "FROM dbo_SOURCE_RUN_DATA AS T, Master AS M, dbo_SOURCE_RUN_DATA AS A " & _
 "WHERE T.file_name Like '*' & M.filename & '*' " & _
 "And A.FILE_NAME=T.FILE_NAME And A.ROW_COUNT=T.ROW_COUNT " & _
 "And A.EXCEPTION_COUNT=T.EXCEPTION_COUNT " & _
 "And T.result=0 And M.SortOrder=1  " & _
 "GROUP BY T.row_count, T.exception_count, M.sortorder, T.File_name, M.FileNameForForm " & _
 "HAVING  Min(T.run_Date) between #" & start_dt & "# and #" & end_dt & "# " & _
 "ORDER BY M.sortorder, M.FileNameForForm"

Use debug.Print to find out the value of RunSQL so you can paste it into a query SQL view.

Duane
Hook'D on Access
MS Access MVP

 

[varadha72]

Hi dhookom thanks for your response. I tried my code with your suggestion, but I still getting the same error- Too few Parameters.

The query result in the debug window is as below-
SELECT DISTINCT (T.File_Name), M.FileNameForForm, T.row_count, T.exception_count, Min(T.run_Date) AS rundate, M.sortorder FROM dbo_SOURCE_RUN_DATA AS T, Master AS M, dbo_SOURCE_RUN_DATA AS A WHERE T.file_name Like '*' & M.filename & '*' And A.FILE_NAME=T.FILE_NAME And A.ROW_COUNT=T.ROW_COUNT And A.EXCEPTION_COUNT=T.EXCEPTION_COUNT And T.result=0 And M.SortOrder=1 GROUP BY T.row_count, T.exception_count, M.sortorder, T.File_name, M.FileNameForForm HAVING Min(T.run_Date) between #12/11/2014# and #12/12/2014# ORDER BY M.sortorder, M.FileNameForForm

 

[varadha72]

Also when I executed the same query in Access, the query is taking a long time to execute. When I remove the having clause and place it in Where clause, the query execution is quick. Please suggest if I could use the parameters in Where clause instead of Having clause.

 

[dhookom]

What happens when you paste the SQL into a blank query?

Your choice of the HAVING or WHERE clause depends on whether you want to filter on the detailed records or on an aggregate. The HAVING acts on aggregate values such as Min(...). If you want to filter against run_Date then use run_Date between #12/11/2014# and #12/12/2014# in your WHERE clause.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

too few parameters" often suggest you have a spelling error of a field or table name.

Duane
Hook'D on Access
MS Access MVP