SQL Query between 2 dates

G

Guest

Hi,

I have a form where a user inputs a "start date" and "end date" (fields are
called date1 and date2). The form is called frm stats closures date.

When they exit the date2 field, it opens another form which has the
following event procedure on current;

Private Sub Form_Current()

Dim db As Database
Dim rst As Recordset
Dim Qdf As QueryDef

Set db = CurrentDb

Set Qdf = db.CreateQueryDef("")
With Qdf
.SQL = "SELECT Count([Tbl FUNDClosures Completed].CompleteDate) AS
CountOfCompleteDate FROM [Tbl FUNDClosures Completed] WHERE ((([Tbl
FUNDClosures Completed].CompleteDate) Between [Forms]![frm stats closures
date]![Date1] And [Forms]![frm stats closures date]![Date2]));"
Set rst = .OpenRecordset()
End With

Me!FundClosed = rst.Fields("CountOfCompleteDate")

End Sub

Now the problem I am having is that when I try to run the code, it says
Run-time error '3061': Too few parameters. Expected 2.

All the code does is find all the records in Tbl FUNDClosures Completed
which have a completed date between the 2 fields above and returns a count.

I have looked through the code and if I paste it into a query, it works fine.

Any suggestions would be greatly apprecuated.

Kind regards,
Maver1ck666
 
A

Allen Browne

Concatenate the dates into the string:

Dim rs AS DAO.Recordset
Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#'
strSql = "SELECT Count([Tbl FUNDClosures Completed].CompleteDate)
AS CountOfCompleteDate FROM [Tbl FUNDClosures Completed]
WHERE [Tbl FUNDClosures Completed].CompleteDate Between " & _
Format([Forms]![frm stats closures date]![Date1], strcJetDate) & _
" And " & Format([Forms]![frm stats closures date]![Date2], strcJetDate) &
";"
Set rst = dbEngine(0)(0).OpenRecordset(strSql)

For an explanation of the jet date formatting, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Note that you could also do this task with DCount() if you wanted to.
 
G

Guest

Thanks for the relpy.

Might sound a bit thick but why would I need to concatenate the dates? The
dates which are being input are in the format of dd/mm/yyyy.

Allen Browne said:
Concatenate the dates into the string:

Dim rs AS DAO.Recordset
Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#'
strSql = "SELECT Count([Tbl FUNDClosures Completed].CompleteDate)
AS CountOfCompleteDate FROM [Tbl FUNDClosures Completed]
WHERE [Tbl FUNDClosures Completed].CompleteDate Between " & _
Format([Forms]![frm stats closures date]![Date1], strcJetDate) & _
" And " & Format([Forms]![frm stats closures date]![Date2], strcJetDate) &
";"
Set rst = dbEngine(0)(0).OpenRecordset(strSql)

For an explanation of the jet date formatting, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Note that you could also do this task with DCount() if you wanted to.

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

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

Maver1ck666 said:
Hi,

I have a form where a user inputs a "start date" and "end date" (fields
are
called date1 and date2). The form is called frm stats closures date.

When they exit the date2 field, it opens another form which has the
following event procedure on current;

Private Sub Form_Current()

Dim db As Database
Dim rst As Recordset
Dim Qdf As QueryDef

Set db = CurrentDb

Set Qdf = db.CreateQueryDef("")
With Qdf
.SQL = "SELECT Count([Tbl FUNDClosures Completed].CompleteDate) AS
CountOfCompleteDate FROM [Tbl FUNDClosures Completed] WHERE ((([Tbl
FUNDClosures Completed].CompleteDate) Between [Forms]![frm stats closures
date]![Date1] And [Forms]![frm stats closures date]![Date2]));"
Set rst = .OpenRecordset()
End With

Me!FundClosed = rst.Fields("CountOfCompleteDate")

End Sub

Now the problem I am having is that when I try to run the code, it says
Run-time error '3061': Too few parameters. Expected 2.

All the code does is find all the records in Tbl FUNDClosures Completed
which have a completed date between the 2 fields above and returns a
count.

I have looked through the code and if I paste it into a query, it works
fine.

Any suggestions would be greatly apprecuated.

Kind regards,
Maver1ck666
Click to expand...
Click to expand...
 
A

Allen Browne

To JET (the query engine in Access), names such as:
[Forms]![frm stats closures date]![Date1]
are parameters.

When you run this as a query, JET calls the Expression Service (ES) which
figures out the values and substitutes them, so the query runs. When you
OpenRecordset in DAO code, the ES is not available to do this for you.
That's why you were told there were 2 parameters you had not sorted out.

The solution is therefore the concatenate the date values into the string,
i.e. the VBA code sorts out what is the value of [Forms]![frm stats closures
date]![Date1], and then passes the literal dates in the string to DAO for
the OpenRecordset.

