How to perform an if statement, if the join statement is true

[Cynthia Morgan]

I have a table and two queries (one filtering out the active status, and the other filtering out pending status from the table). If an active record has had a change made to it, or if a record has pending status, then the Order id has an extension, so I have a field that contains only the base id.

I have a third query which joins the active query and the pending query via the base id field. It contains all the active records, and only those records from the pending query that matches base id in the active query.

I want to indicate whether the record is pending because its date was extended. I have a function that passes in pending end date and the active end date, compares them and returns the appropriate string. However, when dealing with the active records that do not have matching base ids in the pending table, the function returns #Error. I believe it is because of the value passed in for the pending end date isn't valid/present.

I would like to perform a check that performs the If statement only if the base id is in the pending query or if need be if the value passed in is valid. I've tried checking for null values, the nz()option, and dCount>0. This is the current version of the code:

Function IsExt(varBaseID As String,
dtCOEndDate As Date, dtActEndDate As Date) As String
    If (IsBaseIDInPending(varBaseID) = True) Then
        If (dtCOEndDate > dtActEndDate) Then
            IsExt = "EXT"
        Else
            IsExt = "Not EXT"
        End If
    Else 'not needed, just to replace the #Error for now.
        IsExt = "Blank"
    End If
End Function

Any help would be appreciated.

[MMcCarthy]

Surely you don't want to use this function at all if there is no pending date, therefore the validation should be in the main code before this function is called and only call the function if there is a pending end date present.

Can you post the code where this function is called.

[Cynthia Morgan]

Sorry for the delay, a software upgrade, and its related upheavals at work, shifted this project to second place.

True. Ideally, this function would be called only if there is pending data, but unfortunantly I haven't learned Access well enough to branch far out of the wizards, and I saw no way to handle that until the function was called. I only barely know VBA, mostly as guess work based on what's similar to C++ (previous job) and Java (school work). I think this is all the code I have:

SELECT 
     IsExt([ContingentStaffActive].[Base Order ID],[ContingentStaffPending].[End Date],[ContingentStaffActive].[End Date]) AS [CO an Ext?],
     ContingentStaffPending.[Contingent Staff Status] AS [CO Status], 
     ContingentStaffPending.[End Date] AS [CO End Date],
     ContingentStaffPending.ID AS [CO ID],
     CalcAmtLeft([ContingentStaffActive].[Estimated Contingent Staff Amount],[ContingentStaffActive].[Invoiced Amount]) AS [Amount Left On Order], 
     [ContingentStaffActive].[End Date]-[ContingentStaffActive].[Report Run Date] AS [Days Left], 
     ContingentStaffActive.[Report Run Date], 
     ContingentStaffActive.[Contingent Staff Name], 
     ContingentStaffActive.[Contractor/Consultant], 
     ContingentStaffActive.[Contingent Staff ID],
     ContingentStaffActive.[User ID], 
     ContingentStaffActive.[Contingent Staff Status], 
     ContingentStaffActive.[Start Date], 
     ContingentStaffActive.[End Date], 
     ContingentStaffActive.[Hiring Manager Name], 
     ContingentStaffActive.[Manager Employee ID], 
     ContingentStaffActive.[Manager Email], 
     ContingentStaffActive.Location, 
     ContingentStaffActive.[Position Group], 
     ContingentStaffActive.[Position Class], 
     ContingentStaffActive.[Supplier Name],
     ContingentStaffActive.[Shift Information], 
     ContingentStaffActive.ID, ContingentStaffActive.Name, 
     ContingentStaffActive.[Operating Company], 
     ContingentStaffActive.Organization, 
     ContingentStaffActive.Category, 
     ContingentStaffActive.[Estimated Contingent Staff Amount], 
     ContingentStaffActive.[Approved Contingent Staff Amount], 
     ContingentStaffActive.[Invoiced Amount], 
     ContingentStaffActive.Currency, 
     ContingentStaffActive.[PO ID], 
     ContingentStaffActive.[Project Code], 
     ContingentStaffActive.[GL Account], 
     ContingentStaffActive.[Cost Center], 
     ContingentStaffActive.[Regular Rate], 
     ContingentStaffActive.[Overtime Rate], 
     ContingentStaffActive.[Second Overtime Rate], 
     ContingentStaffActive.[MA Regular Bill Rate], 
     ContingentStaffActive.[MA Overtime Rate], 
     ContingentStaffActive.[MA Second Overtime Rate], 
     ContingentStaffActive.[Termination Date], ContingentStaffActive.[Termination Reason]
FROM ContingentStaffActive LEFT JOIN ContingentStaffPending ON ContingentStaffActive.[Base Order ID] = ContingentStaffPending.[Base Order ID];

[MMcCarthy]

OK try this ...

SELECT IIf(IsNull([ContingentStaffPending].[End Date]),"",
     IsExt([ContingentStaffActive].[Base Order ID],[ContingentStaffPending].[End Date],[ContingentStaffActive].[End Date])) AS [CO an Ext?],
     ContingentStaffPending.[Contingent Staff Status] AS [CO Status], 
     ContingentStaffPending.[End Date] AS [CO End Date],
     ContingentStaffPending.ID AS [CO ID],
     CalcAmtLeft([ContingentStaffActive].[Estimated Contingent Staff Amount],[ContingentStaffActive].[Invoiced Amount]) AS [Amount Left On Order], 
     [ContingentStaffActive].[End Date]-[ContingentStaffActive].[Report Run Date] AS [Days Left], 
     ContingentStaffActive.[Report Run Date], 
     ContingentStaffActive.[Contingent Staff Name], 
     ContingentStaffActive.[Contractor/Consultant], 
     ContingentStaffActive.[Contingent Staff ID],
     ContingentStaffActive.[User ID], 
     ContingentStaffActive.[Contingent Staff Status], 
     ContingentStaffActive.[Start Date], 
     ContingentStaffActive.[End Date], 
     ContingentStaffActive.[Hiring Manager Name], 
     ContingentStaffActive.[Manager Employee ID], 
     ContingentStaffActive.[Manager Email], 
     ContingentStaffActive.Location, 
     ContingentStaffActive.[Position Group], 
     ContingentStaffActive.[Position Class], 
     ContingentStaffActive.[Supplier Name],
     ContingentStaffActive.[Shift Information], 
     ContingentStaffActive.ID, ContingentStaffActive.Name, 
     ContingentStaffActive.[Operating Company], 
     ContingentStaffActive.Organization, 
     ContingentStaffActive.Category, 
     ContingentStaffActive.[Estimated Contingent Staff Amount], 
     ContingentStaffActive.[Approved Contingent Staff Amount], 
     ContingentStaffActive.[Invoiced Amount], 
     ContingentStaffActive.Currency, 
     ContingentStaffActive.[PO ID], 
     ContingentStaffActive.[Project Code], 
     ContingentStaffActive.[GL Account], 
     ContingentStaffActive.[Cost Center], 
     ContingentStaffActive.[Regular Rate], 
     ContingentStaffActive.[Overtime Rate], 
     ContingentStaffActive.[Second Overtime Rate], 
     ContingentStaffActive.[MA Regular Bill Rate], 
     ContingentStaffActive.[MA Overtime Rate], 
     ContingentStaffActive.[MA Second Overtime Rate], 
     ContingentStaffActive.[Termination Date], ContingentStaffActive.[Termination Reason]
FROM ContingentStaffActive LEFT JOIN ContingentStaffPending ON ContingentStaffActive.[Base Order ID] = ContingentStaffPending.[Base Order ID];

If the Pending end date is null then it should just return an empty string "".