Too Few Parameters

[CCIDBMNG]

I have a form which has a text box prompting for a date. I use that date as criteria in a query. That works fine. But when I try and reference the query using DAO -
Set rsReportq1 = CurrentDb.OpenRecordset("Orders Reportq1")
it gives me the too few parameters error. Does anyone have any idea why. If I put a date in the criteria instead of referencing the text box that works fine.

Thanks in advance

 

[pdx_man]

You are not able to pass the parameter to the query when you are trying to open the recordset. You may want your criteria to reference a form that has the value. That way, the query can supply the value for the criteria. Don't expect to be prompted for the parameter when you are using VBA code. It won't.

Another option is to run the SQL in the code with the criteria:

Criteria_Str = InputBox("Prompt","Titlebar")
SQL_Str = "Select Table1.Field1, Table1.Field2 FROM Table1 WHERE Table1.Field1 = " & Criteria_Str
Set rsReportq1 = CurrentDb.OpenRecordset(SQL_Str)

You may have to adjust the WHERE portion depending if the criteria is a date, string or numeric data. Also, a side note, try to avoid using spaces in your table and field names. Use an underscore instread. This will save hours of agonization trying to track down errors, trust me. If you insist on using them, you may have to use brackets [] to denote one field. Otherwise, Access may think you are talking about 2 or more fields and then can't find them ...

 

[CCIDBMNG]

You suggested using the input box but I have the text box on a seperate form cause I use the criteria in multiple places. I don' t want to have to ask for the date more then once. Is there a way at all I can specify the text box as criteria for the query I am opening via DAO?

 

[pdx_man]

Then go with my first suggestion.
Make sure the form that has the text box is open and the value is not null. Instead of closing the form, make the form not visible.

me.visible = false

Then you will be able to access the text box on the form for later use.

 

[CCIDBMNG]

The form is open. I have it open at all times because of there are different things that use it at different times. So it's alwasy there.

 

[pdx_man]

Have you tried writing out the SQL statement like I had in the second suggestion?:

Criteria_Str = [Forms]![Frm_Form1]![tb_Crit]
SQL_Str = "Select Table1.Field1, Table1.Field2 FROM Table1 WHERE Table1.Field1 = " & Criteria_Str
Set rsReportq1 = CurrentDb.OpenRecordset(SQL_Str)

 

[CCIDBMNG]

I tried that and it says there are no records there but when I place the code in a query under the sql view it works fine, it shows there are records in there. Here is my code
Criteria_Str = [Forms]![EnterPayDate]![txtPayDate]
sql_str = "Select * FROM [Orders Report] WHERE [Orders Report].[Pay Date] = " & Criteria_Str & " ORDER BY [Orders Report].Product, [Orders Report].SSN, [Orders Report].[Level Seq];"

Set rsReportq1 = CurrentDb.OpenRecordset(sql_str)

Any ideas as to what is wrong

 

[pdx_man]

Because you are referencing a date, you must enclose the criteria with '#'.

sql_str = "Select * FROM [Orders Report] WHERE [Orders Report].[Pay Date] = #" & Criteria_Str & "# ORDER BY [Orders Report].Product, [Orders Report].SSN, [Orders Report].[Level Seq];"

 

[CCIDBMNG]

That worked great thanks soooo much