Like "*" filter in query doesn't show blank records

Cavman · Apr 17, 2013

Morning,

I've created a filter using a query to list records in a continuous form. In the query I have a criteria in each field similar to this:

Code:

 Like "*" & [forms]![frm_ManageAccounts]![txtAccountRefSearch] & "*"

The problem is that if there are any blank fields, the record is not shown. Can anyone advise how I can amend this so it shows blank records also?

Thanks,

Simon

Brianwarnock · Apr 17, 2013

If you are doing this so that any empty control returns all records then code

Yourfield = [forms]![frm_ManageAccounts]![txtAccountRefSearch] or [forms]![frm_ManageAccounts]![txtAccountRefSearch] is null

If you want to do partial searches then try adding
Or yourfield is null

Actually you shouldn't use Like unless you are doing partial searches as the system cannot use indexes and fields being searched should normally be indexed.

Brian

pr2-eugin · Apr 17, 2013

Hello Simon, try adding another Criteria as OR Is Null to the same field..

EDIT : Ha ha, Brian, you beat me to it today..

Brianwarnock · Apr 17, 2013

pr2-eugin said:
Hello Simon, try adding another Criteria as OR Is Null to the same field..

EDIT : Ha ha, Brian, you beat me to it today..

I must have had a big start.

Brian

Cavman · Apr 17, 2013

Thanks for replying guys,

I did think of doing that, but I've just tried it and as I thought, when you input a search criteria, the query now returns records that match your criteria, but also returns all the blank records too!

I need to show all fields when there's no search criteria (including null), and when there is a criteria entered it should only show records that match the criteria (not the null ones!).

Any further thoughts?

Thanks,

Simon

Brianwarnock · Apr 17, 2013

That is what my first example does, but you cannot do partial searches, is that required? You did not answer that point.

Brian

Cavman · Apr 17, 2013

Sorry Brian, I missed that. I should read more carefully!

Yes, partial searches are essential, which is why I've used like. Often the user will be searching for a list of account numbers that begin with a certain number etc.

Do you know any other way around this?

Thanks,

Simon

Brianwarnock · Apr 17, 2013

I've never done it but how about trying a combination of
Like or criteria is null

Brian

Cavman · Apr 17, 2013

I'll experiment and let you know!

recyan · Apr 17, 2013

Just check if below gives some guidelines :
http://www.access-programmers.co.uk/forums/showthread.php?t=221558

perhaps, something like :

Code:

SELECT 
    myTable.TheID, 
    myTable.myField 
FROM 
    myTable
WHERE 
IIf
	(
		Len(Trim(Forms!Form1!txtmyField))>0, 
		((myTable.myField) Like "*" & (Forms!Form1!txtmyField & "*")),
		IIf(IsNull(myTable.myField),"",(myTable.myField))
	)

Thanks

Cavman · Apr 17, 2013

Wow, that works perfectly, thank you so much. It would have taken me weeks to work that out! Here's the final sql in my query which searches in 4 fields...

Code:

SELECT tbl_MasterAccountBase.AccountRef, 
tbl_MasterAccountBase.BasisSAPNo, 
tbl_MasterAccountBase.AccountName, 
tbl_MasterAccountBase.VAPNo
 
FROM tbl_MasterAccountBase
 
WHERE (((tbl_MasterAccountBase.AccountRef) Like "*" & 
[forms]![frm_ManageAccounts]![txtAccountRefSearch] & "*") 
 
AND 
 
((IIf(Len(Trim([forms]![frm_ManageAccounts]![txtAccountNameSearch]))>0,
(([tbl_MasterAccountBase].[AccountName]) Like "*" & ([forms]![frm_ManageAccounts]![txtAccountNameSearch] & "*")),
IIf(IsNull([tbl_MasterAccountBase].[AccountName]),"",([tbl_MasterAccountBase].[AccountName]))))<>False)
 
AND
 
((IIf(Len(Trim([forms]![frm_ManageAccounts]![txtBasisSearch]))>0,
(([tbl_MasterAccountBase].[BasisSAPNo]) Like "*" & ([forms]![frm_ManageAccounts]![txtBasisSearch] & "*")),
IIf(IsNull([tbl_MasterAccountBase].[BasisSAPNo]),"",([tbl_MasterAccountBase].[BasisSAPNo]))))<>False) 
 
AND
 
((IIf(Len(Trim([Forms]![frm_ManageAccounts]![txtVAPNoSearch]))>0,
(([tbl_MasterAccountBase].[VAPNo]) Like "*" & ([Forms]![frm_ManageAccounts]![txtVAPNoSearch] & "*")),
IIf(IsNull([tbl_MasterAccountBase].[VAPNo]),"",([tbl_MasterAccountBase].[VAPNo]))))<>False));

Thank you all for your help with this!

Brianwarnock · Apr 17, 2013

That seems awfully heavy when all you need is

Code:

 Like "*" & [forms]![frm_ManageAccounts]![txtAccountRefSearch] & "*" or  [forms]![frm_ManageAccounts]![txtAccountRefSearch] Is Null

Note that IsNull(field) uses a Function call IsNull is VBA syntax, SQL syntax is Is Null, if handling large amounts of data you will take a performance hit with IsNull.

Brian

Like "*" filter in query doesn't show blank records

Cavman

Registered User.

Brianwarnock

Retired

pr2-eugin

Super Moderator

Brianwarnock

Retired

Cavman

Registered User.

Brianwarnock

Retired

Cavman

Registered User.

Brianwarnock

Retired

Cavman

Registered User.

recyan

Registered User.

Cavman

Registered User.

Brianwarnock

Retired

Similar threads

Users who are viewing this thread