Creating a 'find' screen like Help => Contents and Index => Find

[SMB]

I have a table with 70000 records. I want to be able to find records which match mutliple criteria

ie field called Description

I want to list all matching records which contains words in text box

ie Line Items, Controlling, Period

(The table is linked from another system so is not normalised - bad I know)

Is there a quick way of scrolling through recordset and returning matches?

 

[DougP]

yes by passing SQL statement to it.

VBA code:
Dim db as database, rst as recordset, SQL as string
Set db = CurrentDb
' SQL string.
SQL = "SELECT * FROM [your Table] WHERE Description like '" & Me!Text1 & "*' AND [Line Items] Like '" & Me!Text2 & "*' And [Controlling] Like '" & Me!Text3 & "*' And [Period] Like '" & Me!Text4 & "*';"
Set rst = db.OpenRecordset(SQL)

This is SQL statement is looking at 4 text boxes on the form to get anything a user would key into them. Text1 to 4

So by using the "Like" parameter, if say Description is blank or don't care then the statement will still try and match the other criteria.
If all fields are blank it will return all of the records in the table.

;-)

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[SMB]

Yes this would work thanks but I was trying to be a bit more flexible.

By having one text box and a separator for the different criteria (could be a space or a comma) just like the standard MS help contents find facility.

Thanks for you input

 

[THoey]

If you get the users search criteria, separated by whatever special character, you could still do as DougP suggested, you would just have to parse the string, and keep adding to your WHERE statement. I would look at the INSTR() and LEFT() functions in the help.

Hope this helps...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[DougP]

Yes as Terry said but it could get messy from the input side as well as the programing side.
KIS "Keep it simple"
That way the user knows to key a description into the Decsription box and Line Items in that box etc.


DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[Tetley18]

when the code works how do you see the fields that match the criteria you are searching with????

I am a beginner in VB