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

Hi,

   You did not mention the controls you are using for the start and stop dates. I will assume that you are using data pickers called dteStart and dteEnd:

'=====

Dim dteStart As String
Dim dteEnd As String

...

dteStart = Me.dteStart
dteEnd = Me.dteEnd

...

stSQL = "SELECT * FROM ... WHERE [DateField] BETWEEN " & "#" & dteStart & "#" & " AND " & "#" & dteEnd & "#"

'=====

Anyway, you get the idea. The posters here should be able to correct my syntax.

HTH

mmcdonal

In your query, for the date field you are checking, reference the form fields using:

[Forms]![yourformname]![textfieldcontrolname]

E.g. If you are reporting on a single date from your table (call it "SampleToDate") and your form name is "SampleReportStart" and your from date is "txtFromDate" and your to date is "txtToDate" specify this criteria for the SampleToDate in your "myquery":

Between [Forms]![SampleReportStart]![txtFromDate] and [Forms]![SampleReportStart]![txtToDate]

Be careful of the "Between" or for that matter be careful of using "<=" and ">=". If any of the values in SampleToDate include time, records will not be selected. That happens because "txtToDate" will be interpretted as "mm/dd/yyyy 00:00:00 am".

There are several ways around this:
+ Have your users specify the time (or add the time for the users)

+ Format the date field of the table in your query:

Format([SampleToDate],"mm/dd/yyyy") as CheckDate

+ alter the criteria to:

Between [Forms]![TextOutput]![txtFromDate] and (CDate([Forms]![SampleReportStart]![txtToDate]) + 1)

Note: the last solution will pick up SampleToDate values for one day greater than the user specified if the values in SampleToDate are date only.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

i'm using text boxes for the dates. in my code i don't have an stSQL that you suggested in your reply. my sql is in the query which i only reference in the code by db.OpenRecordSet("myQuery")
i altered my where clause so that it looks like this
WHERE (((Format([Check]![TxnDate],"yyyymmdd")) Between
 " & "#" & StartDate & "#" And " & "#" & EndDate & "#" ));

but i got "syntax error missing operator in query expression
so i changed it to

WHERE (((Format([Check]![TxnDate],"yyyymmdd")) Between
"#" & StartDate & "#" And "#" & EndDate & "#" ));

and i'm back to the originol error message "too few parameters...."
any ideas how to fix this?

I do. Read my post!

Just change "StartDate" and "EndDate" as I described.

Be careful of your Format to "yyyymmdd". If the users don't enter that format, your WHERE won't work. Access figures out that you're working with dates.

And don't forget about that TIME issue. TxnDate implies there might be a time in the value.

so you suggest that i add nothing to my VBA code and just change my where clause in the sql of the query.
this is what i did. my sql looks like this now
WHERE (((Format([Check]![TxnDate],"yyyymmdd")) Between [Forms]![IntroForm]![FromDate] And [Forms]![IntroForm]![ToDate]));
but it still doesn't work and i get the same error message....
it looks like i'm missing something here

(Please don't quote entire messages anymore. Thx.)

Two possible problems.

One, your form is not open in View mode when you're running the query. If the form isn't open [Forms]!... can't access the information in the form.

Two, your query doesn't work to begin with. Try making sure the query works without criteria. Is [Check] then name of a table in your query? Is [TxnDate] the name of a field in the [Check] table? Are you properly joining [Check] to other tables you're viewing?

Perhaps you should try posting the the SQL statement from the query so we can make sure the whole syntax is correct.