Using IIF & Like statements in query

[r.harrison]

Hi All,

Normally I would do this in VBA, but I'm intrigued as to whether I can do it in a query.

I'm building an advanced search for my DB and the user can select many option:

1) What field to search in
2) Whether to search the entire field, or Any part of the field
3) Whether to search all records, or just 'Live' Records.

To do this I'm trying to use the iif statement. But can't get the LIKE operator to work inside the iif.

eg:

IIf([srchCriteria1]=1,Like '*' & [name] & '*',[name])

This works fine if srchCriteria1 isn't '1' and the full name is found. But can't get it to search any part of field.

Any help greatly appreciated.

 

[Brianwarnock]

Think that in SQL you would code
where srchcriterial =1 and fieldname Like '*' & [name] & '*' or fieldname = [name]

But don't use name as an object name

Brian

 

[r.harrison]

Thanks Brian,

I think that your code would return all values either like or exact match to [name] I was looking for the user to be able to choose whether it should be an exact match or not.

ps. I'm not going to use [name] in the query, it'll be more like [txt_Search]

 

[Brianwarnock]

It might need () round the first bit but surely it works if you enter smith and srchcriterial is not 1 then only full name is found, but if it is 1 then all words with smith in including just smith will be found.

Brian

 

[MSAccessRookie]

As you are aware, the structure of a Standard IIf() statement is as follows:

IIf(Item to Test, Value if TRUE, Value if FALSE)

Value if TRUE and Value if FALSE can also be IIf statements, and you can nest the IIf() statements several levels deep if the situation requires you to. You might be able to use nested IIf() statements to handle the variable conditions that your users will encounter

 

[CBrighton]

I'm not sure, but you may not be able to do this purely via SQL / query builder.

I have recently been updatingvarious query criteria, and in any instance where I use an Iif statement I have had to use it around the values themselves.

e.g. the following did not work:

Iif(DatePart("w",Date())=2,>=Date-3,>=Date-1)

Whereas moving the >= to the start instead of inside each Iif result meant it would work:

>=Iif(DatePart("w",Date())=2,Date-3,Date-1)

I had a similar situation with Between, I couldn't have one Iif statement holding the 2 possible betweens, I had to do Between *Iif statement 1* And *Iif statement 2*.

I suspect you need the Like at the beginning if it is to be used, which then eliminates the possibility of limiting it to an exact match.



Instead I'd set the .filter via VBA where possible, that way you can use proper If statements.

 

[r.harrison]

Thanks everyone. Finally figured it out by adding extra fields to the query which were the iif statements. Finished SQL is pretty complex. Thank MS for the Query Builder!

