vbams-accessms-access-query

Issue with Criteria within access query?

I'm trying to write a query that when run will check if a record is overdue by atleast 10days, and if so display it, if not don't. However i am encountering issues with the criteria field for one of the fields

I've got a custom field that calculates the DueDate and calculates if it is currently 10 or more days overdue. The expressions are as follows:

DueDate:

DueDate: DateAdd("d",Switch([Priority]="A - 24 hours",1,[Priority]="A- 24 hours",1,[Priority]="B - 72 hours",3,[Priority]="B- 72 hours",3,[Priority]="C - 5 working days",5,[Priority]="C- 5 days",5,[Priority]="D > 5 working days",14,[Priority]="D > 5 days",14,[Priority]="0-Urgent",-365),[Received])

OverdueBy10:

OverdueBy10: IIf([Priority]="0-Urgent",IIf(DateDiff("d",[DueDate],Date())-365>=10,"Yes",""),IIf(DateDiff("d",[DueDate],Date())>=10,"Yes",""))

For context, i have the due date take 365 days from Urgent samples so it filters them to the top for another form. I believe i have compensated for this in the OverdueBy10 expression, if not please correct me.

My Query is structured as shown in the image below:

QueryStructure

SQL of the above Image:

SELECT [Sample Submission].[Lab Batch ID]
    ,[Sample Submission].[IRIS Batch ID]
    ,[Sample Submission].Requester
    ,[Sample Submission].Samples
    ,[Sample Submission].Runs
    ,[Sample Submission].Method
    ,[Sample Submission].[Instrument Specific]
    ,[Sample Submission].Priority
    ,DateAdd("d", Switch([Priority] = "A - 24 hours", 1, [Priority] = "A- 24 hours", 1, [Priority] = "B - 72 hours", 3, [Priority] = "B- 72 hours", 3, [Priority] = "C - 5 working days", 5, [Priority] = "C- 5 days", 5, [Priority] = "D > 5 working days", 14, [Priority] = "D > 5 days", 14, [Priority] = "0-Urgent", - 365), [Received]) AS DueDate
    ,[Sample Submission].[Drop-Off date]
    ,[Sample Submission].Comments
    ,[Sample Submission].Received
    ,[Sample Submission].Started
    ,[Sample Submission].Finished
    ,IIf([Priority] = "0-Urgent", IIf(DateDiff("d", [DueDate], DATE ()) - 365 >= 10, "Yes", ""), IIf(DateDiff("d", [DueDate], DATE ()) >= 10, "Yes", "")) AS OverdueBy10
FROM [Sample Submission]
WHERE (
        (([Sample Submission].Method) LIKE "*CFPP*")
        AND (([Sample Submission].Received) IS NOT NULL)
        AND (([Sample Submission].Finished) IS NULL)
        )
ORDER BY DateAdd("d", Switch([Priority] = "A - 24 hours", 1, [Priority] = "A- 24 hours", 1, [Priority] = "B - 72 hours", 3, [Priority] = "B- 72 hours", 3, [Priority] = "C - 5 working days", 5, [Priority] = "C- 5 days", 5, [Priority] = "D > 5 working days", 14, [Priority] = "D > 5 days", 14, [Priority] = "0-Urgent", - 365), [Received])
    ,[Sample Submission].Received
    ,[Sample Submission].Started DESC;

When structured exactly like it is seen in the image above it runs perfectly fine. However, it shows every record that meets the defined criteria within the query, and fills in the correct response to the OverdueBy10 expression, as shown below: QueryOutput

I only want it to show the records where the response to the expression OverdueBy10 is Yes. I have attempted to put =Yes, ="Yes", ="* Yes *", Yes and Is Not Null in the criteria for this field and each time i do it asks for an input for the DueDate.

I'm unsure as to why it is asking for this information, can anyone help me understand why, and help with a potential solution, even if the best way forward if to use VBA?

Thanks in advance,

Ben

Solution

  • Unfortunately you can't use calculated query columns (DueDate) for further calculations in the same query (or for sorting, as you see in the SQL).

    To avoid repeating non-trivial calculations over and over, I suggest:

    This sort of thing becomes much easier if you use a server database that supports Common Table Expressions (CTE). There you can do this step by step in one database view.

    Unrelated note: the fact that you need to check for
    Switch([Priority] = "A - 24 hours", 1, [Priority] = "A- 24 hours", 1, etc.
    tells me that you should normalize this column - in the main table this should be a foreign key to the priority lookup table. Not the priority string.