Too many arguments in expression

[jvhazelbaker]

Here is the breakdown:

I have an event taking place. The user wants to see where in the process we are with the event. The only way to monitor this is by what completion dates have or have not been entered. I have the following date fields in as many different tables.

[Sample Dispatch Date]
[brand event table].[ship date]
[date of visit]
[date selection made]
[planned bottling date]
[firm bottling date]
[date bottled]
[shipping table].[ship date]

I want the field to produce results that say in generic terms, "if the shipping table ship date is null, then the item hasn't been shipped so show a message that we have bottled but not shipped, but if the date bottled field is null, then show a message that we have established a firm bottling date but nothing has been bottled, but if the firm bottling date is null, then show a message that we have a planned bottling date but not a firm bottling date."

I have tried soooo many different expressions to get this done and nothing is working. Any ideas on how this can be accomplished? Preferably in the query itself as an expression. If not, I'll try anything.

 

[Golom]

You state that these dates are all in different tables so the issues are:

1. Are these dates strictly sequential? That is, if a date is NULL then must it be true that all the dates after it must also be NULL? Similarly, if a date is not NULL then none of the dates before it in your list can be NULL?

2. I assume that there is some key field or fields that relate records in all these different tables.

If the dates are sequential and the fields that you have listed are in tables "A", "B", "C", etc. then

Select IIF(IsNull(A.[Sample Dispatch Date]), "First Date Missing Message",
       IIF(IsNull(B.[Ship Date]), "Second Date Missing Message",
       IIF(IsNull(C.[Date of Visit]), "Third Date Missing Message", 
       etc.,

From ((((((((A 
     LEFT JOIN B ON A.Key = B.Key) 
     LEFT JOIN C ON A.Key = C.Key)
     LEFT JOIN D ON A.Key = D.Key) ... etc. ...

 

[jvhazelbaker]

All of the dates are sequential except for the first two, where it is an either/or situation. In regards to your code, where do I write it exactly? In the query as an expression? I understand the first part of the code, but I am completely confused when it comes to the second part, beginning with 'From ((((((((A' Can you explain this to me if at all possible?