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.
3 1112
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.
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];
-
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 "".
Sign in to post your reply or Sign up for a free account.
Similar topics
by: kieran |
last post by:
Hi,
I have the following SQL statement which is pulling a few details from
a database. As you can see, there is only the one table from which i
am creating a temporary copy.
The reason I do...
|
by: Zeno |
last post by:
Hi.........
Its been awhile since I've touched SQL statements, so I need some help
with writing a JOIN statement to query 3 tables.
The dB has 3 tables with values
Applications...
|
by: Jack Smith |
last post by:
Hello,
I want to be able to view data from 3 tables using the JOIN statement, but
I'm not sure of how to do it. I think i don't know the syntax of the joins.I
imagine this is easy for the...
|
by: kevinjbowman |
last post by:
I have the following query I wrote in MySQL 5.0
Select
eq_employees.empid,
eq_sigreturns.returnid
From
eq_employees
Left Join eq_sigreturns ON eq_employees.empid
=
eq_sigreturns.empid
|
by: RioRanchoMan |
last post by:
I have a forum table where the field Forum_ID of the first thread
corresponds to itself in the field Forum_Ancestor, and 0 (zero) for the
field Forum_Parent when it is the first topic in a thread:...
|
by: Sphenix |
last post by:
------------------------
UPDATE A
SET A.ID = '?' + A.ID
FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON
A.INDEX = B.INDEX
WHERE B.DUP_ID IS NULL
------------------------
seems like update with...
|
by: ltansey |
last post by:
I have a problem concerning a join statement query, the two table are called PublicHouse & PublicHouseBeer, below are the following tables are there attributes;
PublicHouse Table
create table...
|
by: ltansey |
last post by:
Simple Join Statement Help
--------------------------------------------------------------------------------
I have a problem concerning a join statement query, the two table are called...
|
by: linendra |
last post by:
Hi
i am using oracle 8i. when i execute the join statement i got the error message. at the same time when i execute the same code to sql server and ms access the code execute successfully. i...
|
by: teneesh |
last post by:
Here I have a code for a view that has been created by a developer on my team. I am trying to use the very same code to create a view for a different formid/quesid. But I cannot figure out how this...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| | |