OK, well here's the latest fun. I am trying to build a query that will return the permits with the Max Received Date and the Max Issued Date or MaxReceived and Null Issued Date, here's what I have:

SELECT tblGenPermInfo.PermitNumber, tblGenPermInfo.GenPermInfoID, Max(tblPermits.DateReceived) AS MaxOfDateReceived, tblPermits.PermitReasonID
FROM tblGenPermInfo INNER JOIN tblPermits ON tblGenPermInfo.GenPermInfoID = tblPermits.GenPermInfoID
GROUP BY tblGenPermInfo.PermitNumber, tblGenPermInfo.GenPermInfoID, tblPermits.PermitReasonID, tblPermits.IssuedDate
HAVING (((tblPermits.PermitReasonID)<>17) AND ((tblPermits.IssuedDate) Is Null)) OR (((tblPermits.PermitReasonID)<>17) AND ((Max(([tblPermits].[IssuedDate])))<>False));


So, in layman's terms I need to see(well, I don't, the person I am doing this for does) the most recently received permit for each permit reason as well as any permits that have been received but never issued...

Of course typing this out made me realize I am looking for the Max Received in both instances and I don't need that...hmmm...this is getting really fun. Hopefully someone here will be able to help me through this one...I am getting lost quick now....

Hi Jason,

I formatted your SQL to make it easier to read:



I do not see that you are reporting IssuedDate ... only using it for criteria...

for permits with a null issue date, how about something like this in the HAVING clause:

Hey neighbor, I will have to give that a shot tomorrow, I'm on the way out the door...hopefully tomorrow you will be available...I am sure I am going to have several questions getting this to work!!

J

Hi Jason,

you're welcome

I'll be around till early afternoon...

Ahh, I am finally back, been a fun day or two...and I don't think I'm getting to it today, but I wanted to touch base with you, let you know it's still an on going project, I just haven't had a chance to get back to it.

Crystal, I am trying to understand the line you gave me:

( Max( nz([tblPermits].[IssuedDate],#1/1/1900#)) <> #1/1/1900# )

The query I submitted was the delineation query basically to limit the records that are going to be returned in the query for the report.

I basically need to see any permit that has a received date, but no issued date and then also the permits with the latest received date where issued date is not null.

I will make a copy of this thing and try to get everything out that isn't needed so I can send you a copy, though that might be difficult...I'll see what I can get drug together.

Thank you!!

J

Holy crap!!! Talk about looking too far into something and missing the obvious answer. There is an expired date for the permits so what I ended up doing, was in the query that pulls all the fields for the report, I set the criteria for ExpiredDate to >=Date() or Is Null

That should logically show all open permits right?? And therefor the current workload for each engineer. So now I just have to sit back, laugh at myself for a little bit, and then finish this report...

Crystal, thank you so much for your time!! I will go no so you can post and share the laugh with me(or at me LOL)

J

"That should logically show all open permits right??"

well, you would know better than I ... but I believe so ...

you're welcome, Jason glad you got it figured out