Retrieving records by date and accounting for Null entries

dreamweaver, null set, record set

[Niteow1129n]

I am trying to set up a recordset that will return "X" records after a certain date (user-entered). However, the database contains numerous records that do not contain any entries in the date column.

I am using Dreamweaver, and in the advanced recordset dialog box, I have defined four variables, the last variable being the date. Example: I would like a person to be able to search by "Town" (var1) and by "Date"(var4) to return a certain set of records.

The following code sorta works, but not quite way I want:

SELECT *
FROM tbl_final_publicRecordwLust
WHERE Town LIKE 'var1%' AND Address LIKE '%var2%' AND SiteName LIKE '%var3%' AND (InvestigationStartDate >= #var4# OR InvestigationStartDate IS NULL)
ORDER BY SiteName, Address, Town

The problem is if a person enters a date, the database correctly returns the records with the date AND all the records without a date - I don't want the non-dated records. I tried to write an IF statement, but it keeps bombing.

Any suggestions wuld be very appreciated!

[TheNige]

If you don't want the NULL dates then you need to change your query not to bring them back...your current query is saying to bring back dates if they are null. Try the following:

SELECT *
FROM tbl_final_publicRecordwLust
WHERE Town LIKE 'var1%' AND Address LIKE '%var2%' AND SiteName LIKE '%var3%' AND InvestigationStartDate >= #var4# AND InvestigationStartDate IS NOT NULL
ORDER BY SiteName, Address, Town

[Niteow1129n]

I appreciate the quick reply! But...

If I modify per your code, the recordset will not return any records because of the "AND". (I'm assuming you delieberately left out the Parentheses from my code.) From the logic of it, eg "...AND InvestigationStartDate >= #var4# AND InvestigationStartDate IS NULL", the recordset is being asked to return var4 (the date) and the null records. It refuses to give me anything.

I put the parens back in:
SELECT *
FROM tbl_final_publicRecordwLust
WHERE Town LIKE 'var1%' AND Address LIKE '%var2%' AND SiteName LIKE '%var3%' AND (InvestigationStartDate >= #var4# AND InvestigationStartDate IS NULL)
ORDER BY SiteName, Address, Town

...and the result is still the same: no records.

arrgghh.

[Niteow1129n]

oops - I missed the IS NOT.

But it still won't show records if pulled by simply by TOWN. I would need to see all records associated with a TOWN if the date field is left blank. If the TOWN and DATE fields are filled, I want records with TOWN and DATE, no null-value DATE records.