How to find a value in a table?

[Guest]

Hello,

I have a table with 28 columns, each column has different number of rows of
data.
I need to find a value in this table, and return the column name of where
the matching string is found.
For example, if value "abc" is found in the 20th column which has a column
named of "XYZ", I want to create a function that will return "XYZ"

I have code iCol = rst.Fields.Count to find the number of columns in the
table, but I don't know how to loop through each column in the table, then
loop through each row to find the value. Or can I use Find method to search
the entire table, and return the column name of where the value is found?

Thanks in advance

 

[John Spencer]

How can each column have a different number of rows? In a table each
column has to have the same number of rows.

UNTESTED VBA Code follows. Instead of looping through every field of
every record I would probably be using an SQL query to check for records
that have the value I am looking for.

Note that this codes ASSUMES that EVERY field in the recordset is a
string field - no dates, no number fields, no OLE fields, no boolean
(yes/no) fields, etc.

UNTESTED AIRCODE created on the fly with no guarantees

Public Function fGetColumnName (strIn as String)
Dim iCol as Integer 'Number of columns
Dim iLoop as Integer
Dim vReturn as Variant


vReturn = Null

'==================================
'
' ADD CODE TO CREATE A RECORDSET
'
'==================================

iCol = rst.Fields.Count

Do While rst.eof = false
For iLoop = 0 to iCol-1
If rst.Fields(iLoop) = strIn then
vReturn = rst.Fields(iLoop).Name
Exit Do
end if

Next iLoop
rst.moveNext
Loop

fGetColumnName = vReturn

Exit Function

ERROR_Handler:
fGetColumnName = "#ERROR#"
'Optional Line
Msgbox Err.number & ": " & Err.Description,,"Error: fGetColumnName

End Function

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Thanks John for the hint. I am trying to merge your logic in to mine.
First of all the value that I have been talking about is from a recordset so
I have been trying to code with loop within loop...
In more details, for each record in recordset rst3, I need to search in
recordset rst and return the column name in rst. Then each column name
found, I am updating it in a table, recordset rst4. For some reason when I
want to find out how many records in rst3 so I can set up a loop counter,
rst3.RecordCount doesn't give me the correct count. Therefore I created rst2
to do the record count.

Right now, the below code is able to find the column name of the first
record in rst3, but not able to find the rest. There is no errer when I run
the code and I know that the rest of the values in rst3 do exist in rst. I
can't figure why...I hope my code below can explain better than all these
blurb I have written...

---------------------------------------------------------
Public Function GetColumnName()
Dim iCol, iLoop, IDCount, j As Integer
Dim vReturn As Variant
Dim rst, rst2, rst3, rst4 As Recordset
Dim aSQL, bSQL As String
Dim db As Database
Set db = CurrentDb()

'find how many columns in index table
Set rst = db.OpenRecordset("01d Cleaned Up Index")
iCol = rst.Fields.Count

'number of AssetID need to match in indices
aSQL = "SELECT Count(Index_Mapping_Tbl.AssetID) AS CountOfAssetID FROM
Index_Mapping_Tbl;"
Set rst2 = CurrentDb.OpenRecordset(aSQL, dbOpenDynaset)
IDCount = rst2!CountOfAssetID 'number of AssetID

bSQL = "SELECT Index_Mapping_Tbl.AssetID FROM Index_Mapping_Tbl;"
Set rst3 = db.OpenRecordset(bSQL) 'AssetIDs need to be matched


Set rst4 = CurrentDb.OpenRecordset("Index_Mapping_Tbl")

For j = 1 To IDCount
strIn = rst3!AssetID
For iLoop = 0 To iCol - 1
If rst.Fields(iLoop) = strIn Then
vReturn = rst.Fields(iLoop).Name
With rst4
.Edit
.Fields(8) = vReturn
.Update
End With
End If
Next iLoop 'next Index column
rst3.MoveNext
Next

End Function


Thankyou so much for your patience and help!!

 

[Guest]

Oh..I just found out something
In my first email I mentioned there are different number of row of data.
What I meant was column 1, I have data up to row 25, column 2, I have data up
to row 15...so each column has data up to different row.
Now what I found is the code can only return the column name when the
matching value is at row 1, and when updating to table Index_Mapping_Tbl, it
keeps updating to the first row only, so whatever was updated before is
replaced...

 

[John Spencer]

To get an accurate count of records in a recordset, you must move to the
last record and then you should get an accurate count.

Something like:

Set rst3 = db.OpenRecordset(bSQL) 'AssetIDs need to be matched
If rst3.RecordCount > 0 then
rst3.MoveLast
iCountRst3 = rst3.RecordCount
rst3.MoveFirst
End IF

I'm at a loss on how you select which record in rst4 to Edit. It looks as
if you would just be assigning a value, updating the record and then getting
the next record.

This whole thing looks strange to me and I'm not getting a handle on what is
happening.



Public Function GetColumnName()
'You must declare each variable's type individually; If you don't
' specify the type the variable will be a variant.
Dim iCol as Integer, iLoop as Integer, IDCount as Integer, j As Integer
Dim vReturn As Variant
Dim rst As Recordset, rst2 As Recordset, rst3 As Recordset, rst4 As
Recordset
Dim aSQL As String , bSQL As String

Dim db As Database
Set db = CurrentDb()

'find how many columns in index table
Set rst = db.OpenRecordset("01d Cleaned Up Index")
iCol = rst.Fields.Count

'number of AssetID need to match in indices
aSQL = "SELECT Count(Index_Mapping_Tbl.AssetID) AS CountOfAssetID FROM
Index_Mapping_Tbl;"
Set rst2 = CurrentDb.OpenRecordset(aSQL, dbOpenDynaset)
IDCount = rst2!CountOfAssetID 'number of AssetID

bSQL = "SELECT Index_Mapping_Tbl.AssetID FROM Index_Mapping_Tbl;"
Set rst3 = db.OpenRecordset(bSQL) 'AssetIDs need to be matched


Set rst4 = CurrentDb.OpenRecordset("Index_Mapping_Tbl")

For j = 1 To IDCount
strIn = rst3!AssetID
For iLoop = 0 To iCol - 1
If rst.Fields(iLoop) = strIn Then
vReturn = rst.Fields(iLoop).Name
With rst4
.Edit
.Fields(8) = vReturn
.Update
End With
End If
Next iLoop 'next Index column
rst3.MoveNext
Next j

End Function



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks John. I know it is confusing, think only myself know what I am doing.
But maybe you can help me on this...
Right now my only issue is the code only returns the column name when the
record is located in row 1 of the column. for example, if "abc" is located
at the first row of column "XYZ", I will get a return value of "XYZ". But if
"abc" is located at 10th row of column "XYZ", I don't get anything, the code
just excute without returning anything. It is this section here that's doing
the search:
-----------------------------------------------------------------------
For iLoop = 0 To iCol - 1 'for each column in Index
If rst.Fields(iLoop) = strIn Then 'find the AssetID in
each Index column
vReturn = rst.Fields(iLoop).Name
With rst4 'update Index Name to
Index_Mapping_Tbl
.Edit
.Fields(8) = vReturn
.Update
.MoveNext
End With
End If
Next iLoop