Filtering Search Form

[raychow22]

Hi Forum,



Can anyone help me with my accdb. I basically used http://allenbrowne.com/ser-62.html sample db for creating "Search Form".

the Table is called "tblInspectionReport"
the Form I want to create the Form in is "frmSearchForm"

I got as far as setting up my form; however, the I am having trouble coding it to filter my search. The search criteria is based on "Segment", "WorkArea", "InspectorName", & "Dates_of_Inspection"

Thanks,searchForm8.16.17.zip

[June7]

What is the 'trouble'? What conditions produce the issue? What happens - error message, wrong results, nothing?

Work Area and Inspector comboboxes are providing text values. Text parameters need apostrophe or quote delimiters. Use one of Allen's examples for text data.

Or fix the comboboxes to return numeric ID. Make them multi-column, example for WorkArea:

RowSource: SELECT WAID, WorkAreaNumber FROM tblWAID;
ColumnCount: 2
ColumnWidths: 0";1"

[raychow22]

What is the 'trouble'? What conditions produce the issue? What happens - error message, wrong results, nothing?

Work Area and Inspector comboboxes are providing text values. Text parameters need apostrophe or quote delimiters. Use one of Allen's examples for text data.

Or fix the comboboxes to return numeric ID. Make them multi-column, example for WorkArea:

RowSource: SELECT WAID, WorkAreaNumber FROM tblWAID;
ColumnCount: 2
ColumnWidths: 0";1"

June7,

Am I supposed to use the combo box example code from Allen, that looks like this

'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
'If Me.cboFilterIsCorporate = -1 Then
'strWhere = strWhere & "([IsCorporate] = True) AND "
'ElseIf Me.cboFilterIsCorporate = 0 Then
'strWhere = strWhere & "([IsCorporate] = False) AND "
'End If

[raychow22]

The problem is doesn't display anything. I noticed something at the bottom of the picture.

[raychow22]

My code looks like the following:

'Segment filter
If Not IsNull(Me.cboFilterSegment) Then
strWhere = strWhere & "([Segment] = ""*" & Me.cboFilterSegment & "*"") AND "
End If

'Work Area filter
If Not IsNull(Me.cboFilterWorkArea) Then
strWhere = strWhere & "([WorkArea] = ""*" & Me.cboFilterWorkArea & "*"") AND "
End If

'InspectorName filter
If Not IsNull(Me.cboFilterInspectorName) Then
strWhere = strWhere & "([InspectorName] = ""*" & Me.cboFilterInspectorName & "*"") AND "
End If

'Deficiencies filter
If Not IsNull(Me.cboFilterDeficiencies) Then
strWhere = strWhere & "([Deficiencies] = ""*" & Me.cboFilterDeficiencies & "*"") AND "
End If

'Date field example. Use the format string to add the # delimiters and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date_Of_Inspection] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Date_Of_Inspection] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

[raychow22]

Here's what happens when it's unfiltered and when I try to filter "Segment" combobox

[June7]

Yes, I expect the Yes/No code should work with Deficiencies value.

Did you make adjustments as suggested for WorkArea and Inspector?

[raychow22]

Ok I furthered my progress. I removed the asterisk***** from the code and it worked. Only thing I need to fix is the Inspector Name.. it displays the initials which is the first column in the row source. Is there a way to make it bound to the second column (Written out Name).

Thanks, Here's a picture.

[June7]

Your code can reference combobox columns by index. Index begins with 0. Column 2 would be index 1.

Me.cboFilterInspectorName.Column(1)

Again, filtering on autonumber unique ID primary key would be better.

tblLABnames is not normalized structure.

Name parts should be in separate fields: LName, FName, MName

[raychow22]

Okay! Almost there...


I want my Report Name to be FileName: Format([DateofInspection],"yyyy-mm-dd") & "-" & [InspectorName]; however the "InspectorName" I only want it to be the initials for example "Raymond Chow" = "RC".

I have a table with two fields for full name and initals. Where do I start?

[andy49]

Take a look at https://access-programmers.co.uk/for...d.php?t=181247

[June7]

I NEVER set lookups in table. http://access.mvps.org/Access/lookupfields.htm

If the name parts were in separate fields, the initials could be calculated.

To make both name and initials available for display, have to include tblQEInames in the form RecordSource.

FileName: Format([Date of Inspection], "yyyy-mm-dd") & "-" & [QEIinitials]