Question SQL Select WHERE IS NULL


New member
Sep 4, 2016
Programming Experience

I have 5 textboxes:
  • First Name
  • Last Name
  • User Name
  • Date From
  • Date To

I want it to get records from the database based on what the user input is.

My current SQL query is:

@"SELECT * FROM NewRecord WHERE (@FirstName IS NULL OR FirstName LIKE @FirstName + '%') AND (@LastName IS NULL OR LastName LIKE @LastName + '%') AND (@Username IS NULL OR Username LIKE @Username + '%') AND (@StartDate IS NULL AND @EndDate IS NULL OR Date BETWEEN @StartDate AND @EndDate)";

The problem I have is that it only comes back with records if I type a To and From date even though they are NULL.

I cant see any problem with the syntax of my SQL. I was wondering if anyone here can recognise where I am going wrong?

Thank you
You're missing some very important parentheses. This part:
(@StartDate IS NULL AND @EndDate IS NULL OR Date BETWEEN @StartDate AND @EndDate)
should be this:
((@StartDate IS NULL AND @EndDate IS NULL) OR Date BETWEEN @StartDate AND @EndDate)
Hmmm... after posting that, I'm now having second thoughts. I guess give it a go and see if it does the trick.
No that didn't work either :(

I've also tried:

(@StartDate IS NULL OR Date >= @StartDate) AND (@EndDate IS NULL OR Date <= @EndDate)

But still get the same result of being forced to enter the date to get results.
Top Bottom