RunTime Error when Exporting Query

[Debbie Hatten]

HELP!!! I am getting a run time error 3021 - no current
record when I try to export query results to an Excel
file. I do NOT get the RTE when I view the query in
Access. The query contains a function that I designed
based on KB article #210138 to compute a moving average.
It seems to be hanging up on the rst.movelast line
Here's my code:

Function GMovAvg(Property, startDate, period As Integer)

Dim rst As DAO.Recordset
Dim sql As String
Dim ma As Currency
Dim n

sql = "Select * from calculations "
sql = sql & "where property = '" & Property & "'"
sql = sql & " and date < #" & startDate & "#"
sql = sql & " order by date"

Set rst = CurrentDb.OpenRecordset(sql)
rst.MoveLast
For n = 0 To period - 1
If rst.BOF Then
GMovAvg = 0
Exit Function
Else
ma = ma + rst.Fields("gross sales gas")
End If
rst.MovePrevious
Next n
rst.Close
GMovAvg = ma / period

 

[Nikos Yannacopoulos]

Debbie,

You get this error message because your sql statement returns no records,
most likely on account of a problem around your date filter. To begin with,
startDate is not assigned a value in your code. Is it a global variable? If
yes, and provided it is Date/Time type, you need to remove the hashes from
around it in your code:
....
sql = sql & " and date <" & startDate
....

If, on the other hand, it is a text variable, then you must keep the hashes,
but bear in mind that to VB all dates are US format (month/day/year), so if
the string in your variable is non-US (day/month/year or year/month/day or
whatever) it won't work. One workaround is to use a combination of Left /
Mid Right / Val functions within a DateSerial function so as to convert the
string to a proper date, and then use that in the sql statement (without the
hashes).

If startDate is not a global variable then it is not assigned a value (it's
null) at the time you conctruct the sql statement, so no records are
returned.

HTH.
Nikos