Simple Recorset Question

[Guest]

Hi

I'm having a problem with the following code in a module (see below)
This code seems fine as per the examples I saw on internet, but I get an error sayin
that I am missing some parameters in the OpenRecordSet method. I thought the other parameters were optional
I am only trying to reach a value that is in a table in my database, if you have better ways to do it, it would be great

Please help!!

Jenn

Private Function getLastImportDate(poDB As DAO.Database) As Dat

Dim tpDate As Dat
Dim sSQL As Strin
Dim tempSet As DAO.Recordse
Set poDB = CurrentD

sSQL = "SELECT First(ImportDate) FROM TLastImportDate

Set tempSet = poDB.OpenRecordset(sSQL

tpDate = tempSet.Fields("ImportDate").Valu

tempSet.Clos

getLastImportDate = tpDat

End Functio

 

[Reid]

Try using this to set tpDate:

tpDate = tempSet("ImportDate")

Hi,

I'm having a problem with the following code in a module (see below).
This code seems fine as per the examples I saw on internet, but I get an error saying
that I am missing some parameters in the OpenRecordSet method. I thought

the other parameters were optional.

I am only trying to reach a value that is in a table in my database, if

you have better ways to do it, it would be great!

 

[Steve Schapel]

Jenny,

I am not sure specifically what is producing the error message.
However, there are a few problems in your code. First, I am not sure of
the effect of making the Database a parameter of the function, I have
never seen it done that way before. I would dim the poDB variable
within the declarations section instead, as in...
Private Function getLastImportDate() As Date
Dim poDB As Database

Second thing I notice is the use of the First() function. The First()
function returns a random record, which I imagine is not what you want.
You probably really intend the Min() function instead.

Then we have the line
tpDate = tempSet.Fields("ImportDate").Value
which is probably more neatly expressed as
tpDate = tempSet!ImportDate
but, either way, there is no field ImportDate in the tempSet recordset.
There will probably be a field called FirstOfImportDate which is the
default name given in SQL when you use an aggregate function without
aliasing it... alternatively you could have done something like
sSQL = "SELECT First(ImportDate) AS FirstDate FROM TLastImportDate"
.... and then...
tpDate = tempSet!FirstDate

By the way, you should also have put these lines at the end of your code...
Set tempSet = Nothing
Set poDB = Nothing

By the way, where and how would you intend to call/use this function?
Is it appropriate that the function be Private rather than Public?

Ok, now my main point... You are trying to create a function for which
Access already provides a perfectly adequate and much simpler function:
DMin("[ImportDate]","TLastImportDate")

 

[Jonathan Parminter]

-----Original Message-----
Hi,

I'm having a problem with the following code in a module (see below).
This code seems fine as per the examples I saw on

internet, but I get an error saying

that I am missing some parameters in the OpenRecordSet

method. I thought the other parameters were optional.

I am only trying to reach a value that is in a table in

my database, if you have better ways to do it, it would be
great!

Please help!!!

Jenny

Private Function getLastImportDate(poDB As DAO.Database) As Date

Dim tpDate As Date
Dim sSQL As String
Dim tempSet As DAO.Recordset
Set poDB = CurrentDb

sSQL = "SELECT First(ImportDate) FROM TLastImportDate"

Set tempSet = poDB.OpenRecordset(sSQL)

tpDate = tempSet.Fields("ImportDate").Value

tempSet.Close

getLastImportDate = tpDate

End Function

Hi Jenny,
just guessing... but I think you need single quotes for
the field in the sql string

sSQL = "SELECT First('ImportDate') FROM TLastImportDate;"

Luck
Jonathan