How to handle empty fields with wildcard

[krooyv]

Hi.

I have a problem with the below query. I am using an Access database.

SQL = "SELECT DISTINCT Etternavn, Fornavn, PersonID, Tittel FROM Person WHERE Fornavn LIKE '"& fn &"%' AND Etternavn LIKE '"& en &"%' ORDER BY Etternavn, Fornavn ASC"

There are some records in the DB where there are no value for "Etternavn". When I run the above query, the records without "Etternavn" don't display, only records with both "Etternavn" and "Fornavn".

Any idea how to solve this?

Thanks!

[oracleguy]

You can change the "AND" in your WHERE clause to an "OR" and it would solve your problem. However, it might not give the results you were looking for, if that is the case you could just use a more extensive WHERE caluse to solve that problem.

[krooyv]

When I use OR, all posts will show.

I want to return both posts that contain "Etternavn" and posts that don't contain "Etternavn".

Can anyone give an example of a WHERE clause that will give the result I want?

Thanks

[avkb03]

So you want to include matching Fornavn, but don't care about the Etternavn?


SQL = "SELECT DISTINCT Etternavn, Fornavn, PersonID, Tittel FROM Person WHERE Fornavn LIKE '"& fn &"%' ORDER BY Etternavn, Fornavn ASC"