Re: SQL Query between 2 dates

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Maver1ck666" <Maver1ck666@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B036F441-5952-4269-83EA-7A9B134595A0@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Booking System - Vacant room report
    ... in what format? ... within the date range as a field in the recordset, ... Dim colDates As Collection ... Set rst = CurrentDb.OpenRecordset ...
    (comp.databases.ms-access)
  • Re: SQL Query between 2 dates
    ... The solution is therefore the concatenate the date values into the string, ... The Format() function call performs this operation. ... Allen Browne - Microsoft MVP. ... Dim rs AS DAO.Recordset ...
    (microsoft.public.access.queries)
  • Re: Custom ID Field
    ... field JobNumber. ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset ... the ID field in the table is set to AutoNumber and the Format ...
    (microsoft.public.access.formscoding)
  • Re: Custom ID Field
    ... current database, open the Recordset, find the number, etc. ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset ... Each year the format has to be ...
    (microsoft.public.access.formscoding)
  • Re: Custom ID Field
    ... field JobNumber. ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset ... the ID field in the table is set to AutoNumber and the Format ...
    (microsoft.public.access.formscoding)