Problem with empty/null records

F

fotoart

hello everyone

I have three fields in a query PROGRAMTITLE, STARTDATE, ENDDATE. al
of them from one table.

Sometimes there might be no enddates for events which means the
continue for an unspecified period or continuing everyday.

My problems is when i ask the query to select events happening on
particular day its ignoring the events with empty/null enddat
records.

How can i make a query so that it selects all the records (even nulls
if it matches the startdate.

hope im clear
any help would be appreciated

ahme
 
G

Gary Walter

Hi fotoart,

I believe you asking for:

WHERE STARTDATE <= #7/12/05#
AND (ENDDATE >= #7/12/05# OR ENDDATE IS NULL)
 
F

fotoart

hello Gary

thanx for the rep.

no. I have already tried that.

when i use IS Null in the OR criteria it keeps giving only the NUL
records in the table.
In my query the user will always supply a STARTDATE and and ENDDATE

And when the query looks for data it has to see what records match th
start date, select all of them and among those records there might b
one with no end dates select them as well.

this is whats my end date criterial is now

<=[Forms]![PrintReports]![cboEndDate]

But when I use Is Null in the in the end date all the records includin
nulls are selected regardless of what the start date is.

if you are interested this is what the SQL code looks like

SELECT programmes.programtitle, daysofweek.dayofweek
programmes.broadcastime, programmes.enddate
FROM programmes INNER JOIN (daysofweek INNER JOIN progDays O
daysofweek.daysofweekID = progDays.daysofweekID) O
programmes.programmesID = progDays.programmesID
WHERE (((programmes.enddate)<=[Forms]![PrintReports]![cboEndDate]) AN
((programmes.service)=[Forms]![PrintReports]![OptionService]) AN
((programmes.startdate)>=[Forms]![PrintReports]![cboStartDate])) O
(((programmes.enddate) Is Null));


hope im clear.

any help would be appreciated

ahme
 
A

Allen Browne

The bracketing in your query is ambiguous.

Try:
WHERE ((programmes.service = [Forms]![PrintReports]![OptionService])
AND (programmes.startdate >= [Forms]![PrintReports]![cboStartDate])
AND ((programmes.enddate <= [Forms]![PrintReports]![cboEndDate])
OR (programmes.enddate) Is Null)));

Note that whatever expressions are represented by a, b, and c:
a AND (b OR c)
does not give the same results as:
(a AND b) OR c
 
R

Rick Brandt

fotoart said:
hello everyone

I have three fields in a query PROGRAMTITLE, STARTDATE, ENDDATE. all
of them from one table.

Sometimes there might be no enddates for events which means they
continue for an unspecified period or continuing everyday.

My problems is when i ask the query to select events happening on a
particular day its ignoring the events with empty/null enddate
records.

How can i make a query so that it selects all the records (even nulls)
if it matches the startdate.

hope im clear
any help would be appreciated

ahmed
Click to expand...

Add another criteria of...

Or ENDATE Is Null
 
Top