473,320 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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:
Expand|Select|Wrap|Line Numbers
  1. Function IsExt(varBaseID As String,
  2. dtCOEndDate As Date, dtActEndDate As Date) As String
  3.     If (IsBaseIDInPending(varBaseID) = True) Then
  4.         If (dtCOEndDate > dtActEndDate) Then
  5.             IsExt = "EXT"
  6.         Else
  7.             IsExt = "Not EXT"
  8.         End If
  9.     Else 'not needed, just to replace the #Error for now.
  10.         IsExt = "Blank"
  11.     End If
  12. End Function
  13.  
Any help would be appreciated.
Sep 29 '10 #1
3 1112
MMcCarthy
14,534 Expert Mod 8TB
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.
Sep 29 '10 #2
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.      IsExt([ContingentStaffActive].[Base Order ID],[ContingentStaffPending].[End Date],[ContingentStaffActive].[End Date]) AS [CO an Ext?],
  3.      ContingentStaffPending.[Contingent Staff Status] AS [CO Status], 
  4.      ContingentStaffPending.[End Date] AS [CO End Date],
  5.      ContingentStaffPending.ID AS [CO ID],
  6.      CalcAmtLeft([ContingentStaffActive].[Estimated Contingent Staff Amount],[ContingentStaffActive].[Invoiced Amount]) AS [Amount Left On Order], 
  7.      [ContingentStaffActive].[End Date]-[ContingentStaffActive].[Report Run Date] AS [Days Left], 
  8.      ContingentStaffActive.[Report Run Date], 
  9.      ContingentStaffActive.[Contingent Staff Name], 
  10.      ContingentStaffActive.[Contractor/Consultant], 
  11.      ContingentStaffActive.[Contingent Staff ID],
  12.      ContingentStaffActive.[User ID], 
  13.      ContingentStaffActive.[Contingent Staff Status], 
  14.      ContingentStaffActive.[Start Date], 
  15.      ContingentStaffActive.[End Date], 
  16.      ContingentStaffActive.[Hiring Manager Name], 
  17.      ContingentStaffActive.[Manager Employee ID], 
  18.      ContingentStaffActive.[Manager Email], 
  19.      ContingentStaffActive.Location, 
  20.      ContingentStaffActive.[Position Group], 
  21.      ContingentStaffActive.[Position Class], 
  22.      ContingentStaffActive.[Supplier Name],
  23.      ContingentStaffActive.[Shift Information], 
  24.      ContingentStaffActive.ID, ContingentStaffActive.Name, 
  25.      ContingentStaffActive.[Operating Company], 
  26.      ContingentStaffActive.Organization, 
  27.      ContingentStaffActive.Category, 
  28.      ContingentStaffActive.[Estimated Contingent Staff Amount], 
  29.      ContingentStaffActive.[Approved Contingent Staff Amount], 
  30.      ContingentStaffActive.[Invoiced Amount], 
  31.      ContingentStaffActive.Currency, 
  32.      ContingentStaffActive.[PO ID], 
  33.      ContingentStaffActive.[Project Code], 
  34.      ContingentStaffActive.[GL Account], 
  35.      ContingentStaffActive.[Cost Center], 
  36.      ContingentStaffActive.[Regular Rate], 
  37.      ContingentStaffActive.[Overtime Rate], 
  38.      ContingentStaffActive.[Second Overtime Rate], 
  39.      ContingentStaffActive.[MA Regular Bill Rate], 
  40.      ContingentStaffActive.[MA Overtime Rate], 
  41.      ContingentStaffActive.[MA Second Overtime Rate], 
  42.      ContingentStaffActive.[Termination Date], ContingentStaffActive.[Termination Reason]
  43. FROM ContingentStaffActive LEFT JOIN ContingentStaffPending ON ContingentStaffActive.[Base Order ID] = ContingentStaffPending.[Base Order ID];
  44.  
Oct 1 '10 #3
MMcCarthy
14,534 Expert Mod 8TB
OK try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT IIf(IsNull([ContingentStaffPending].[End Date]),"",
  2.      IsExt([ContingentStaffActive].[Base Order ID],[ContingentStaffPending].[End Date],[ContingentStaffActive].[End Date])) AS [CO an Ext?],
  3.      ContingentStaffPending.[Contingent Staff Status] AS [CO Status], 
  4.      ContingentStaffPending.[End Date] AS [CO End Date],
  5.      ContingentStaffPending.ID AS [CO ID],
  6.      CalcAmtLeft([ContingentStaffActive].[Estimated Contingent Staff Amount],[ContingentStaffActive].[Invoiced Amount]) AS [Amount Left On Order], 
  7.      [ContingentStaffActive].[End Date]-[ContingentStaffActive].[Report Run Date] AS [Days Left], 
  8.      ContingentStaffActive.[Report Run Date], 
  9.      ContingentStaffActive.[Contingent Staff Name], 
  10.      ContingentStaffActive.[Contractor/Consultant], 
  11.      ContingentStaffActive.[Contingent Staff ID],
  12.      ContingentStaffActive.[User ID], 
  13.      ContingentStaffActive.[Contingent Staff Status], 
  14.      ContingentStaffActive.[Start Date], 
  15.      ContingentStaffActive.[End Date], 
  16.      ContingentStaffActive.[Hiring Manager Name], 
  17.      ContingentStaffActive.[Manager Employee ID], 
  18.      ContingentStaffActive.[Manager Email], 
  19.      ContingentStaffActive.Location, 
  20.      ContingentStaffActive.[Position Group], 
  21.      ContingentStaffActive.[Position Class], 
  22.      ContingentStaffActive.[Supplier Name],
  23.      ContingentStaffActive.[Shift Information], 
  24.      ContingentStaffActive.ID, ContingentStaffActive.Name, 
  25.      ContingentStaffActive.[Operating Company], 
  26.      ContingentStaffActive.Organization, 
  27.      ContingentStaffActive.Category, 
  28.      ContingentStaffActive.[Estimated Contingent Staff Amount], 
  29.      ContingentStaffActive.[Approved Contingent Staff Amount], 
  30.      ContingentStaffActive.[Invoiced Amount], 
  31.      ContingentStaffActive.Currency, 
  32.      ContingentStaffActive.[PO ID], 
  33.      ContingentStaffActive.[Project Code], 
  34.      ContingentStaffActive.[GL Account], 
  35.      ContingentStaffActive.[Cost Center], 
  36.      ContingentStaffActive.[Regular Rate], 
  37.      ContingentStaffActive.[Overtime Rate], 
  38.      ContingentStaffActive.[Second Overtime Rate], 
  39.      ContingentStaffActive.[MA Regular Bill Rate], 
  40.      ContingentStaffActive.[MA Overtime Rate], 
  41.      ContingentStaffActive.[MA Second Overtime Rate], 
  42.      ContingentStaffActive.[Termination Date], ContingentStaffActive.[Termination Reason]
  43. FROM ContingentStaffActive LEFT JOIN ContingentStaffPending ON ContingentStaffActive.[Base Order ID] = ContingentStaffPending.[Base Order ID];
  44.  
If the Pending end date is null then it should just return an empty string "".
Oct 4 '10 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

13
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...
5
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...
3
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...
2
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
5
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:...
5
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...
5
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...
2
by: ltansey | last post by:
Simple Join Statement Help -------------------------------------------------------------------------------- I have a problem concerning a join statement query, the two table are called...
4
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...
1
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.