I'm trying to write a access 2000 VBA search function that accept user text input and return records based on case id or name found (as entered by user). Case id is the primary key for all records. Can anyone help?

If think what you want is to input either case Id or name and have the access find the correct record. First do the regular search by case id, if not found then assume it is a name then search by a secondary index based on name. You will have to add code to display multiple names that might match.

That's correct fred. But i need help in the coding of the search function. Anybody has a pre-built search function?

Hello I am looking for the same search function..if you have got any help already please send me the code.. Preetham

rivate Sub updateQuery() Dim SQLQuery As String Dim bNotFirst As Boolean Dim Magtitle As String Dim contactName As String Dim COUNTRY As String Dim freeText As String Dim jobcode As String Dim busSector As String Dim publisher As String Dim qdf As QueryDef Set db = CurrentDb 'this function allows multi field search and displays the results in a list box 'the list box ofcourse looks at the query TitleSearch Query 'delete query created from prev search DoCmd.DeleteObject acQuery, "TitleSearch Query" bNotFirst = False 'select records from query and load them SQLQuery = "SELECT [Magazine data].[SEQ-NUM] AS [Magazine data_SEQ-NUM], [Magazine data].FOLDER, [Magazine data].[PUBLISHER NAME], [Magazine data].[MAGAZINE TITLE] " & _ "AS [Magazine data_MAGAZINE TITLE], [Magazine data].COUNTRY AS [Magazine data_COUNTRY], [Magazine data].MARKET, [Magazine data].[Archive], [Magazine data].[Historical], [Magazine Contacts].[MAGAZINE TITLE] " & _ "AS [Magazine Contacts_MAGAZINE TITLE], [Magazine Contacts].EDITOR, [Magazine Contacts].[Editor First Name], [Magazine Contacts].[Editor Surname], [Magazine Contacts].[Job Title], [Magazine Contacts].[EDITOR TITLE], [Magazine Contacts].[RAND 1 JOB CODE], [Magazine Contacts].[RAND 2 JOB CODE], [Magazine Contacts].[RAND 3 JOB CODE], [Magazine Contacts].[RAND 4 JOB CODE], [Magazine Contacts].[RAND 5 JOB CODE], [Magazine Contacts].[RAND 6 JOB CODE], [Magazine Contacts].[PROD JOB CODE], [Magazine Contacts].[RAND 1 FIRST NAME], [Magazine Contacts].[RAND 1 SURNAME], [Magazine Contacts].[RAND 2 FIRST NAME], [Magazine Contacts].[RAND 2 SURNAME], [Magazine Contacts].[RAND 3 FIRST NAME], [Magazine Contacts].[RAND 3 SURNAME], [Magazine Contacts].[RAND 4 FIRST NAME], [Magazine Contacts].[RAND 4 SURNAME], [Magazine Contacts].[RAND 5 FIRST NAME], [Magazine Contacts].[RAND 5 SURNAME], [Magazine Contacts].[RAND 6 FIRST NAME], [Magazine Contacts].[RAND 6 SURNAME]" & _ "FROM [Magazine data] INNER JOIN [Magazine Contacts] ON [Magazine data].[SEQ-NUM] = [Magazine Contacts].[SEQ-NUM]" If Not Form!titlesearch = "" Then Magtitle = Form!titlesearch If Not bNotFirst Then SQLQuery = SQLQuery & "WHERE [Magazine data].[MAGAZINE TITLE] Like ""*" & Magtitle & "*"" " bNotFirst = True Else SQLQuery = SQLQuery & "AND [Magazine data].[MAGAZINE TITLE] Like ""*" & Magtitle & "*"" " End If End If If Not Form!jobcode = "" Then jobcode = Form!jobcode If Not bNotFirst Then 'SQLQuery = SQLQuery & "WHERE [Magazine Contacts].[Job Title] Like ""*" & jobcode & "*"" " SQLQuery = SQLQuery & "WHERE [Magazine Contacts].[EDITOR TITLE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[RAND 1 JOB CODE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[RAND 2 JOB CODE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[RAND 3 JOB CODE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[RAND 4 JOB CODE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[RAND 5 JOB CODE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[RAND 6 JOB CODE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[PROD JOB CODE] Like ""*" & jobcode & "*"" " bNotFirst = True Else ' SQLQuery = SQLQuery & "AND [Magazine Contacts].[Job Title] Like ""*" & jobcode & "*"" " SQLQuery = SQLQuery & "AND [Magazine Contacts].[EDITOR TITLE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[RAND 1 JOB CODE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[RAND 2 JOB CODE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[RAND 3 JOB CODE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[RAND 4 JOB CODE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[RAND 5 JOB CODE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[RAND 6 JOB CODE] Like ""*" & jobcode & "*"" OR [Magazine Contacts].[PROD JOB CODE] Like ""*" & jobcode & "*"" " End If End If If Not Form!countrysearch = "" Then COUNTRY = Form!countrysearch If Not bNotFirst Then SQLQuery = SQLQuery & "WHERE [Magazine data].[COUNTRY] Like ""*" & COUNTRY & "*"" " bNotFirst = True Else SQLQuery = SQLQuery & "AND [Magazine data].[COUNTRY] Like ""*" & COUNTRY & "*"" " End If End If -------------------------- If Not Form!namesearch = "" Then contactName = Form!namesearch If Not bNotFirst Then SQLQuery = SQLQuery & "WHERE ([Magazine Contacts].[Editor First Name] Like ""*" & contactName & "*"" OR [Magazine Contacts].[RAND 1 FIRST NAME] Like ""*" & contactName & "*"" OR [Magazine Contacts].[RAND 2 FIRST NAME] Like ""*" & contactName & "*"" OR [Magazine Contacts].[RAND 3 FIRST NAME] Like ""*" & contactName & "*"" OR [Magazine Contacts].[RAND 4 FIRST NAME] Like ""*" & contactName & "*"" OR [Magazine Contacts].[RAND 5 FIRST NAME] Like ""*" & contactName & "*"" OR [Magazine Contacts].[RAND 6 FIRST NAME] Like ""*" & contactName & "*"" OR [Magazine Contacts].[PROD FIRST NAME] Like ""*" & contactName & "*"")" bNotFirst = True Else SQLQuery = SQLQuery & "AND ([Magazine Contacts].[Editor First Name] Like ""*" & contactName & "*"" OR [Magazine Contacts].[RAND 1 FIRST NAME] Like ""*" & contactName & "*"" OR [Magazine Contacts].[RAND 2 FIRST NAME] Like ""*" & contactName & "*"" OR [Magazine Contacts].[RAND 3 FIRST NAME] Like ""*" & contactName & "*"" OR [Magazine Contacts].[RAND 4 FIRST NAME] Like ""*" & contactName & "*"" OR [Magazine Contacts].[RAND 5 FIRST NAME] Like ""*" & contactName & "*"" OR [Magazine Contacts].[RAND 6 FIRST NAME] Like ""*" & contactName & "*"" OR [Magazine Contacts].[PROD FIRST NAME] Like ""*" & contactName & "*"")" End If End If ------------ If Not Form!Snamesearch = "" Then contactsName = Form!Snamesearch If Not bNotFirst Then SQLQuery = SQLQuery & "WHERE ([Magazine Contacts].[Editor Surname] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 1 SURNAME] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 2 SURNAME] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 3 SURNAME] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 4 SURNAME] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 5 SURNAME] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 6 SURNAME] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 6 SURNAME] Like ""*" & contactsName & "*"")" bNotFirst = True Else SQLQuery = SQLQuery & "AND ([Magazine Contacts].[Editor Surname] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 1 SURNAME] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 2 SURNAME] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 3 SURNAME] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 4 SURNAME] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 5 SURNAME] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 6 SURNAME] Like ""*" & contactsName & "*"" OR [Magazine Contacts].[RAND 6 SURNAME] Like ""*" & contactsName & "*"")" End If End If If Not Form!BSCsearch = "" Then busSector = Form!BSCsearch If Not bNotFirst Then SQLQuery = SQLQuery & "WHERE [Magazine data].MARKET = Form!BSCsearch" bNotFirst = True Else SQLQuery = SQLQuery & "AND [Magazine data].MARKET = Form!BSCsearch" End If End If SQLQuery = SQLQuery & ";" 'create the object query with search results Set qdf = db.CreateQueryDef("TitleSearch Query", SQLQuery) Form.refresh On Error GoTo errhandler 'count of results returned Text29 = List27.ListCount & " Records" Text29.Enabled = False errhandler: Err.Clear