too few parameters expected n

[desprate]

this is my case: i have a query and a form and a VBA code that excutes when a
button is clicked on the form and exports the data from the query into a text
file.
the problem is that on the form i have to input a start and end date so that
the query results will be limited by the period given.
the question is: how do i tell my vba code the dates that i have entered
through the form so that it will add them to the sql query in the "where"
clause?
this is the part of my code that is causing trouble:

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("myquery") <-----this is where it's going wrong
and asking for the parameters.
i use the recordset to hold the results of the query while exporting to the
file.
Hope you can help me because it's really important
Thank you

 

[Allen Browne]

You need to programmatically supply the parameters for the query before you
OpenRecordset.

This kind of thing:
Dim qdf As QueryDef
Set qdf = db.QueryDefs("Myquery")
qdf.Parameters("StartDate") = #1/1/2004#
qdf.OpenRecordset...

 

[Allen Browne]

Use a form, or an InputBox() to get the date for your code.

If you have a form where the date is entered, you can:
qdf.Parameters("StartDate") = Forms!Form1!txtStartDate

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

well, as i said, i get this date from the form(ie the user enters this
date,
so it's changed everytime).
this is what i have in my "where" clause :
WHERE (((Format([Check]![TxnDate],"yyyymmdd")) Between [Enter Start Date:
Format YYYYMMDD] And [Enter End Date: Format YYYYMMDD]));
so i need somethihng in my code that will correspond to this value entered
by the user.

Thank you very much

You need to programmatically supply the parameters for the query before
you
OpenRecordset.

This kind of thing:
Dim qdf As QueryDef
Set qdf = db.QueryDefs("Myquery")
qdf.Parameters("StartDate") = #1/1/2004#
qdf.OpenRecordset...

 

[desprate]

Thank you for helping me,i do have a form that includes two text boxes and a
button. the text boxes are used to enter the dates and when the button is
clicked it runs the vba code and there starts my problem.
i added QDef.Parameters("FromDate") = Forms!IntroForm!FromDate
to my code and now i get the error message item not found in this collection
what do i have to do now?

Use a form, or an InputBox() to get the date for your code.

If you have a form where the date is entered, you can:
qdf.Parameters("StartDate") = Forms!Form1!txtStartDate

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

well, as i said, i get this date from the form(ie the user enters this
date,
so it's changed everytime).
this is what i have in my "where" clause :
WHERE (((Format([Check]![TxnDate],"yyyymmdd")) Between [Enter Start Date:
Format YYYYMMDD] And [Enter End Date: Format YYYYMMDD]));
so i need somethihng in my code that will correspond to this value entered
by the user.

Thank you very much

You need to programmatically supply the parameters for the query before
you
OpenRecordset.

This kind of thing:
Dim qdf As QueryDef
Set qdf = db.QueryDefs("Myquery")
qdf.Parameters("StartDate") = #1/1/2004#
qdf.OpenRecordset...


this is my case: i have a query and a form and a VBA code that excutes
when a
button is clicked on the form and exports the data from the query into
a
text
file.
the problem is that on the form i have to input a start and end date so
that
the query results will be limited by the period given.
the question is: how do i tell my vba code the dates that i have
entered
through the form so that it will add them to the sql query in the
"where"
clause?
this is the part of my code that is causing trouble:

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("myquery") <-----this is where it's going
wrong
and asking for the parameters.
i use the recordset to hold the results of the query while exporting to
the
file.
Hope you can help me because it's really important
Thank you

 

[Allen Browne]

If the Criteria row of your query contains:
[Forms]![IntroForm]![FromDate]
then that is the parameter name that Access needs supplied.
Use:
qdf.Parameters("[Forms]![IntroForm]![FromDate]") =
Forms!IntroForm!FromDate

BTW, it also helps to declare these parameters in the query.
In query design view, choose Parameters on the Query menu.
Access opens a dialog.
Enter:
[Forms]![IntroForm]![FromDate] Date/Time

Also, if the FromDate text box is unbound, set its Format property to Short
Date or similar so Access won't accept an invalid date. Your code might also
check that the text box is not null and contains a valid date with:
If IsDate(Forms!IntroForm!FromDate) Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for helping me,i do have a form that includes two text boxes and
a
button. the text boxes are used to enter the dates and when the button is
clicked it runs the vba code and there starts my problem.
i added QDef.Parameters("FromDate") = Forms!IntroForm!FromDate
to my code and now i get the error message item not found in this
collection
what do i have to do now?

Use a form, or an InputBox() to get the date for your code.

If you have a form where the date is entered, you can:
qdf.Parameters("StartDate") = Forms!Form1!txtStartDate

well, as i said, i get this date from the form(ie the user enters this
date,
so it's changed everytime).
this is what i have in my "where" clause :
WHERE (((Format([Check]![TxnDate],"yyyymmdd")) Between [Enter Start
Date:
Format YYYYMMDD] And [Enter End Date: Format YYYYMMDD]));
so i need somethihng in my code that will correspond to this value
entered
by the user.

Thank you very much

:

You need to programmatically supply the parameters for the query
before
you
OpenRecordset.

This kind of thing:
Dim qdf As QueryDef
Set qdf = db.QueryDefs("Myquery")
qdf.Parameters("StartDate") = #1/1/2004#
qdf.OpenRecordset...


this is my case: i have a query and a form and a VBA code that
excutes
when a
button is clicked on the form and exports the data from the query
into
a
text
file.
the problem is that on the form i have to input a start and end date
so
that
the query results will be limited by the period given.
the question is: how do i tell my vba code the dates that i have
entered
through the form so that it will add them to the sql query in the
"where"
clause?
this is the part of my code that is causing trouble:

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("myquery") <-----this is where it's going
wrong
and asking for the parameters.
i use the recordset to hold the results of the query while exporting
to
the
file.
Hope you can help me because it's really important
Thank you