troubleshooting Question

Expression in Access Query Field not showing null results

Avatar of Fliss Rundle
Fliss Rundle asked on
Microsoft Access
11 Comments1 Solution135 ViewsLast Modified:

I have a query as follows:

Field:     meet_date            contactid              Meeting Due (with expression as below)
Table:    TBL_Meetings      TBL_Contacts       n/a
Total:     Last                        Group By              Group By

The meet_date and contactid are linked through key fields in TBL_Contacts and TBL_Meetings via a third table TBL_Meeting Contact.

In TBL_Contacts, there is a grade field cont_grade which is linked to another table which has grades A to F and a contact frequency in days.

On my FRM_contacts form, I have all of the contact details and a sub-form which is populated from the above query, showing the last meeting date and when the next meeting is due using the following expression in Meeting Due:

Meeting Due: IIf([Grade_ID]=5,"No Scheduling",IIf([Grade_ID]=6,"No Scheduling",IIf([Grade_ID] Is Null,"No Grade",[meet_date]+[TBL_ClientGrade]![Contact Frequency])))

This seems to work fine if a grade has been selected for the contact, however, if there is no grade (it's not a mandatory field), it is not even showing the last meeting date, which seems strange, any help, and an explanation of why it isn't working would be gratefully received (if I can understand the issue better, then I'll be more able to figure these things out for myself).  Grade 5 and 6 (E and F) are working as I expect them to, just not if there is no grade at all.

It would also be really helpful if you could point me in the direction of how to get the sub-form to update itself when I change something in the main form.  I've tried various On Event approaches, but so far to no avail.

Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros