CODE not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to count records in a query using two dates entered by the user
and put it inot a variable called 'intWLDThMonth' . It returns the wrong
count and I can;t work out why.

strSQL = "PARAMETERS [StartDate] Date, [EndDate] Date; "
strSQL = strSQL & "SELECT * From qryJoinThisMonth_old WHERE "
strSQL = strSQL & "DateJoined >=[StartDate] AND DateJoined <= [EndDate];"
Set qdf = CurrentDb.CreateQueryDef("", strSQL)
qdf.Parameters("StartDate") = varStartDate
qdf.Parameters("EndDate") = varEndDate
Set rstContact = qdf.OpenRecordset

If Not rstContact.EOF Then
rstContact.MoveFirst
rstContact.MoveLast
intWLDThMonth = rstContact.RecordCount
End If
Set rstContact = Nothing

Any help much appreciated. Paul Mendlesohn
 
That looks as if you are doing it the hard way. You could probably use the
DCount function to do this.

intWLDThMonth = DCount("*","qryJoinThisMonth","DateJoined >= #" & varStartDate
"# AND DateJoined <=#" & varEndDate "#")

But, since the query is returning the "wrong" count, I would look at the query
itself and see what is happening. What is wrong with the count? Is it too
small, always zero, too large, a random value that doesn't make sense, negative?

Is this an .adp (Access Project) or an .mdb (Access Database) or are you linking
to some tables using odbc? What version of Access?

If you generate the query as a query and run it, does it give you the correct results?
 
I would use a DCount("intWldThMonth", "qryJointhisMonth_old", "StartDate
= " & varStartDate & " AND EndDate = " & varEndDate")

And YES you can use ALL Domain functions (DLookup(), DCount(), etc.) on
a query.
 
"StartDate = " & varStartDate & " AND EndDate = " & varEndDate"
Click to expand...

No: this method of passing date literals to Jet will not work. You need an
ISO or a USA format and # delimeters. Check help file.

HTH


Tim F
 
Back
Top