SELECT tbl_Address.[Customer Ref], tbl_Address.Surname, tbl_Address.[Post code], tbl_Telephone.[Contact number], IIf([Forms]![frm_Search]![cbo_MA]=1,[tbl_Address]![Surname] Like '*' & [forms]![frm_Search]![txt_search] & '*',[tbl_Address]![Surname]=[forms]![frm_Search]![txt_search]) AS SN, IIf([Forms]![frm_Search]![cbo_MA]=1,[tbl_Address]![Post code] Like '*' & [forms]![frm_Search]![txt_search] & '*',[tbl_Address]![Post code]=[forms]![frm_Search]![txt_search]) AS PC, IIf([Forms]![frm_Search]![cbo_MA]=1,[tbl_Telephone]![Contact number] Like '*' & [forms]![frm_Search]![txt_search] & '*',[tbl_Telephone]![Contact number]=[forms]![frm_Search]![txt_search]) AS Tel, IIf([Forms]![frm_Search]![cbo_MA]=1,[tbl_Cards]![Card number] Like '*' & [forms]![frm_Search]![txt_search] & '*',[tbl_Cards]![Card number]=[forms]![frm_Search]![txt_search]) AS CN, IIf([Forms]![frm_Search]![cbo_ST]=1,[tbl_policy]![Policy Status]="Live",[tbl_policy]![Policy Status] Is Not Null) AS PS
FROM ((tbl_Address INNER JOIN tbl_Telephone ON tbl_Address.[Customer Ref] = tbl_Telephone.[Customer Ref]) INNER JOIN tbl_Cards ON tbl_Address.[Customer Ref] = tbl_Cards.[Customer Ref]) INNER JOIN tbl_Policy ON tbl_Address.[Customer Ref] = tbl_Policy.[Customer Ref]
WHERE (((IIf([Forms]![frm_Search]![cbo_MA]=1,[tbl_Address]![Surname] Like '*' & [forms]![frm_Search]![txt_search] & '*',[tbl_Address]![Surname]=[forms]![frm_Search]![txt_search]))=IIf([Forms]![frm_Search]![cbo_LI]=1,True,False)) AND ((IIf([Forms]![frm_Search]![cbo_MA]=1,[tbl_Address]![Post code] Like '*' & [forms]![frm_Search]![txt_search] & '*',[tbl_Address]![Post code]=[forms]![frm_Search]![txt_search]))=IIf([Forms]![frm_Search]![cbo_LI]=2,True,False)) AND ((IIf([Forms]![frm_Search]![cbo_MA]=1,[tbl_Telephone]![Contact number] Like '*' & [forms]![frm_Search]![txt_search] & '*',[tbl_Telephone]![Contact number]=[forms]![frm_Search]![txt_search]))=IIf([Forms]![frm_Search]![cbo_LI]=3,True,False)) AND ((IIf([Forms]![frm_Search]![cbo_MA]=1,[tbl_Cards]![Card number] Like '*' & [forms]![frm_Search]![txt_search] & '*',[tbl_Cards]![Card number]=[forms]![frm_Search]![txt_search]))=IIf([Forms]![frm_Search]![cbo_LI]=4,True,False)) AND ((IIf([Forms]![frm_Search]![cbo_ST]=1,[tbl_policy]![Policy Status]="Live",[tbl_policy]![Policy Status] Is Not Null))=True));

 

[vbaInet]

Just fyi, what you've just done isn't the most effecient way of filtering your records. The IIF() statement has moved from being in the WHERE clause to becoming a hidden field.

So instead of the query optimising the LIKE operator for the entire dataset, it is having to do it for each record and then checking if it's Not False before the row is returned.

A slightly optimised version would have been:

[Field] LIKE IIF([srchCriteria1]=1, '*' & [name] & '*',[name])

To be honest, you are better off building it in VBA like you initially had it.

 

[r.harrison]

Thanks VbaInet, (again!)

I've taken your advice and put it in to a function which is called on the afterupdate of all the controls.

Private Function DoSearch()
    Dim SQL As String
    Dim WhereStatement As String
    
    Select Case Me.frm_SF ' Option frame to select which field to look in
        Case 1
            WhereStatement = "[tbl_address].[Surname] "
        Case 2
            WhereStatement = "[tbl_address].[Post code] "
        Case 3
            WhereStatement = "[tbl_Telephone].[Contact Number] "
        Case 4
            WhereStatement = "[tbl_cards].[Card Number] "
    End Select
    Select Case Me.frm_SW ' Option frame to select what to match
        Case 1
            WhereStatement = WhereStatement & "LIKE '*' & '" & Me.txt_Search & "' & '*'"
        Case 2
            WhereStatement = WhereStatement & "= '" & Me.txt_Search & "'"
    End Select
    Select Case Me.frm_Status ' Option frame to select Live or All policies
        Case 1
            WhereStatement = WhereStatement & " AND [tbl_policy].[Status] = 'Live'"
        Case 2
            WhereStatement = WhereStatement
    End Select
        
    SQL = "SELECT TBL_Address.[Customer Ref], TBL_Address.Surname, TBL_Address.[Post code], TBL_Telephone.[Contact number], TBL_Cards.[Card number], TBL_Policy.Status " & _
        "FROM ((TBL_Address INNER JOIN TBL_Telephone ON TBL_Address.[Customer Ref] = TBL_Telephone.[Customer Ref]) INNER JOIN TBL_Cards ON TBL_Address.[Customer Ref] = TBL_Cards.[Customer Ref]) INNER JOIN TBL_Policy ON TBL_Address.[Customer Ref] = TBL_Policy.[Customer Ref] " & _
        "WHERE " & WhereStatement & " ORDER BY TBL_Address.[Customer Ref];"
    
    Me.List26.RowSource = SQL
    
