-2

I have a form and a list box what is filter with the criteria when the user is selecting from Combo Boxes.

And the record source from the list box is a query so i want to filter the query

I have used this code in query designer Like " * " & [Forms]![Costumers]![PB_City] & " * " in order to get partial text from the table but it don't show a record if the field is blank.

The code Like " * " & [Forms]![AnyForm]![AnyThing] & " * " leave out the Blank records as I like to use the query designer I wonder what's the exact syntax for the SQL or how to work in the query designer to get this work done as well

Benzion weberman
  • 7
  • 7
  • The LIKE and wildcard with AND operator is correct. Calculate a field to supply a value if field is Null and apply criteria to that field. Example for text type: `Nz([fieldname],"") AS someName`. – June7 Oct 09 '18 at 19:09
  • However, wildcard only useful for text type fields, not numbers or dates (dates are really a number). Review http://allenbrowne.com/ser-62.html – June7 Oct 09 '18 at 19:15
  • In my case i would not like like to look in every field if its Null or Blank renter work whit a if are some thing else not to use Criteria (where clause) if Combo Box is = "" , its waist to run down 1000's of field's to see if they or blank, will make the computer it work slower – Benzion weberman Oct 09 '18 at 19:25
  • How could there be 1000's of fields? Did you mean records? Doesn't matter how many fields have criteria, still same number of records. Possibly you need VBA approach as demonstrated in the referenced tutorial. – June7 Oct 09 '18 at 19:35
  • i mean records but my point is that that the query will look in 1000's records multiple times for every column – Benzion weberman Oct 09 '18 at 19:59
  • I doubt that will be significantly slower. However, I never use dynamic parameterized queries so I could be wrong. If you have any expression in Criteria under any field, it must be evaluated, no matter what the expression result is. – June7 Oct 09 '18 at 20:38
  • this Code`Nz([fieldname],"") AS someName` those not work when a field is empty – Benzion weberman Oct 16 '18 at 16:28

1 Answers1

-1

I found a code whats work type in query designer the following:

if you want to write in SQL take a look on this question,

Click on this link https://stackoverflow.com/a/38899574/9661307

or https://stackoverflow.com/a/38852152/9661307

in Query designer do the following

under your column you do the criteria

Like "*" & [Forms]![myForm]![myControl] & "*" OR [Forms]![myForm]![myControl] Is 
Null
Benzion weberman
  • 7
  • 7
  • but If you use the Query designer whit multiple columns you may result a automatic duplication from the Query designer whit the same **Code** one row after the other like the following Like " * " & [Forms]![myForm]![myControl] & " * " Like " * " & [Forms]![myForm]![myControl] & " * " Like " * " & [Forms]![myForm]![myControl] & " * " Like " * " & [Forms]![myForm]![myControl] & " * " or more and also a bunch of new columns whit code ([Forms]![myForm]![myControl]) , and multiple lines Is Null Is Null Is Null – Benzion weberman Dec 27 '18 at 20:21