Expression in Access Query Field not showing null results

Hi,

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.

Thanks,
Fliss RundleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
If there is no value (NULL), then a link on that field will result in knocking out all records where the value is not filled -- the join can be changed. However, you said this is a form, so presumably, information if being updated -- so it is not good to join in other tables to the source. Better for the source to be the table you want to fill and show corresponding text values in other tables with comboboxes  (or listboxes).

Rather than writing an equation for the Grades, why not create a table with that information? then use a comboboxto show text while collecting a numeric value.

If Grade is not filled, it can then simply be blank (my preference) -- NZ can also be used to supply a value.

> " how to get the sub-form to update itself when I change something in the main form"

what is the relationship between the subform and mainform?
0
Fliss RundleAuthor Commented:
Hi,

Thanks for your comment.  The information for the Grades is in a separate table, which has grade_id, Grade (A, B.. etc.) and frequency in days (30, 90.. etc), I am just referring to it in the expression to calculate what the next meeting date should be, ie if contact A is a grade A customer and Grade A customers should be seen every 30 days, then the next meeting date should be last meeting date + 30 days.  I don't want to show the meeting frequency on the face of my contact form, but I do need to be able to show the last meeting date and the next planned meeting date as calculated by the above.
0
PatHartmanCommented:
Please post your SQL.  It is possible that the problem is you are using an Inner join and you need to use a left join.  For example, If you want to return a count of all orders by customers you would join the customer table to the order table on customerID.  However, if a customer has no orders, he would not exist in the resultset.  To include all customers whether or not they have placed orders, use a left join between customer and order.

Select tblCustomer.CustomerID, tblCustomer.CustomerName, Count(*) as OrderCount
From tblCustomer Left Join tblOrder ON tblCustomer.CustomerID = tblOrder.CustomerID
Group by tblCustomer.CustomerID, tblCustomer.CustomerName;

Having multiple tables in a query does not make it not-updateable.  Generally each form should be designed to update a single table but the RecordSource query might include a number of joins to lookup tables to get reference information.  Using joins to obtain the lookup data is more efficient than other methods such as DLookup()'s  For example, your order form might include a join to the customer table to get customer name and a join to the address table to get billing address.  Neither customer nor billing address will be updated from this form so it is best to lock their controls to prevent accidents but their presence doesn't present any particular problem.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Fliss RundleAuthor Commented:
Thanks Pat,

SQL as follows:

SELECT TOP 1 Last(TBL_Meetings.meet_date) AS LastOfmeet_date, TBL_Contacts.contactid, 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]))) AS [Meeting Due]
FROM (TBL_Meetings INNER JOIN [TBL_Meeting ARMS] ON TBL_Meetings.meet_id = [TBL_Meeting ARMS].contarms_meeting) INNER JOIN ((TBL_ClientGrade INNER JOIN TBL_Contacts ON TBL_ClientGrade.Grade_ID = TBL_Contacts.cont_grade) INNER JOIN [TBL_Meeting Contact] ON TBL_Contacts.contactid = [TBL_Meeting Contact].contmeet_contact) ON TBL_Meetings.meet_id = [TBL_Meeting Contact].contmeet_meeting
GROUP BY TBL_Contacts.contactid, 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])))
HAVING (((TBL_Contacts.contactid)=[Forms]![FRM_contacts]![contactid]));

Open in new window

0
PatHartmanCommented:
Change the join to a left join and change the having to a where.  The WHERE clause is applied before the grouping and will restrict the selection of data.  The HAVING is applied after the grouping and will eliminate grouped rows.  Generally, you use a Having when you want to restrict based on the aggregation of data for example, select only when the SalesTotal is > $5,000.  When the criteria applies to a table field, it is more efficient to use a WHERE to allow the query engine to make use of indexes if any are available.  A HAVING can only look at the data that was selected and grouped.  No indexes are available.

SELECT TOP 1 Last(TBL_Meetings.meet_date) AS LastOfmeet_date, TBL_Contacts.contactid, 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]))) AS [Meeting Due]
FROM (TBL_Meetings Left JOIN [TBL_Meeting ARMS] ON TBL_Meetings.meet_id = [TBL_Meeting ARMS].contarms_meeting) INNER JOIN ((TBL_ClientGrade INNER JOIN TBL_Contacts ON TBL_ClientGrade.Grade_ID = TBL_Contacts.cont_grade) INNER JOIN [TBL_Meeting Contact] ON TBL_Contacts.contactid = [TBL_Meeting Contact].contmeet_contact) ON TBL_Meetings.meet_id = [TBL_Meeting Contact].contmeet_meeting
WHERE (((TBL_Contacts.contactid)=[Forms]![FRM_contacts]![contactid]))
GROUP BY TBL_Contacts.contactid, 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])));
0
Fliss RundleAuthor Commented:
Hi Pat,  thanks for that, I think I follow most of it.
I tried your code above, but am having the same issue.  Might it be that some of the other Inner Joins need to be Left Joins?  I tried changing all of them, but a 'Join expression not supported' error popped up.
0
PatHartmanCommented:
Without a deeper understanding of your schema, I'd just be guessing.  Sometimes, you can isolate an inner or left join if it is causing a problem by putting it into a separate query and then having the main query join the remaining table(2) to the separate query.  If you can post your db, I might be able to figure out what you nee.  Please remove any sensitive information.
0
Fliss RundleAuthor Commented:
Pat, Hi, sorry for the delay in coming back again, please see attached my db. with sensitive data removed.
The-Ridge---desensitised.accdb
0
PatHartmanCommented:
You didn't change the join.  I changed it.

SELECT TOP 1 Last(TBL_Meetings.meet_date) AS LastOfmeet_date, TBL_Contacts.contactid, 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]))) AS [Meeting Due]
FROM (TBL_Meetings LEFT JOIN [TBL_Meeting ARMS] ON TBL_Meetings.meet_id = [TBL_Meeting ARMS].contarms_meeting) INNER JOIN ((TBL_ClientGrade RIGHT JOIN TBL_Contacts ON TBL_ClientGrade.Grade_ID = TBL_Contacts.cont_grade) INNER JOIN [TBL_Meeting Contact] ON TBL_Contacts.contactid = [TBL_Meeting Contact].contmeet_contact) ON TBL_Meetings.meet_id = [TBL_Meeting Contact].contmeet_meeting
WHERE (((TBL_Contacts.contactid)=[Forms]![FRM_contacts]![contactid]))
GROUP BY TBL_Contacts.contactid, 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])));

Just FYI - table level lookups will cause nothing but problems.  They are a crutch for people who can't create a query with a join.  Get rid of them before you find out the problems they cause with SQL and VBA.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fliss RundleAuthor Commented:
Thanks Pat, I must have changed the wrong join or somesuch.  I'll try to tidy things up a little as you suggest, but being mostly self taught, finding the best way of doing things isn't always that easy.
0
PatHartmanCommented:
You're welcome.  Glad it's working now.  Please don't forget to get rid of the table level lookups.  They will ultimately be more trouble than they are worth and the more development you do, the more you will have to change to get rid of them.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.