When you include literal date values in a string, they must be delimited
with #, and they should be formatted in the native JET format (not your user
settings.) The Format() function call performs this operation. The article
(previously linked) explains this.

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

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

Maver1ck666 said:
Thanks for the relpy.

Might sound a bit thick but why would I need to concatenate the dates? The
dates which are being input are in the format of dd/mm/yyyy.

Allen Browne said:
Concatenate the dates into the string:

Dim rs AS DAO.Recordset
Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#'
strSql = "SELECT Count([Tbl FUNDClosures Completed].CompleteDate)
AS CountOfCompleteDate FROM [Tbl FUNDClosures Completed]
WHERE [Tbl FUNDClosures Completed].CompleteDate Between " & _
Format([Forms]![frm stats closures date]![Date1], strcJetDate) & _
" And " & Format([Forms]![frm stats closures date]![Date2], strcJetDate)
&
";"
Set rst = dbEngine(0)(0).OpenRecordset(strSql)

For an explanation of the jet date formatting, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Note that you could also do this task with DCount() if you wanted to.

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

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

Maver1ck666 said:
Hi,

I have a form where a user inputs a "start date" and "end date" (fields
are
called date1 and date2). The form is called frm stats closures date.

When they exit the date2 field, it opens another form which has the
following event procedure on current;

Private Sub Form_Current()

Dim db As Database
Dim rst As Recordset
Dim Qdf As QueryDef

Set db = CurrentDb

Set Qdf = db.CreateQueryDef("")
With Qdf
.SQL = "SELECT Count([Tbl FUNDClosures Completed].CompleteDate)
AS
CountOfCompleteDate FROM [Tbl FUNDClosures Completed] WHERE ((([Tbl
FUNDClosures Completed].CompleteDate) Between [Forms]![frm stats
closures
date]![Date1] And [Forms]![frm stats closures date]![Date2]));"
Set rst = .OpenRecordset()
End With

Me!FundClosed = rst.Fields("CountOfCompleteDate")

End Sub

Now the problem I am having is that when I try to run the code, it says
Run-time error '3061': Too few parameters. Expected 2.

All the code does is find all the records in Tbl FUNDClosures Completed
which have a completed date between the 2 fields above and returns a
count.

I have looked through the code and if I paste it into a query, it works
fine.

Any suggestions would be greatly apprecuated.

Kind regards,
Maver1ck666
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ok, thanks for that.

I have tried your code and now Im getting a type mismatch error :(

The completedate field in the table is set as a short date and so are the 2
fields in the stats form.

Really not sure what I am doing wrong!

Allen Browne said:
To JET (the query engine in Access), names such as:
[Forms]![frm stats closures date]![Date1]
are parameters.

When you run this as a query, JET calls the Expression Service (ES) which
figures out the values and substitutes them, so the query runs. When you
OpenRecordset in DAO code, the ES is not available to do this for you.
That's why you were told there were 2 parameters you had not sorted out.

The solution is therefore the concatenate the date values into the string,
i.e. the VBA code sorts out what is the value of [Forms]![frm stats closures
date]![Date1], and then passes the literal dates in the string to DAO for
the OpenRecordset.

When you include literal date values in a string, they must be delimited
with #, and they should be formatted in the native JET format (not your user
settings.) The Format() function call performs this operation. The article
(previously linked) explains this.

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

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

Maver1ck666 said:
Thanks for the relpy.

Might sound a bit thick but why would I need to concatenate the dates? The
dates which are being input are in the format of dd/mm/yyyy.

Allen Browne said:
Concatenate the dates into the string:

Dim rs AS DAO.Recordset
Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#'
strSql = "SELECT Count([Tbl FUNDClosures Completed].CompleteDate)
AS CountOfCompleteDate FROM [Tbl FUNDClosures Completed]
WHERE [Tbl FUNDClosures Completed].CompleteDate Between " & _
Format([Forms]![frm stats closures date]![Date1], strcJetDate) & _
" And " & Format([Forms]![frm stats closures date]![Date2], strcJetDate)
&
";"
Set rst = dbEngine(0)(0).OpenRecordset(strSql)

For an explanation of the jet date formatting, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Note that you could also do this task with DCount() if you wanted to.

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

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

Hi,

I have a form where a user inputs a "start date" and "end date" (fields
are
called date1 and date2). The form is called frm stats closures date.

When they exit the date2 field, it opens another form which has the
following event procedure on current;

Private Sub Form_Current()

Dim db As Database
Dim rst As Recordset
Dim Qdf As QueryDef

Set db = CurrentDb

