How to get rows with empty fields as well?

[Orabidoo]

Hey ppl,

I have a table with several fields: ID, firstname, lastname1 and lastname2. When I set the criteria for lastname2 to "*" I only get the rows that actually have a second lastname added. If I don't state any criteria there I get the ones with only the first lastname as well. Why is that?

[RuralGuy]

How about posting the SQL of the query you have?

[CraigDolphin]

Try changing the criteria from "*" to
Is Null or Like "*"

[Orabidoo]

Try changing the criteria from "*" to
Is Null or Like "*"

When I type in "*" the "Like" is added automatically. Can I do something like Is Null OR Like "*"?

[Orabidoo]

It works!

Like "*"+get_global("V_lastname2")+"*" Or Is Null

[Orabidoo]

Damn...I just realized that I always get the ones with empty fields now (not very surprising). There's a field called "country" as well and if I search for, say, Denmark, I also get those people whose country of origin is unknown.

So the desired behaviour is different depending on what the user thinks is the most important parameter. This is getting complicated...

[CraigDolphin]

Life is complicated

You could use another criterion (Is Null) in the country field that excludes records with a null value in that field.

[Orabidoo]

Life is complicated

You could use another criterion (Is Null) in the country field that excludes records with a null value in that field.

But that would exclude those at all times. If I search for "Hansen" from "Denmark" (with "OR Is Null" added) I get both that person as well as all those immigrants whose lastname field is blank.

[Orabidoo]

I think I solved it..! If a text field is empty the corresponding variable is set to "* Or Is Null", otherwise it's txtField + "*". Phew, that was easier than I thought.

[Orabidoo]

Or maybe not... That doesn't work if you try to pass it on as a string, since...well, since it's a text string and not a command. Hmm, is it possible to make commands variable?

[Orabidoo]

Maybe I should build the whole SQL string in VBA code and pass it directly to the list box?