Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Jan 2005
    Location
    Norway
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to handle empty fields with wildcard

    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!

  • #2
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    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.
    OracleGuy

  • #3
    New to the CF scene
    Join Date
    Jan 2005
    Location
    Norway
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Last edited by krooyv; 01-20-2005 at 03:36 PM.

  • #4
    New to the CF scene
    Join Date
    Jan 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So you want to include matching Fornavn, but don't care about the Etternavn?

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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •