Matching any characters in a query

[Dave Edwards]

Can anyone help me please.

I have been trying to create a report by using a query to include those records specified by the user. One field of a table can contain a number of letters eg
ABC, ADB, CDEB, or DEA etc. I want the user to be able to enter *their* selection of letters (in any order), and then match *any* of their chosen letters with those held in the field so if they entered AD then in my example above it would match on fields 2 and 3 as they both contain at least one of these letters. I don't believe that it can be done with a normal query string, and so a function has been suggested to me.

This is as far as I have got;

Public Function InChars(ByVal strLookFor As String, ByVal strInField As String) As Boolean
Dim i As Integer
InChars = False
For i = 1 To Len(strLookFor)
If InStr(strInField, Mid(strLookFor, i, 1)) = 1 Then
InChars = True
Exit For
End If
Next
End Function

Where in the field of my query I have the following;

Values: InChars([Enter letters:],[TableField])

This gives the user a parameter box into which they can enter their choice of letters. But the table that is created from this contains ALL records from the original table, and not those that were true -1. Athough the records that should match do contain a -1 whereas non matches contain a 0 or an #Error if the field was empty. What I want is for the table to contain *only* those records where they match ie that are currently marked as -1

I've got this far and am probably missing something obvious - can anyone help please.

Dave

 

[TexasTJ]

First, the way I read your explanation with example data of ABC, ADB, CDEB, or DEA and user entered value of AD the query should return all four sample values as they each contain an "A" and/or a "D".

However, since the Values column of the query seems to be correctly flagging which records you want with a -1, have you tried using the criteria of -1 in the Values column of the query?

 

[llyal]

Dave,

If the input is from a pop-up box or form field, I find the easiest solution is to "build" a query.

You breakdown the User input string and then build a query from this breakdown such as (in Pseudo-code):

strSQL = "SELECT * FROM tblTable WHERE "
strSQL = "tblTable.Field Like '" & strFullUsrInput & "' "
While Loop
strSearchCriteria = (Analyze strFullUsrInput) (more than one loop may be required)
strSQL = strSQL & "OR tblTable.Field Like '%" & strSearchCriteria & "&' "
Wend
strSQL= strSQL & ";"

Example strSQL for string 'AD' would be "SELECT * FROM tblTable WHERE tblTable.Field Like '%AD%' OR tblTable.Field Like '%D%' OR tblTable.Field Like '%A%' OR tblTable.Field Like '%DA%';"

You then use the query to base your report;

If you deal with database structured data, you want to stay as close to the SQL language as possible.

If you need help analyzing the string gotten from the User input, that is a separate topic and you will need to search for string parsing.


Give me feedback if this helps,
Llyal

 

[Dave Edwards]

You are right, my explanation was wrong - it should have matched on every occassion. I found out that the reason I couldn't get it to work was because if nothing was held in the field then instead of a true or false value I got an error (#Error ?) I have solved it by making the field a compulsory one which has to have at least on letter in.

Thanks for the reply

Dave