OPENRECORDSET CRITERIA 1

[ZaZa]

Hello everyone, Hope that someone can give me a hand with this problem.

SENARIO:
I have two tables. Tbl01 is used to store a list of Equipment TagNos and info as to what Areas of the plant the equips are located in. It looks something like this:

Tbl01
-----------
AreaID
TagNo (Primary key)
LastTestDate

Tbl02 stores info about the different plant Areas and estimates as to what date each Area would be needed ( ie the StartUpDate).Since the engineers want to record all their previous estimates(or guesses), I have a field with EstimateNo. Tbl02 looks something like this:

Tbl02
-------------
AreaID
EstimateNo
StartUpDate

The same AreaID could have 1 to 5 estimates, with the StartUpDate for EstimateNo 5 being the most recent guess.

GOAL:
GIVEN A TagNO in Tbl01, I WANT TO COMAPRE ITS LastTestDate WITH THE MOST RECENT ESTIMATE FOR StartUpDate IN Tbl02
The two tables are linked by AreaID


My code looks something like this:

Dim rstTbl01 As Recordset, rstTbl02 As Recordset
Dim dbs As Database
Dim strCriteria1 As String, strCriteria2 As String

Set dbs = CurrentDb()

Set rstTbl01= dbs.OpenRecordset("tbl01",dbOpenDynaset)
strCriteria1 = "[TagNo]='" & Me![TagNo] & "'"
rstTbl01.FindFirst strCriteria1

strCriteria2 = "[AreaID]= '" & rstTb01![AreaID] & "' AND [EstimateNo] = Max(EstimateNo)"
Set rstTbl02 = dbs.OpenRecordset("tbl02", dbOpenDynaset)
rstTbl02.FindFirst strCriteria2

If DateDiff(&quot;d&quot;, rstTbl02![LastTestDate], rstTbl02![StartUpDate]) < 90 Then............blah, blah, blah, blah


I put all this on the after up date event for a combo called [TagNo]. It works up to the 2nd criteria -strCrieria2. At this point I get an error message saying &quot; FUNCTION ISN'T AVAILABLE IN EXPRESSION.&quot; There is something wrong with using the MAX function in the 2nd criteria expression.

BUT I DON'T KNOW OF ANY OTHER WAY OF PHRASING THE 2ND CRITERIA TO avoid using the max function and still get the record in tbl02 where:
a) Tbl02!AreaId = Tbl01!AreaId
b) Tbl02!EstimateNo is the latest estimate.


RESUEST:
Can someone suggest a better way or a better phrasing for the 2nd criteria??!!?

Thanks in advance,
ZaZa

 

[jebry]

Hi ZaZa!

Try this for your second recordset:

strCriteria2 = &quot;Select Top 1 * From Tbl02 Where [Area ID] = '&quot; & rstTbl01![Area ID] & &quot;' Order By [EstimateNo] Desc&quot;

Set rstTbl02 = dbs.OpenRecordset(strCriteria2, dbOpenDynaset)

I think that should pull the record you want.

hth
Jeff Bridgham

 

[ZaZa]

Jeff,

Thanks alot for that tip, it worked well!

ZaZa