Set Qdf = db.CreateQueryDef("")
With Qdf
.SQL = "SELECT Count([Tbl FUNDClosures Completed].CompleteDate)
AS
CountOfCompleteDate FROM [Tbl FUNDClosures Completed] WHERE ((([Tbl
FUNDClosures Completed].CompleteDate) Between [Forms]![frm stats
closures
date]![Date1] And [Forms]![frm stats closures date]![Date2]));"
Set rst = .OpenRecordset()
End With

Me!FundClosed = rst.Fields("CountOfCompleteDate")

End Sub

Now the problem I am having is that when I try to run the code, it says
Run-time error '3061': Too few parameters. Expected 2.

All the code does is find all the records in Tbl FUNDClosures Completed
which have a completed date between the 2 fields above and returns a
count.

I have looked through the code and if I paste it into a query, it works
fine.

Any suggestions would be greatly apprecuated.

Kind regards,
Maver1ck666
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Did you start with the declaration:
Dim rs AS DAO.Recordset

The ADO library also has a Recordset object. If you have referenced both
libraries (or only the ADO library), you can get a type declaration error.

It could also error if you have a text field instead of a date field. The
format of the field is not relevant to the code (due to the way Access
stores dates internally.)

If that's not the issue, simplify the code, and then start building up until
you identify the culprit.

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

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

Maver1ck666 said:
Ok, thanks for that.

I have tried your code and now Im getting a type mismatch error :(

The completedate field in the table is set as a short date and so are the
2
fields in the stats form.

Really not sure what I am doing wrong!

Allen Browne said:
To JET (the query engine in Access), names such as:
[Forms]![frm stats closures date]![Date1]
are parameters.

When you run this as a query, JET calls the Expression Service (ES) which
figures out the values and substitutes them, so the query runs. When you
OpenRecordset in DAO code, the ES is not available to do this for you.
That's why you were told there were 2 parameters you had not sorted out.

The solution is therefore the concatenate the date values into the
string,
i.e. the VBA code sorts out what is the value of [Forms]![frm stats
closures
date]![Date1], and then passes the literal dates in the string to DAO for
the OpenRecordset.

When you include literal date values in a string, they must be delimited
with #, and they should be formatted in the native JET format (not your
user
settings.) The Format() function call performs this operation. The
article
(previously linked) explains this.

Maver1ck666 said:
Thanks for the relpy.

Might sound a bit thick but why would I need to concatenate the dates?
The
dates which are being input are in the format of dd/mm/yyyy.

:

Concatenate the dates into the string:

Dim rs AS DAO.Recordset
Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#'
strSql = "SELECT Count([Tbl FUNDClosures Completed].CompleteDate)
AS CountOfCompleteDate FROM [Tbl FUNDClosures Completed]
WHERE [Tbl FUNDClosures Completed].CompleteDate Between " & _
Format([Forms]![frm stats closures date]![Date1], strcJetDate) & _
" And " & Format([Forms]![frm stats closures date]![Date2],
strcJetDate)
&
";"
Set rst = dbEngine(0)(0).OpenRecordset(strSql)

For an explanation of the jet date formatting, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Note that you could also do this task with DCount() if you wanted to.

Hi,

I have a form where a user inputs a "start date" and "end date"
(fields
are
called date1 and date2). The form is called frm stats closures date.

When they exit the date2 field, it opens another form which has the
following event procedure on current;

Private Sub Form_Current()

Dim db As Database
Dim rst As Recordset
Dim Qdf As QueryDef

Set db = CurrentDb

Set Qdf = db.CreateQueryDef("")
With Qdf
.SQL = "SELECT Count([Tbl FUNDClosures
Completed].CompleteDate)
AS
CountOfCompleteDate FROM [Tbl FUNDClosures Completed] WHERE ((([Tbl
FUNDClosures Completed].CompleteDate) Between [Forms]![frm stats
closures
date]![Date1] And [Forms]![frm stats closures date]![Date2]));"
Set rst = .OpenRecordset()
End With

Me!FundClosed = rst.Fields("CountOfCompleteDate")

End Sub

Now the problem I am having is that when I try to run the code, it
says
Run-time error '3061': Too few parameters. Expected 2.

All the code does is find all the records in Tbl FUNDClosures
Completed
which have a completed date between the 2 fields above and returns a
count.

I have looked through the code and if I paste it into a query, it
works
fine.

Any suggestions would be greatly apprecuated.

Kind regards,
Maver1ck666
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Function with two recordsets 6
Excel Formula to Access Query - Time Between 2 Dates in Weeks 2
querying a query 1
Determine a recordcount from a query 2
Send an Query output with a Email using "sendObject" function 1
Query produces no records 1
VBA SQL statement help 5
Query By Form 2

Top