I need to create a query to that searches the entire table instead of a particular column. How do I do this? I have 6 different columns that contain serial numbers for 6 different sub units. All 6 of these sub units belong to a main unit (contained in the table as well). I want to be able to search by a serial number that belongs to the sub unit and have it tell me what main unit it belongs to without having to use a the “like” command in each column. I am sure this can be done, but I am just too ignorant to figure it out. Any help that is provided will be GREATLY appreciated.

You've just run into the brick wall of the unnormalized database. To do what you want most effectively will require a union query of all 6 columns into one. Speaking to you from painful pexerience, you'll be much happier if you fix your database first.

Try this topic (my first post, dealing with the SAME issue): http://www.access-programmers.co.uk/ubb/Forum3/HTML/002378.html

Or look into a good website on 'database normalization'.

HTH,
David R

You might try copy/pasting this in a module in Northwind, then calling it from the debug window like this:

? stringfind("Products", "ot")

As shown below, it only works when the key field is an integer, but this is easily modified.

Function StringFind(ptblName As String, pStrToFind As String)
'*******************************************
'Name: StringFind (Function)
'Purpose: Search all text and memo fields
' for specific string
'Inputs: in Northwind debug.window
' ? stringfind("Products", "ot")
'Output: All records containing "ot"
'*******************************************

Dim db As Database, rs As Recordset, tblName As String
Dim test As String, strSQL As String, fld As Field
Dim primHold As String, primType As String, qd As QueryDef

Set db = CurrentDb
' Trap for any errors.
On Error Resume Next
tblName = "z_tblTempHold"
'Does table tblname exist? If true, delete it;

test = db.TableDefs(tblName).Name
If Err <> 3265 Then 'the specified table exists
DoCmd.DeleteObject acTable, tblName
End If

'Find name of primary key
primHold = PrimKey(ptblName)

'SQL string to create new table

strSQL = "CREATE TABLE " _
& tblName _
& "(ThePrim INTEGER CONSTRAINT MyFieldConstraint " _
& "PRIMARY KEY); "

'create the table
db.Execute strSQL

'open target table

Set rs = db.OpenRecordset(ptblName, dbOpenDynaset)

'create query for each text and memo field
For Each fld In rs.Fields

If fld.Name <> primHold And (fld.Type = 10 Or fld.Type = 12) Then
strSQL = "INSERT INTO " & tblName & " ( ThePrim ) " _
& "SELECT " & primHold & " FROM " & ptblName & " WHERE " _
& "(((Instr([" & fld.Name & "], '" & pStrToFind & "'))>0));"
db.Execute strSQL
End If
Next fld
rs.Close

'create select query SQL for records selected
strSQL = "SELECT " & ptblName & ".* FROM " & ptblName & " INNER " _
& "JOIN " & tblName & " ON " & ptblName & "." & primHold & "= " _
& tblName & ".ThePrim ORDER BY z_tblTempHold.ThePrim;"

'create query def
Set qd = Nothing
DoCmd.DeleteObject acQuery, "queryXXX"
Set qd = db.CreateQueryDef("queryXXX", strSQL)
db.QueryDefs.Refresh

'run the query
DoCmd.OpenQuery "queryXXX", acViewNormal

db.Close
End Function

Function PrimKey(tblName As String)
'*******************************************
'Name: primkey (Function)
'Purpose: Programatically determine a
' table's primary key
'Inputs: from Northwind's debug window:
' ? PrimKey("Products")
'Output: "ProductID"
'*******************************************

Dim db As Database
Dim td As TableDef
Dim idxLoop As Index

Set db = CurrentDb
Set td = db.TableDefs(tblName)
For Each idxLoop In td.Indexes
If idxLoop.Primary = True Then
PrimKey = Mid(idxLoop.Fields, 2)
Exit For
End If
Next idxLoop

db.Close
Set db = Nothing

End Function