Hello

I have a search coding that it has to find records from 3
tables;tblDocuments,tblTransmittals,
tblTransmittalNo,

tblDocuments consists these fields

Doc No        Title        zone        plant       

tblTransmittals consists these fields

Doc No        Transmittal        Rev       

tblTransmittalNo consists these fields

Transmittal        Date

my search should find max Rev , max Transmittal for each criteria, so I have
used a

a  simple total query (max) for the result of searching at the end of my
coding, but some times

it doesn,t work correctly for the searching transmittal or date that are not
maximum or last.

how I have to solve my problem for these cases.

for examlple

tblDocuments

Doc No            Title        zone        plant       

ct-st-dwg-1001        test        2        CA       

tblTransmittals

Doc No                             Transmittal           Rev         
ct-st-dwg-1001        T-001           00
ct-st-dwg-1001                          T-002           01
ct-st-dwg-1001                          T-003           02

tblTransmittalNo

Transmittal        Date
T-001            04-JUN-06
T-002            04-AUG-06
T-003            04-SEP-06

When I search "T-002" my search find

Doc No              Title    zone    plant    transmittlal     rev           
ct-st-dwg-1001     test    2    CA      t-003          02

my coding procedure is as below:

Private Sub cmdSearch_Click()
Dim varWhere As Variant, varDateSearch As Variant
Dim rst As DAO.Recordset

varWhere = Null
varDateSearch = Null

If Not IsNothing(Me.txtDocumentNo) Then
       ' .. build the predicate
       varWhere = "[DocumentNo] LIKE '" & Me.txtDocumentNo & "*'"
   End If

If Not IsNothing(Me.txtTitle) Then
varWhere = (varWhere + " AND ") & "[Title] LIKE '" & Me.txtTitle & "*'"
   End If
.
.
.
.
If Not IsNothing(Me.txtTransmittal) Then

varWhere = (varWhere + " AND ") & _
           "[DocumentNo] IN (SELECT DocumentNo FROM tblTransmittalls " & _
           "WHERE tblTransmittalls.Transmittal LIKE '" & Me.txtTransmittal
& "*')"
           End If

Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblDocuments WHERE " &
varWhere)

If rst.RecordCount = 0 Then
       MsgBox "No Documents meet your criteria.", vbInformation, gstrAppTitle
rst.Close
       Set rst = Nothing
       Exit Sub
   End If
Me.Visible = False
rst.MoveLast

If vbYes = MsgBox("Your search found " & rst.RecordCount & " Documents.  " & _
           "Do you want to see a summary list first?", _
           vbQuestion + vbYesNo, gstrAppTitle) Then
           
           DoCmd.OpenForm "frmDocumentSummary", WhereCondition:=varWhere

Regards.