RecordSet is not Retrieving Data if i use Date period in my statment

[noorani]

Hi,
My problem is related with the RecordSet.
i want to retrieve data from my table for specified period using some parameters, like

1) stock number
2) product number,
3) and between some user define period.

At the starting part of my code every veriable is defined.

R - is a Recordset
start_date and end_date - is a Define by User

--here is the small part of my code where i have problem, i only have problem with this statment when i try to use the Date field from my table and want to retrieve records between specified period. the format of date field is set to a short date in both table and form.

here is the statements,
1)
---------------
Set R = dbs.OpenRecordset("select * from jrn where (stock = 6)and (productcode = 214)and
(jrdate > Me!start_date) and (jrdate < Me!Me!end_date)", dbOpenDynaset)
-----------------

i try to do it in another way,

2)
-------------
Set R = dbs.OpenRecordset("select * from jrn where (stock = 6)and (productcode = 214)and
(jrdate) Between Me!start_date and Me!end_date", dbOpenDynaset)
--------------

in both the statments i got the error saying like
(Run Time Error 3061
Too few parameters required 2).

Please guide me where i'm doing wrong, thanks in advance.

 

[RoyVidar]

First - In your statement the name of the controls are passed, not the values.

Second - qualifiers, hash (#) for datas, single quote (') for text, none for numbers

Third - depending on regional settings, you might have to format the date so Jet understands.

Parts of the Where clause might then look something like this:

[tt]...Between #" & format$(Me!start_date, "yyyy-mm-dd") & "# and #" & format$(Me!end_date,"yyyy-mm-dd") & "#"[/tt]

- also take a look at missing spaces in the rest of the statement, some of those might provide errors too.

Tip - use a debug.print on the sql statement, hit CTRL+G, copy the statement to the SQL-view of the QBE - and it should run.

Roy-Vidar

 

[randy700]

Try this...

Set R = dbs.OpenRecordset("Select * From jrn Where stock = 6 and productcode = 214 and jrdate between #" & me!start_date & "# and #" & me!end_date & "#", dbOpenDynaset)

 

[noorani]

Thanks Roy Vidar it's really worked out.