End Function

Is there any way of calling a function when any control has changed rather than putting code in each control's event?

 

[CBrighton]

You could try the forms after update event, but I'm not sure if that fires as soon as a control is updated or as soon as the recordset is updated (i.e. the changes saved).

I know that I use the event for my audit logs to record any changes to any control on the form, but I'm not sure at which point the code actually fires.

 

[r.harrison]

I think the form afterupdate fires just before the form closes or updates.

In VB you can use a private function and add HANDLES txt_search, frm_sf.... but I can't find anything like that in Access

 

[vbaInet]

Happy to hear r.harrison

I noticed you're not requerying your listbox and you're not performing any validation on the textboxes for Null or the zero-length string. Was the code just the version?

I think the form afterupdate fires just before the form closes or updates.

The form's AfterUpdate event performs a commit to the database so it fires when Dirty is True. So if you made a change to one of the controls and you move between records, the sequence of "events" (w.r.t the form's AfterUpdate) will be BeforeUpdate > Commit > AfterUpdate > NextRecord.

Is there any way of calling a function when any control has changed rather than putting code in each control's event?

In VB you can use a private function and add HANDLES txt_search, frm_sf.... but I can't find anything like that in Access

You can do this in VBA too but you need a class for this. It's called Event Sinking.

 

[r.harrison]

!?

Event Sinking

I've looked in to creating classes but didn't get very far. Is there a good source for 'how to' on this?

 

[vbaInet]

Erm, I don't think there's much information on Event Sinking on the net. You could try looking into WithEvents because that's the key event sinking.

Before you go into this you will need to understand classes and OOP concepts (if you don't already).

 

[CBrighton]

Sounds like a lot of hassle to save having a single line of VBA in each control calling the function.

I think I'd add a command button to run the function myself, that way once the user has set the required criteria they can apply it with a click of a button.

Unless there is some reason that I missed?

 

[r.harrison]

@CBrighton,

No real reason that I couldn't use a command button. Same as I could use the after update event of the search box to do it. I just like to make things difficult for myself!

VbaInet, will look in to WithEvents. Even if I don't use it this time, it's always worth learning new things.

 

[vbaInet]

It is quite involving. But I think r.harrison is doing it for his own edification.

One would normally use event sinking when you have lots of controls that use the same event(s).

Another way is to hook the events in the Open event of your form using the Code Builder syntax. E.g.

for each ctl in Me.Detail.Controls
    if ctl.controltype = actextbox then
        ctl.AfterUpdate = "=MyFunction()"
    end if
next

But then again this method is mainly useful when you have lots of controls to hook events to.

 

[CBrighton]

Fair enough.

I tend to favour simplicity, mainly because at some point someone else is going to need to understand my code (whether it's because I'm unavailable or no longer work here), and going for methods which are hard to find documentation for online could cause big headaches down the road.

Of course, if the documentation within the code is good enough that cancels out the risk of understanding, but if they will find it hard to find detailed documentation the risk remains if they need to add to the existing code.

Plus any additional knowledge, such as object orientated programming, will be required by people who work on the code after you.

 

[vbaInet]

I tend to favour simplicity, mainly because at some point someone else is going to need to understand my code (whether it's because I'm unavailable or no longer work here), and going for methods which are hard to find documentation for online could cause big headaches down the road.

Then they call you, the subject matter expert, to come and fix the problem as a Consultant. Big pay cheque.

 

[r.harrison]

I do like these discussions on here. Thanks for that little code snippet vbaInet, Exactly what I was looking for.

And yes, this is mainly for my own edifiaction. Being a self taught VBA'er I have picked up a few bad habits so just trying to iron them out.

Thanks again!