Hi All,
Sorry to ask this question again. I know it here some where but I can't find it. I have a form with two controls for inputting dates into a query: criteria for [CloseDate] Between [Forms]![frmSummeryPeriod].[StartDate] And [Forms]![frmSummeryPeriod].[StopDate]. Works great, unless CloseDate is blank, and you want to see the open records only. If CloseDate is blank it means that the pond has not been drained. If StartDate and StopDate are left blank I would like Access to return the records in which CloseRecord is null.

Thanks again

-Pete

OR [CloseDate] Is Null

Great that works!

I did not know exactly where to put that line so this is how I got it to work
WHERE (((tblFillDate.CloseDate) Between Forms!frmSummeryPeriod.StartDate And Forms!frmSummeryPeriod.StopDate)) Or (((IsNull(tblFillDate.CloseDate))<>False));

This works in this one query, but when I tried to add the OR statement into other queries that are built on the same tables I get this error

"The LEVEL clause contains a reserve word or argument that is misspelled of missing or punctuation is not correct. "

If this is so why would this exact code work in one query and not another?

Thanks again

-Pete

WHERE (((tblFillDate.CloseDate) Between Forms!frmSummeryPeriod.StartDate And Forms!frmSummeryPeriod.StopDate) Or (tblFillDate.CloseDate) Is Null);

Thanks RuralGuy, I think I have narrowed down the problem. The difference between the querys (the one that works and the ones that don't) is summation. When I cut and past your line into the summation querys I get the LEVEL error message above. Here is the whole query.

SELECT tblFillDate.SpeciesID, tblDailyFeedData.FeedID, tblFeed.FeedSize, Sum(tblDailyFeedData.Amount) AS SumOfAmount, tblFeed.Cost, Sum([Amount]*[Cost]) AS [Size Total]
FROM tblFillDate INNER JOIN (tblFeed INNER JOIN tblDailyFeedData ON tblFeed.FeedID = tblDailyFeedData.FeedID) ON tblFillDate.FillID = tblDailyFeedData.FillID
WHERE (((tblFillDate.CloseDate) Between Forms!frmSummeryPeriod.StartDate And Forms!frmSummeryPeriod.StopDate) Or (tblFillDate.CloseDate) Is Null);
GROUP BY tblFillDate.SpeciesID, tblDailyFeedData.FeedID, tblFeed.FeedSize, tblFeed.Cost;


Does this have to do with the fact that in these summations [CloseDate] is set to "where"

Thanks for all your help

-Pete

Do you remove the ; after Null); in the WHERE clause?

Nope! That was it!

Works great now.

Thanks

-Pete

OK spoke too soon. It does work, if the date controls are left blank than I get the records where CloseDate is blank, but when dates are entered I get both records between the dates and record with a blank CloseDate. So I changed Or to XOr and that got rid of all the records were CloseDate is null even when the Start and StopDate are null. Is XOr not allowed in SQL?

Thanks again

-Pete

Edited by: Catfishman on Tue Feb 7 13:51:06 EST 2006.

I don't believe XOR is supported and I can't think of a work around. I misread your first post, sorry. Right now all I see is two separate queries to get the results you want.