Can I set criteria to inclu Null Date Values to our monthly report? 1

[LDP]

Hello:

I have a monthly report based on a query , grouped by submittal number, that sorts documents by the date they were responded to. The criteria of the SDSRDate (Response date) is set to filter only the previous months responses. The report is grouped by SD number (submitted transmittal number).

However, I would also like to return from the query all those documents that were received in the previous month but not responded to. The response date field for those records would be a null or empty field.

Can I use a "where" statement in the criteria under the received field (SDDateR) that would include a previous month filter but only lists those dates that have null values in the SDSRDate field?

I can't seem to include in my response date (SDSRDate) filtering criteria a statement that returns the previous months response date records whose date values are null (empty) as well.

I would like to include in the report, documents that have not been responded to or are,in other words,outstanding.

I use the following to limit the report to the previous months records. Can I append to this statement to include nulls?

Between DateSerial(Year(Date()),Month(Date()),1) And DateAdd("d",-1,DateSerial(Year(Date()),Month(DateAdd("m",-1,Date())),1))


Thank you for your time and help in advance.

 

[PWise]

a simple or should do it
or (SDSRDate is null and (SDSRDate <DateSerial(Year(Date()),Month(Date()),1)))

 

[LDP]

Thank you pwise

However..

I get a "Data Type Misatch in criteria expression" when I place "IsNull" in the 'or' field in the query design page.
also when I use the 'and' statement to add the isnull in the criteria expression I have nothing returned in the report and all my count/sum boxes in the report footer list #error.

Any ideas?

 

[PWise]

this is a query that i use that returns all Activities that
were rssolved last month [DateResolved]

or
were not resolved but were not created this month [ActionDate]

SELECT CustomerID,ActionID, ActionDate, DateResolved
FROM Activities
WHERE DateResolved Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1)
OR
(ActionDate<DateSerial(Year(Date()),Month(Date()),1) AND DateResolved Is Null)

 

[LDP]

Thanks pwise

I was using the 'or' statement the wrong way,
ie. just adding ="Is Null" .

Thank you, I'm sure this format will work.

 

[LDP]

Pwise

Your Activities query is almost identical to what I'm after.
This must be another common report function of a database I would think. I can get the correct month but not the null values (no response date)of all previous months besides the current.

Could you take a a quick look at my code? Is it a problem there is a inner join?

Thanks again pwise

Monthly Shop Drawing Query

SDDateR = Date Received
SDSRD = Date Responded

SELECT SD#, DwgNo, RevNo, .SDTitle, .SDDateR, .SDSRD, DateDiff("y",[SDDateR],[SDSRD]) AS DaysElapsed, [Shop Drawings].SDComment
FROM ([Shop Drawing Table] INNER JOIN [Shop Drawing Tracking] ON [Shop Drawing Table].[SD#] = [Shop Drawing Tracking].[SD#]) INNER JOIN [Shop Drawings] ON [Shop Drawing Table].[SD#] = [Shop Drawings].[SD#]

WHERE ((([Shop Drawing Tracking].SDSRD) Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1))) OR ((("SDDateR")<DateSerial(Year(Date()),Month(Date()),1)) AND (("SDSRD") Is Null));

 

[PWise]

what data type is SDDateR

 

[PWise]

on second thought change
((("SDDateR")<DateSerial(Year(Date()),Month(Date()),1)) AND (("SDSRD") Is Null)); to
((([SDDateR])<DateSerial(Year(Date()),Month(Date()),1)) AND (([SDSRD]) Is Null));

 

[LDP]

pwise

Thank you pwise. It ran with some strange results.

It picks up every record from last month to the first month (over 2 years ago)and if their response date is not April (in this case) it makes their response date a null (a blank field in the report). All response dates in April reported fine. It's like it's first making the set of records received from last month to way back to the beginning and if their response date is not April sets it to a null but still includes it in the report.
The sequence should be to list the response dates that are null then filter for those that are from last month
and back. it's a bit confusing. Wish you could see it run.
Thank you again, pwise.

Here's my latest code:

SDDateR is the Date the document was received in our office.

SELECT [Shop Drawing Table].[SD#], [Shop Drawings].DwgNo, [Shop Drawings].RevNo, [Shop Drawings].SDTitle, [Shop Drawing Table].SDDateR, [Shop Drawing Tracking].SDSRD, DateDiff("y",[SDDateR],[SDSRD]) AS DaysElapsed, [Shop Drawings].SDComment
FROM ([Shop Drawing Table] INNER JOIN [Shop Drawing Tracking] ON [Shop Drawing Table].[SD#]=[Shop Drawing Tracking].[SD#]) INNER JOIN [Shop Drawings] ON [Shop Drawing Table].[SD#]=[Shop Drawings].[SD#]
WHERE ((([Shop Drawing Tracking].SDSRD) Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1))) OR ((([SDDateR])<DateSerial(Year(Date()),Month(Date()),1)) AND (([SDSRD]) Is Null));

 

[PWise]

it makes their response date a null (a blank field in the report).
do you mean that in the table ther is a date value and on the report it prints a null??

 

[LDP]

yes all other response dates, beside the ones in April,
were blank in the report. I call a blank field in the report a null which is incorrect because the field is not null in the table.
It grabbed all the received dates prior to this month and
reported everything, except for all the response dates of
documents prior to April (one month ago).

I'm thinking I should look for blank fields in the document
status (SDComment)instead of a date field. SDCommnet is the document status of "Approved" , "Rejected", "Resubmit" etc. If there is no status(SDComment),given to the document, obviously it's still outstanding. Would "is null" or an empty string "" have better results with text instead of dates?
Pwise wish you could witness the code and report.
any ideas?
Thanks