Please Help... Problem with Object Reference in Recordset???

[Guest]

Hi,

I am trying to count the rows in a table where the date (Update_Archive) is
between two user-input dates on my form, frmWork_Load. However, I get an
error saying that "No Value given for one or more required parameters."

Public Function Calculate_Workload() As String
If ([Forms]![frmWork_Load]![Second_Date]) >
[Forms]![frmWork_Load]![First_Date]
Then DateMessage() As String
Else

'Establish connection to ActiveX Data Objects
Dim trs As ADODB.Connection
Set trs = CurrentProject.Connection

'Declare Recordset
Dim allRecords As New ADODB.Recordset
allRecords.ActiveConnection = trs

'SQL statement to populate Recordset
Dim SQL As String
SQL = "SELECT Count([ARCHIVE].[Invoice_Number]) FROM Archive"
SQL = SQL + " WHERE (ARCHIVE.Update_Archive) BETWEEN "
SQL = SQL + "[Forms]![frmWork_Load]![First_Date]"
SQL = SQL + " AND "
SQL = SQL + "([Forms]![frmWork_Load]![Second_Date])"

'Run SQL Select statement
allRecords.Open SQL

'Retrieve the value from the upper-left most of the Recordset
Forms!frmWork_Load!Result = (allRecords.Fields(0).Value)

'Close Recordset and terminate connection
allRecords.Close
Set allRecords = Nothing
Set trs = Nothing

End If

End Function

 

[Tim Ferguson]

SQL = "SELECT Count([ARCHIVE].[Invoice_Number]) FROM Archive"
SQL = SQL + " WHERE (ARCHIVE.Update_Archive) BETWEEN "
SQL = SQL + "[Forms]![frmWork_Load]![First_Date]"
SQL = SQL + " AND "
SQL = SQL + "([Forms]![frmWork_Load]![Second_Date])"

As Klatuu says, the + concatenation can cause problems, but I doubt it's
your problem here.

First of all, sending user input straight into a SQL query is asking for
trouble: particularly for dates you really _must_ look after the
formatting _explicitly_.

Second, the BETWEEN operator is designed for integer ranges not real
numbers, and it will give unexpected results (read: errors) if there are
any time values in your data (can you swear you've never used Now()
instead of Date()??).

Thirdly, but less important, is that you can simplify the query by
getting rid of all the [bracket] garbage. Try this:

' this needs error trapping because it will fail if one of the
' text boxes is not a legal date
dtOne = CDate([Forms]![frmWork_Load]![First_Date])
dtTwo = CDate([Forms]![frmWork_Load]![Second_Date])

' now you can do the SQL
' remember that Update_Archive can be from 00:00 on day one
' up to (but not including) midnight on day two
'
strSQL = _
"SELECT COUNT(Invoice_Number) AS NumRecs " & vbNewLine & _
"FROM Archive " & vbNewLine & _
"WHERE " & Format(dtOne, "\#yyyy\-mm\-dd\#") & _
" <= Update_Archive " & vbNewLine & _
" AND UpdateArchive < " & Format(dtTwo+1, "\#yyyy\-mm\-dd\#")

' comment this out once you know it's working
Debug.Assert vbYes=MsgBox(strSQL, vbYesNo, "Is this OK?")

' and then call the command
rsCountRecords.Open strSQL

Hope that helps


Tim F

 

[Guest]

It works. Thank you both sooo much! I have been really frustrated
with this.

Tim, could you explain:
A. why we have to Format it, after you have already declared dtOne and dtTwo
being equal to CDate(Forms!frmWork_Load!blah_blah_blah)
B. what the significance is of adding one to dtTwo, "Format(dtTwo+1"
C. what the significance/importance is of the order YYYY/MM/DD
D. why month and day are proceeded by a minus sign

Again, I can't emphasize how much I appreciate your help.

Thanks!

 

[Tim Ferguson]

A. why we have to Format it, after you have already declared dtOne and
dtTwo being equal to CDate(Forms!frmWork_Load!blah_blah_blah)

because Jet only recognises a very small number of date formats, which
are not regionally-aware, and the default coercion by VBA will not
neccessarily give what is wanted. For example, in my country

strSQL = "WHERE MyDate = #" & dtMyBirthday & "#"

gives "WHERE MyDate = #11/02/2005#" which Jet will see as sometime in
November -- wrong! It is only safe to use an explicit Jet-compatible
format, in practice either #yyyy-mm-dd# or #mm/dd/yyyy# Anything else
_will_ come back and bite you in the end.

B. what the significance is of adding one to dtTwo, "Format(dtTwo+1"

You need all Time values up to (but not including) 00:00 the following
day. Note that #2005-05-20 09:15# comes _after_ #2005-05-20#

C. what the significance/importance is of the order YYYY/MM/DD

See above. See help files on Date Literals in SQL.

D. why month and day are proceeded by a minus sign

See above. Standard ISO format. I used to have a URL for the ISO
definition: how sad is that??

All the best


Tim F

 

[Guest]

Thanks so much for taking the time to explain all of that. I really
appreciate it.

 

[Tim Ferguson]

Thanks so much for taking the time to explain all of that. I really
appreciate it.

You're welcome.

Tim F