Search query with wildcards

[rs2k]

Hi,
I have created a simple Unbound search form based on a 'Bookings' table. There are 4 comboboxes that the user can choose to search on, 'Client_ID', 'BookingType', 'FundingArea' and 'ChargeTo'. I have created a query that uses the following criteria;

Like "*" & [Forms].[frmLBT_db_Tool].[cmbName] & "*"
Like "*" & [Forms].[frmLBT_db_Tool].[cmbBookingType] & "*"
Like "*" & [Forms].[frmLBT_db_Tool].[cmbFundingArea] & "*"
Like "*" & [Forms].[frmLBT_db_Tool].[cmbChargeTo] & "*"

Using this criteria any of the comboboxes can be left blank and the results will display the chosen combobox used, or if 2 comboboxes were used the results would be further filtered.

The problem I have is that if a name was chosen (cmbName - Client_ID) and the Client_ID was 2, then all Client_ID records which have a 2 in them are returned, 2, 12, 20-29,32,42,52 etc...

Is there a way to overcome this, or am I going about it all wrong?

I hope I have provided enough info, if any more is required, then please shout.

Many thanks
Colin

 

[Simon_MT]

Try taking out the leading "*" and then test is the relevant combi is null and ignore the search criteria in other words search on values that have been requested.

Simon

 

[rs2k]

Hi Simon,

Thanks for the reply.

I have taken out the leading "*" and still got similar (if not the same results).

I am not to sure by what you mean by;

'and then test is the relevant combi is null and ignore the search criteria in other words search on values that have been requested'

Can you please be more specific and perhaps show me an example?

On a side note, I have found an article online by Allen Browne who states that the type of search criteria I'm using is no good for numerical searches and provides a VBA solution, I'm still trying to get my head around it though.

If you could help out in the mean time, that would be great.

Many thanks
Colin

 

[Alansidman]

The only way to overcome this is to choose a specific client and that means you cannot use the LIKE function.

change this line

Like "*" & [Forms].[frmLBT_db_Tool].[cmbName] & "*"

to

[Forms].[frmLBT_db_Tool].[cmbName]

Of course, this means that your query will fail if the combo box for the name is not selected.

You can then add a line of code to validate that the combo box for the client name is selected and generate a message if it is empty.

 

[rs2k]

Hi Alan,

Thanks for the input, I thought that would be the outcome. Unfortunately the name criteria is not always required, the user has to filter on FundingAreas, ChargeTo and BookingType as well as names.

I will pursue the VBA route from Allen Browne's article and keep this post updated for future reference.

Many thanks
Colin

 

[rzw0wr]

You can try putting Is Null as an OR in all of the Filtered fields.
Forms![frmLBT_db_Tool]!cmbName
Then under this (OR) type Is Null.

Dale

 

[Simon_MT]

Really. if you are using combiboxes then a value is selected then you don't need to search using wildcards just that specific record. The wildcards seem to be required to search records when nothing is selected and therefore each unpopulated combibox is irrelevant to the search.

It maybe better to intercept the search and test of a value in each combibox and then perform the search on the populated combiboxes.

Simon

 

[Brianwarnock]

The use of Like is only relevant if you are doing partial string searches , and as Simon has pointed out as you are using combo boxes that is not the case, to allow for unselected combos code along the following lines


(Fieldname1= [Forms].[frmLBT_db_Tool].[cmbBookingType] or [Forms].[frmLBT_db_Tool].[cmbBookingType] is null) and
(Fieldname2=[Forms].[frmLBT_db_Tool].[cmbFundingArea] or[Forms].[frmLBT_db_Tool].[cmbFundingArea] is null) and
etc

Brian

 

[rs2k]

Hi,

Thanks all for the replies.

Dale, I tried your suggestion, with no joy.

Simon, Brian, I guess my solution is along the lines of what you have suggested. I pursued the article by Allen Browne and finally got it to work (Or at least it seems like it's working with the small amount of testing I have done).

Here is the code I have used;

Option Compare Database
Option Explicit
Private Sub cmdFilter_Click()
'Author:    Allen Browne June 2006.
'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                    we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#dd\/mm\/yyyy\#"   'The format expected for dates in a JET query string. ## Changed format from mm\/dd\/yyyy
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Number field example. Do not add the extra quotes. ### Started Here #####
   If Not IsNull(Me.cmbName2) Then
        strWhere = strWhere & "([Client_ID] = " & Me.cmbName2 & ") AND "
    End If
    
   If Not IsNull(Me.cmbBookingType2) Then
        strWhere = strWhere & "([BookingType_ID] = " & Me.cmbBookingType2 & ") AND "
    End If
    
   If Not IsNull(Me.cmbFundingArea2) Then
        strWhere = strWhere & "([FundingArea] = " & Me.cmbFundingArea2 & ") AND "
    End If
    
   If Not IsNull(Me.cmbChargeTo2) Then
        strWhere = strWhere & "([ChargeTo_ID] = " & Me.cmbChargeTo2 & ") AND "
    End If
    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.txtDateFrom2) Then
        strWhere = strWhere & "([BKStartDate] >= " & Format(Me.txtDateFrom2, 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.txtDateTo2) Then   'Less than the next day.
        strWhere = strWhere & "([BKStartDate] < " & Format(Me.txtDateTo2 + 1, conJetDate) & ") AND "
    End If
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

Private Sub cmdFilterOff_Click()
    'Purpose: Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    'Remove the form's filter.
    Me.FilterOn = False
End Sub

Many thanks to all those that provided input to my problem.

Cheers
Colin