Query filter search

[mseeker22]

I have a form used to search through customer records. 'F_Booking Customer Search' (See screenshot) and the query behind it 'Q_Booking Customer Search' (also see screenshot) which filters down customer details based on the more information the user enters into the search fields. When the form first opens it should show all customer records.



The query is refreshed on every key stroke entered into any of the search boxes, as with the screenshot I've tried to show this . . . 'sh' has been typed in so far and returned all records in the surname column beginning with 'sh', then if the user were to type something into the forename search box that would then further filter the information.

This works except it only includes fields that have any data in. So if I have a customer that has all fields with data in except say 'Forename' then it won't show that record at all.

I tried to over come this with this in the query : =IIf(IsNull([Forms]![F_Booking Customer Search].[TxtForename])," ",(Like "*" & [Forms]![F_Booking Customer Search].[TxtForename] & "*"))
but sadly this just didn't return any records at all.


I'm having a mental block and can't work out what to do next?


Help please :-)

[June7]

I don't think the IIF is necessry, and you show a space in the " ". Try just the second argument as criteria: Like "*" & [Forms]![F_Booking Customer Search].[TxtForename] & "*"

Check this tutorial http://datapigtechnologies.com/flash...earchform.html

[mseeker22]

Thats what I had tried but because there are some records without 'Forename' for example it won't show them at all, even when I'm using the 'Surname' field for example.

The example shown in the video is basically the same as what I have but every field in his database has data in it . . . unfortunately thats not the same for mine. Due to the methods of collection, there will by Mobile phone numbers for one customer and Home phone numbers for another . . . any ideas?

[June7]

Not sure, maybe:
Like "*" & [Forms]![F_Booking Customer Search].[TxtForename] & "*" Or Is Null

[mseeker22]

Ok, that works! . . . next question :-) . . . how do I get it to sort numbers against blank fields? It is correctly sorting the named fields but for some reason it isn't sorting the numbers see pic >

I don't mind that it shows the fields without numbers in them, but I do need it to show the fields with the correct/matching data in at the top if that makes sense?

Thank you

[mseeker22]

Sorry just to clarify, by 'named fields' I was simply referring to fields that hold customers 'Names'

[June7]

I don't know your data well enough. Why does it go Shillings, Shillings, Williams, Thorpe, Shillings, Williams. Which numbers?

Null will always sort first (or last if Descending). You could use an IIF expression in ORDER BY clause:
ORDER BY IIf(IsNull(field),"999999999", field)