problem in searching the records

[veer]

hello

can any one help me by providing the way how can i search the records of one column in another column

actually i have a column which contain some names and in other column i want to search the same name and if found then print else goto next record

i used some looping concepts but not getting the proper idea
please provide some method

thanks in advance
varinder

[QVeen72]

Hi,

By Column you mean Database Table Column..? or Excel Column..? Or Flexgrid/DataGrid Column...?

Regards
Veena

[progvar]

hi!
i am accessing the mdb files

Hi,

By Column you mean Database Table Column..? or Excel Column..? Or Flexgrid/DataGrid Column...?

Regards
Veena

[veer]

Hi!
i am using the access table database

Hi,

By Column you mean Database Table Column..? or Excel Column..? Or Flexgrid/DataGrid Column...?

Regards
Veena

[QVeen72]

Hi!
i am using the access table database

Hi,

Open A Recordset with First Column Loop Through.. In the Loop, Open Another Recordset For Second Column with Matching name , If found Any Print, else Proceed to Next Value.. Follow This Algorithm

RST1 First Recset..
RST2 Second RecSet

[code=vb]
RST1.Open "Select Distinct Col1 From MyTable , Conn
Do While Not RST.EOF
RST2.Open "Select * From MyTable Where Col2='" & RST1(0) & "',Conn
If Not RST2.EOF Then
'Code To print
End If
RST1.MoveNext
Loop
[/code]

REgards
Veena

[veer]

Hi!

CAN WE USE TWO RECORDSET WITH ONE CONNECTION OBJECT

ACTUALLY I AM USING DAO OBJECT AND ACCESSING THE RECORDSET FROM MY LAN NETWORK

Hi,

Open A Recordset with First Column Loop Through.. In the Loop, Open Another Recordset For Second Column with Matching name , If found Any Print, else Proceed to Next Value.. Follow This Algorithm

RST1 First Recset..
RST2 Second RecSet

[code=vb]
RST1.Open "Select Distinct Col1 From MyTable , Conn
Do While Not RST.EOF
RST2.Open "Select * From MyTable Where Col2='" & RST1(0) & "',Conn
If Not RST2.EOF Then
'Code To print
End If
RST1.MoveNext
Loop
[/code]

REgards
Veena

[QVeen72]

Hi,

Yes, You can open many recordsets with One Database Object:
our DAO code should be something like this :

[code=vb]
Set RST1 = DB.OpenRecordSe t("Select Distinct Col1 From MyTable" )
IF RST1.RecordCoun t> 0 Then
Do While Not RST.EOF
Set RST2 = DB.OpenRecordSe t("Select * From MyTable Where Col2='" & RST1(0) & "'")
If RST2.RecordCoun t>0 Then
'Code To print
End If
RST1.MoveNext
Loop
End If
[/code]

Regards
Veena

[progvar]

Hi!
Your code print the whole record of of Column CMPNYNM in the same order as it inthe mdb file but i want a different order for example my mdb file has two column CMPNYNM and SUBNAME
i want to compare the whole record of SUBNAME COLUMN with first record of CMPNYNM COLUMN and if record found in SUBNAME COLUMN then it will print after the FIRST RECORD of CMPNYNM COLUMN and if not match any record then only the FIRST RECORD of CMPNYNM COLUMN print and then move next record in the CMPNYNM COLUMN and again match the SECOND RECORD of CMPNYNM COLUMN with whole SUBNAME COLUMN.

i mean to say every time i want to compare every record of CMPNYNM COLUMN with whole SUBNAME COLUMN.
please do the modification in my code
thanks

Set rsMn = dbMn.OpenRecord set("Select CMPNYNM, INDNT from " & txttable)
If rsMn.RecordCoun t > 0 Then
Do While Not rsMn.EOF
Set NewRec = dbMn.OpenRecord set("Select CMPNYNM,INDNT from " & txttable & " where SUBNAME =' " & Replace(rsMn(0) , "'", "''") & "'")
If NewRec.RecordCo unt = 0 Then
Print #1, rsMn.Fields("CM PNYNM")
End If
If rsMn.EOF = True Then
Exit Do
End If
rsMn.MoveNext
Loop
End If

Hi,

Yes, You can open many recordsets with One Database Object:
our DAO code should be something like this :

[code=vb]
Set RST1 = DB.OpenRecordSe t("Select Distinct Col1 From MyTable" )
IF RST1.RecordCoun t> 0 Then
Do While Not RST.EOF
Set RST2 = DB.OpenRecordSe t("Select * From MyTable Where Col2='" & RST1(0) & "'")
If RST2.RecordCoun t>0 Then
'Code To print
End If
RST1.MoveNext
Loop
End If
[/code]

Regards
Veena

[QVeen72]

Hi,

Try This :

[code=vb]
Set rsMn = dbMn.OpenRecord set("Select Distinct CMPNYNM, INDNT from " & txttable)
If rsMn.RecordCoun t > 0 Then
Do While Not rsMn.EOF
Set NewRec = dbMn.OpenRecord set("Select CMPNYNM,INDNT from " & txttable & " where SUBNAME =' " & Replace(rsMn(0) , "'", "''") & "'")
If NewRec.RecordCo unt > 0 Then
NewRec.MoveFirs t
Print #1, rsMn.Fields("CM PNYNM")
Do While Not NewRec.EOF
Print #1, NewRec.Fields(" CMPNYNM")
NewRec.MoveNext
Loop
End If
rsMn.MoveNext
Loop
End If
[/code]


Regards
Veena

[progvar]

Hi
This code is not printing anything
if i use rsmn.recordcoun t >0 "7th" line then it will not print anything and if i use recordcount =0 then it produces the error no record found

i think now you understand my program and also what kind of out put i want


thanks

Hi,

Try This :

[code=vb]
Set rsMn = dbMn.OpenRecord set("Select Distinct CMPNYNM, INDNT from " & txttable)
If rsMn.RecordCoun t > 0 Then
Do While Not rsMn.EOF
Set NewRec = dbMn.OpenRecord set("Select CMPNYNM,INDNT from " & txttable & " where SUBNAME =' " & Replace(rsMn(0) , "'", "''") & "'")
If NewRec.RecordCo unt > 0 Then
NewRec.MoveFirs t
Print #1, rsMn.Fields("CM PNYNM")
Do While Not NewRec.EOF
Print #1, NewRec.Fields(" CMPNYNM")
NewRec.MoveNext
Loop
End If
rsMn.MoveNext
Loop
End If
[/code]


Regards
Veena

[QVeen72]

Hi,

are you sure CompName and subname Matching data exists..? Can you post some test data here..?

Post some test data (with column names) and required results ..

Regards
Veena

[progvar]

Hi
I ATTACHED A MDB FILE IN WHICH TWO FILES "VIR" AND "VIR1" VIR CONTAIN RANDOMLY ADDED DATA WHILE VIR1 CONTAIN A PROPER WAY OF OUTPUT I NEED

Hi,

are you sure CompName and subname Matching data exists..? Can you post some test data here..?

Post some test data (with column names) and required results ..

Regards
Veena

[QVeen72]

Hi,

Your attachment is not seen here..
Edit the post and attach..

REgards
Veena

[progvar]

Hi!
i am attaching a mdb file in which two tables are present "VIR" AND "VIR1"
VIR contain the randomly inserted data and VIR1 has the required output

Hi,

Your attachment is not seen here..
Edit the post and attach..

REgards
Veena

[Killer42]

I think TheScripts limits what file extensions can be attached. You might need to zip your MDB before you can attach it.

Do you know how to attach a file to a message here? It is a bit awkward.