Search function problem

[Highnz]

hi
ive got a problem with a search function
it only seems to work with constants not with variables
ive tried to use the field and criteria as a constant and table as var this works
but i need all of them as a var

im using Access 2002 / XP with VBA 6.3 and MS DAO 3.51 Object Library

Function Find()
Dim Table As String
Dim Field As String
Dim Criteria As String
Dim RS As DAO.Recordset
Dim DB As DAO.Database
Set DB = Application.CurrentDb
Table = InputBox("Enter table")
Set RS = DB.OpenRecordset(Table, dbOpenDynaset)
Field = InputBox("Enter field")
Criteria = InputBox("Enter criteria")
RS.FindFirst (Field Like Criteria)
Find = Null
Do While RS.NoMatch <> True
Find = Find & Chr(13) & RS.Fields("Movex Code").Value & " : " & RS.Fields(Field).Value
RS.FindNext (Field Like Criteria)
Loop
RS.Close
Set RS = Nothing
Set DB = Nothing
End Function

 

[workmad3]

Function Find()
Dim Table As String
Dim Field As String
Dim Criteria As String
Dim RS As DAO.Recordset
Dim DB As DAO.Database
Set DB = Application.CurrentDb
Table = InputBox("Enter table")
Field = InputBox("Enter field")
Criteria = InputBox("Enter criteria")
Set RS = DB.OpenRecordset("SELECT * FROM " & table & " WHERE " & criteria)
If Not (RS.EOF And RS.BOF) Then
RS.MoveFirst
   Do While Not RS.EOF
       Find = Find & Chr(13) & RS.Fields("Movex Code").Value & " : " & RS.Fields(Field).Value
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
Set DB = Nothing
End Function

Try the above. This can then also be used for multi table queries by putting in table names like this
table1, table2, table3

and criteria are entered like this
field1 = value AND field2 = value AND field3 = value

Hope this helps/works

 

[Highnz]

hmm this does not work now the problem is 'syntax error (missing operator) in querry expression'

 

[workmad3]

what values did you type in? remembering that non numerical values need to be quoted in single ' quotes

 

[Highnz]

the thing i want is that i can search in any field of my database

 

[workmad3]

which this will allow, was just wondering what you typed in this specific time to get the syntax error (Like i said if its a text field, you need to enclose the text being searchd on with single quotes 'like this')

 

[Highnz]

ah now i know *gg* ok it works but i got all the records of the table not those who matches with the criteria

 

[workmad3]

so you put it in like this?

fieldname = 'text to match' AND fieldname2 = 'other text to match'

and so on?

 

[Highnz]

yeah i did
i type in the name of the table then the name of the field and then the text which i want to search eg. 'text'
and if type in a field name like 'Trade Name' an error window comes up which says "Too few parameters"

 

[workmad3]

ah yeah... lovely lovely access.

If it has a space in the fieldname, put the field name in square brackets, like so [field name]

 

[Highnz]

oh ^^ with ' it works too but i get no item found in this collection now ^^

 

[workmad3]

ok... are you searching for an exact match? if not, use

[field name] LIKE '*string*'

don't you love sql?

 

[Highnz]

yes in all fields unless its a memo field

 

[workmad3]

hmm... upper and lower case correct as well?

 

[Highnz]

no upper and lower cas is not important

this is the code im using now

Function Find()
Dim Table As String
Dim Field As String
Dim Criteria As String
Dim RS As DAO.Recordset
Dim DB As DAO.Database
Set DB = Application.CurrentDb
Table = InputBox("Enter table")
Field = InputBox("Enter field")
Criteria = InputBox("Enter criteria")
Set RS = DB.OpenRecordset("SELECT " & Field & " FROM " & Table & " WHERE " & Criteria)
If Not (RS.EOF And RS.BOF) Then
RS.MoveFirst
Do While Not RS.EOF
Find = Find & Chr(13) & RS.Fields("Movex Code").Value & " : " & RS.Fields(Field).Value
RS.MoveNext
Loop
End If
RS.Close
Set RS = Nothing
Set DB = Nothing
End Function

 

[workmad3]

umm... upper and lower case are important when doing sql searches... the criteria is case sensitive anyways. Should probably check that out.

 

[Highnz]

Now it works the expression after 'WHERE' wasnt exactly enough

THX 4 HELP )

 

[workmad3]

tis ok. I like to help, and also like to give people the fundamentals of SQL, as it helps for if they ever use a DB other than access