Filter subform from combo unbound text box

[hughess7]

Hi all

I have a combo on the main form (frmDealerClaimCheck) which is set to Value
List, the Rowsource is set programmatically and contains all field names and
data types from a specific query. There is also an unbound text box on main
form which contains the criteria to filter on. When the user presses a filter
button I want the subform (frmClaims) to filter the records (it shows all
records when first opened) to just the criteria selected in the unbound text
box for the field specified in the combo box.

Can anyone help with the coding please? I have the field name to search in a
field called cboField, the data type of this field is stored in column(1) (eg
Text, Date/Time etc) of this combo box. The criteria to search is in a field
called txtCriteria on the main form.

Hope this makes sense... thanks. Sue

 

[hughess7]

Hi Ken

Thank you so much!! I looked at that for so long and didn't spot the
equality operator instead of the concatenate value. Doh! For ref I am in the
UK hence the date formatting.

Works a treat now . Now I have that working I need to look at filtering
further as you suggested in your other post. The only thing at the moment
that it doesn't cover is a date range or generally we use > Repair_Date to
search for records only after a certain date. I guess I could just do as you
suggest and use the same fields, get them to filter again but put an unbound
control for the operator do you think? Default equal '=' or '>' etc...

Thanks again, most appreciated. Sue

Sue:

That's essentially the same as mine, but I've used the ISO standard for
date
notation of YYYY-MM-DD rather than the US short date format. Both should
work. It may be just typo's in your post but you have a equality operator
where you need to concatenate the values:

strFilter = txtField & Format(strDate, "\#mm\/dd\/yyyy\#")

It would also work without the formatting if the system uses US short date
format, but internationalizing it by formatting the date is always
prudent.

Ken Sheridan
Stafford, England

Sorry about all the posts... these are finally coming though from
yesterday
morning!!

I've moved on since, got the filter working for any text field but can't
get
the date fields working. Tried the following but just returns null
recordset
for subform... going round and round in circles doing my head in on this -
pls help!!!
Thanks
Sue

Dim txtSearch As String, txtType As String, txtField
Dim frm As Form, strFilter As String, strDate As Date

Set frm = Me.frmClaims.Form

txtType = Me![cboField].Column(1)
txtSearch = Me![txtCriteria]
strDate = Me![txtCriteria]
txtField = "[" & Me![cboField] & "]="
'also tried strDate = CDate(Format(txtSearch, strJetDate))

Select Case txtType
Case "Text"
strFilter = txtField & """" & txtSearch & """"
Case "Date/Time"
strFilter = txtField = "#" & strDate & "#"
' also tried strFilter = txtField = Format(strDate,
"\#mm\/dd\/yyyy\#")
End Select

frm.Filter = strFilter
frm.FilterOn = True

Hi all

[quoted text clipped - 20 lines]

Hope this makes sense... thanks. Sue

 

[hughess7]

Getting there, got '>' etc working but struggling with the 'like' syntax -
anyone help me correcting this please? Found loads of examples talking about
queries but not in vba using variable names.

Thanks

 

[hughess7]

Thanks Ken

Sue:

Assuming the txtField variable has been assigned a value such as
"[LastName]
Like " with:

txtField = "[" & Me![cboField] & "] Like "

and, for example, you want to return records where LastName starts with
the
character(s) entered I think then this should do it:

strFilter = txtField & """" & txtSearch & "*"""

or for a match anywhere in LastName:

strFilter = txtField & """*" & txtSearch & "*"""

or for where LastName ends with the character(s) entered:

strFilter = txtField & """*" & txtSearch & """"

Ken Sheridan
Stafford, England

Getting there, got '>' etc working but struggling with the 'like' syntax -
anyone help me correcting this please? Found loads of examples talking
about
queries but not in vba using variable names.

Thanks

Sue:

[quoted text clipped - 87 lines]

Hope this makes sense... thanks. Sue