search coding

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
I got only as far as the contents of your tables. To me, it appears that you
should combine the Transmittals and the TransmittalNo by simply adding the
Date field to Transmittlas. BTW, you should rename this because Date is a
reserved word in Access and you could run into problems if you aren't already.
Access has a fit sometimes when it runs into a reserved word in queries. Try
this and see if you still have problems.
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.
Click to expand...
 
Hello,
SELECT qrymaxTransmittal.Originator, qrymaxTransmittal.DISCIPLINE,
qrymaxTransmittal.TYPE, qrymaxTransmittal.DocumentNo,
qrymaxTransmittal.TITLE, qrymaxTransmittal.PLANT, qrymaxTransmittal.ZONE,
qrymaxTransmittal.[CIVIL UNIT], qrymaxTransmittal.[PROCESS UNIT],
qrymaxTransmittal.MaxOfREV, qrymaxTransmittal.MaxOfTRANSMITTAL,
tbltransmittalNo.RevDATE
FROM qrymaxTransmittal LEFT JOIN tbltransmittalNo ON
qrymaxTransmittal.MaxOfTRANSMITTAL = tbltransmittalNo.TRANSMITTAL
GROUP BY qrymaxTransmittal.Originator, qrymaxTransmittal.DISCIPLINE,
qrymaxTransmittal.TYPE, qrymaxTransmittal.DocumentNo,
qrymaxTransmittal.TITLE, qrymaxTransmittal.PLANT, qrymaxTransmittal.ZONE,
qrymaxTransmittal.[CIVIL UNIT], qrymaxTransmittal.[PROCESS UNIT],
qrymaxTransmittal.MaxOfREV, qrymaxTransmittal.MaxOfTRANSMITTAL,
tbltransmittalNo.RevDATE;

As I wrote some times I need to search transmittal that are not maximum in the
group so wih this query in the search result i can find document NO for
that Transmittal but it bring max transmittal in the search.for example
search "t-002"
Doc No title transmittal
st-dwg-0001 test t-001
st-dwg-0001 test t-002
st-dwg-0001 test t-003
it brings.
Doc No title transmittal
st-dwg-0001 test t-003


I like to know there is any way in VBA (I Posted before) for developing my
search
or i have to make another form and query for theese cases.

Best regards


jahoobob via AccessMonster.com said:
I got only as far as the contents of your tables. To me, it appears that you
should combine the Transmittals and the TransmittalNo by simply adding the
Date field to Transmittlas. BTW, you should rename this because Date is a
reserved word in Access and you could run into problems if you aren't already.
Access has a fit sometimes when it runs into a reserved word in queries. Try
this and see if you still have problems.
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.
Click to expand...
Click to expand...
 
Back
Top