Order by IIf statement?

[Minty]

You are missing a bracket after the =8 .

This will run painfully slowly once you get more than a few hundred records.

If you made all the text boxes combo's that only had valid values in them you could remove the Like clauses completely and simply use something like;

      "AND (Scans.Engine = " & (Forms!frmMain!cmbEngine) & " OR  " & (Forms!frmMain!cmbEngine) & " Is Null )" & _

Which allows for nothing being selected in the combo box.

Also if this code is in the form you are running it from you can simplify further to

      "AND (Scans.Engine = " & Me.cmbEngine & " OR  " & Me.cmbEngine & " Is Null ) " & _

 

[Tieval]

Hi Minty,

Unfortunately combos are not really an option as I need to just put a letter or two or the entire value into the text box, the actual application is quite unusual.

The query takes about ten seconds to process with 140000 records, what is strange is that I was hoping to speed things up by putting this final part in the vba rather than having the vba sitting on top of an actual query that did the BladesOnly bit, in truth it has slowed it down.

 

[Minty]

If you are putting in the start of the text so FR* would be you search string, then remove the initial * as that will then use indexes. And make sure the fields are indexed.

 

[Tieval]

Unfortunately I need the 'contains' logic so cannot remove the one wildcard, I have however added an index to some other fields to speed it up but more importantly noticed that I was calling the procedure 'on click' and re-querying the sub-form 'on-update', removing the double load has helped greatly!

I can now re-run the process in four seconds with 140000 records so am quite happy, plus it is a lovely day just north of you in Gloucs

 

[Minty]

Certainly is very nice compared to the recent non stop deluges we've suffered with recently.