Question SQL Select WHERE IS NULL

[Kitty2016]

Hi,

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

 

[jmcilhinney]

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.

 

[Kitty2016]

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.