Link to home
Create AccountLog in
Avatar of pdvsa
pdvsaFlag for United States of America

asked on

Report Grouping

Experts,

I have this report that groups based on dates (uses a Module).
I need to override this Module grouping based on whether or not a query contains data.

I am not sure best how to do this.  

I have attached a pared down db with only the report and the query and 2 tables. I deleted much of the data.

Please see attached pic and db and open the report.  
Note: the report must be opened in Print Preview as there is a dcount code in the detail format event.  

thank you

 User generated imageEE.accdb
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Here you go.  I just modified the RecordSource of your report and removed the code behind.  I also deleted the query since it wasn't needed.
EE.accdb
Avatar of pdvsa

ASKER

So glad you responded.  I am sure you noticed that the module was authored by you.  Not at computer now though... Talk to you soon...
I actually didn't realize that till you mentioned it.
:-)

Ron
Avatar of pdvsa

ASKER

wow that was nice.   I have noticed that after using my data I also need to add an additional condition on whether a record is marked as YES or "" by accounting for whether or not  tblLetterOfCredit.Amount IS NOT NULL.  If it is NOT NULL then mark it as YES.  Right now, on my side with all my data, there are too many records marked as YES and adding that additional criteria will take care of it.  

Not sure if the following would have to be edited:
LCPending: IIf(Not IsNull([ProjectID]),"Yes")

I dont think I can simply put NOT IS NULL in the tblLetterOfCredit.Amount criteria in the qry design window.  (It removes too many records).

Let me know what you think.  thank you for the help.
Avatar of pdvsa

ASKER

Also, just fyi, not sure if would need to revert back to the Dcount (on that unbound field that is now [LCPending]


=DLookup("'Yes'", "qryLC_NotIssued_ATS", "ID=" & [ID] & "")

and somehow add that condition of tblLetterOfCredit.Amount.
I'm not sure what you mean.  Are you saying that you have more data now that are being marked as yes?  Can you give me an example?
Avatar of pdvsa

ASKER

OK I have attached a modified tblLetterOfCredit.  
For example, lets focus on ID = 312 under "Approaching"
This one should not be marked as YES because the tbLetterOfCredit.Amount is NULL.  

Do you see what I mean?  

fyi: I limited the report to show only unique values because projects to tblLetterOfCredit is one to MANY.  I dont think that will affect anything but just thought would let you know.
EE-Ron-grp-rpt.accdb
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of pdvsa

ASKER

Ron, that works!  I have been testing the data all night.   thank you for sticking with me.  Have a good night.
Your welcome.