Search as you type VBA

[vector39]

Hello everyone



Basically what I would like to do is replicate Allen Browne's brilliant example (found here http://allenbrowne.com/appfindasutype.html) of a form filtering records as you type or by selecting different options based on a combo box. I followed this example exactly, however I realized, the limitations section indicates why this example isn't feasible. I have a datasheet that I wish to filter using a search bar and a combo box. But one of the columns is a yes/no data type. Does anyone have any insight or guidance on how I can search for records using a query in split form? Thank you.

[rpeare]

yes no can be queried by using -1 for yes, 0 for no (-1 is the check mark, 0 is an unchecked value).
so if the search works for numeric value fields you should be able to adapt the code to deal with a yes/no field.

Probably in this section:

    'Unfilter if there is no text to find, or no control to filter. Otherwise, filter.
    If (strText = vbNullString) Or (strField = vbNullString) Then
        frm.FilterOn = False
    Else
        'this section works for text fields only
        'I would tend to look up the data type from the source table and build this statement accordingly BUT
        'you could do something here for a specific field like:
        if strfield = "YES NO FIELD NAME HERE" Then
            frm.filter = strfield & " = " & if(strtext = "Yes", -1, 0)
        else
             frm.Filter = strField & " Like """ & IIf(mbcStartOfField, vbNullString, mstrcWildcardChar) & strText & mstrcWildcardChar & """"
        endif
        frm.FilterOn = True
    End If

[CJ_London]

or do it the other way round

your form recordsource would be something like

SELECT fld1, fld2,booleanfield, format(booleanfield,"yes/No") as txtBooleanfied.....
FROM....

in your form, display booleanfield, but search on txtbooleanfield

or just display the txtbooleanfield - just depends on what the user can do with the data

Alternatively
you can also use the eval function

strfilter =strfilter & "eval([" & strfield & "] = " & me.searchfld & ") AND "

or universally

strfilter =strfilter & "eval([" & strfield & "] Like *" & me.searchfld & "*) AND "