Access Query Formula Question

[bwlytkr]

I built this query formula and it looks like it works fine with the exception of the last statement. The cells populate yes if the lu company is yes and >= the date in the formula and no in the cell if it is less than the date. However, it won't populate NO if the LU company is Blank as the last statement says. any thouths on why this is or what I need to do to correct this.

PSC Ticket: IIF([LU Company]="YES",IIF([FL NTG].[NextToGoStartDate]>= #6/29/21#,"YES","NO"), IIF([LU Company]="","NO")

 

[Joe4]

Try this:

PSC Ticket: IIF((([LU Company]="YES") AND ([FL NTG].[NextToGoStartDate]>= #6/29/21#)),"YES","NO")

 

[bwlytkr]

tried that already and it only populates NO if the cell is less than the date and the lu company is yes. If the lu company is blank it will not populate NO

 

[Joe4]

It seems to work fine for me.

For the sake to show the formula works, I put both fields in the same Table, and then wrote a query on the table with that calculation, and I got the expected results, as shown below:

I noticed that in your formula, that the "NextToGoStartDate" field is prefaced by a table name, whole the "LU Company" field is not, which leads me to believe that you are dealing with two different tables. So, how exactly are you joining these tables? In a query? What is the SQL code of that query?

I suspect that your problem probably lies there, and not in the calculation.

 

[welshgasman]

LU Company is probably Null?, so use the NZ() function

 

[bwlytkr]

I tried the NZ function as well and it didn't work either. I do have 7 tables involved with the Query. however, there is only one tie from the pxfr02 Main Table to the PSC INFO (2) table. The PoleOwner name from the PXFR02 table has no other connection to it other than the PoleOwner in the PSC INFO (2) table Here is a copy of the SQL used.

SELECT IIf(Left([Pxfr_TaskOwner],3)="AFO","AFO",IIf(Left([Pxfr_TaskOwner],4)="tech","Construction",IIf(Left([Pxfr_TaskOwner],3)="con","Construction",IIf(Left([Pxfr_TaskOwner],3)="cmc","CMC",IIf(Left([Pxfr_TaskOwner],3)="eng","Engineering",IIf(Left([Pxfr_TaskOwner],3)="LCE","Construction",IIf(Left([Pxfr_TaskOwner],4)="SCTR","SourceOne"))))))) AS [Responsible Party], [PXFR02 NTG].TicketId, [PXFR02 NTG].StepId, [PXFR02 NTG].MostRecentEvent_ID, [SE Bid Zone].[BID ZONE], IIf([DaysAged]<30,"G- <30 Days",IIf([DaysAged]>=30 And [DaysAged]<=60,"F - 30-60 Days",IIf([DaysAged]>60 And [DaysAged]<=90,"E - 60-90 Days",IIf([DaysAged]>90 And [DaysAged]<=180,"D - 90-180 Days",IIf([DaysAged]>180 And [DaysAged]<=240,"C - 180-240 Days",IIf([DaysAged]>240 And [DaysAged]<=365,"B- 240-365 Days",IIf([DaysAged]>365,"A - >365 Days"))))))) AS [NTG AGE BUCKET], [PXFR02 NTG].District, WLS07.[Const Area Manager], WLS07.[Const Scheduler], WLS07.[Const Placing Network Manager], WLS07.[DBT Area Manager], WLS07.[Primary WC Eng], [FL NTG].OwnerName, [FL NTG].CreatedBy, [PSC INFO (2)].[LU Company], IIf([LU Company]="YES",IIf([FL NTG].[NextToGoStartDate]>=#6/29/2021#,"YES","NO")) AS [PSC Ticket], IIf(Left([PXFR02 NTG]![JobType],3)="Not","Not In Plan",IIf(Left([PXFR02 NTG]![JobType],4)="Pull","Not In Plan",IIf(Left([PXFR02 NTG]![JobType],3)="set","Not In Plan",IIf(Left([PXFR02 NTG]![JobType],3)="DIS","Not In Plan","In Plan")))) AS [In Plan-Not In Plan], [PXFR02 NTG].TicketNumber, [PXFR02 NTG].PriorityCode, [PXFR02 NTG].PriorityCategory, [PXFR02 NTG].PriorityNote, [PXFR02 NTG].JobType, [PXFR02 NTG].JobIdentifier, [PXFR02 NTG].TaskId, [FPL NOA].[FPL ABANDOMENT], [PXFR02 NTG].job_nm, [PXFR02 NTG].workIds, IIf(Left([PXFR02 NTG]![JobIdentifier],3)="PWO","PWO",IIf(Left([PXFR02 NTG]![JobIdentifier],6)="SOC PP","SOC PP Survey",IIf(Left([PXFR02 NTG]![JobIdentifier],2)="A0","EWO",IIf(Left([PXFR02 NTG]![JobIdentifier],2)="RW","RW REQ'D",IIf(Left([PXFR02 NTG]![JobIdentifier],3)="DIS","NEED SURVEY",IIf(Left([PXFR02 NTG]![JobIdentifier],5)="CONST","CONST REVISIT",IIf(Left([PXFR02 NTG]![JobIdentifier],4)="DROP","DROP ONLY",IIf(Left([PXFR02 NTG]![JobIdentifier],3)="ENG","REF TO ENGINEER",IIf(Left([PXFR02 NTG]![JobIdentifier],3)="Ref","REF TO ENGINEER",IIf(Left([PXFR02 NTG]![JobIdentifier],3)="Reo","NEED SURVEY",IIf(Right([PXFR02 NTG]![JobIdentifier],1)="n" Or Right([PXFR02 NTG]![JobIdentifier],1)="d" Or Right([PXFR02 NTG]![JobIdentifier],1)="b" Or Right([PXFR02 NTG]![JobIdentifier],1)="a" Or Right([PXFR02 NTG]![JobIdentifier],1)="c","EWO","Need Survey"))))))))))) AS [PXFR JOB TYPE], [PXFR02 NTG].Pxfr_Status, [PXFR02 NTG].MostRecentEventName, [PXFR02 NTG].Pxfr_TaskOwner, [PXFR02 NTG].MostRecentEvent, [PXFR02 NTG].MostRecentEvent_Remarks, [PXFR02 NTG].MostRecentEvent_Created_ts, [PXFR02 NTG].AfoTicketNm, [FL NTG].TicketType, [PXFR02 NTG].CfasNumber, [FL NTG].DaysAged, [FL NTG].ResponseRequestDate, [FL NTG].NextToGoStartDate, [FL NTG].ReopenedDate, [FL NTG].UpdatedOn, [FL NTG].ReferenceId, [FL NTG].ServingArea, [FL NTG].Location, [PXFR02 NTG].WC_CLLI, [PXFR02 NTG].HouseNumber, [PXFR02 NTG].StreetName, [FL NTG].CrossStreet, [PXFR02 NTG].AssetId, [FL NTG].PoleNumber, [FL NTG].StepNumberOfPoles, [FL NTG].Latitude, [FL NTG].Longitude, [PXFR02 NTG].Place, [PXFR02 NTG].County, [PXFR02 NTG].State, [FL NTG].Remarks1, Final_JobData1.PendingClose, [PXFR02 NTG].RemainObjHrs, Final_JobData1.StepsTelco, Final_JobData1.StepsContract, Final_JobData1.StepsDetailOpen, Final_JobData1.StepsDetailClosed, Final_JobData1.JobStatus, Final_JobData1.JobStatusOSPCMV, [PXFR02 NTG].JobStatus
FROM ((((([PXFR02 NTG] INNER JOIN WLS07 ON [PXFR02 NTG].WC_CLLI = WLS07.clli) LEFT JOIN [FL NTG] ON [PXFR02 NTG].TicketNumber = [FL NTG].TicketNumber) LEFT JOIN [FPL NOA] ON [PXFR02 NTG].TicketNumber = [FPL NOA].TicketNumber) LEFT JOIN Final_JobData1 ON [PXFR02 NTG].job_nm = Final_JobData1.JobNumber) LEFT JOIN [SE Bid Zone] ON [PXFR02 NTG].WC_CLLI = [SE Bid Zone].CLLI) LEFT JOIN [PSC INFO (2)] ON [PXFR02 NTG].PoleOwner = [PSC INFO (2)].PoleOwner
WHERE ((([PXFR02 NTG].District) Like "FL*"));

 

[Joe4]

If you have 7 tables in there, you should qualify which table "LU Company" is coming from, like you did with the "NextToGoStartDate" field to avoid any confusion/ambiguity.

You got a lot of stuff going on there. These type of things can often be hard to debug because of all the external "noise". I find that one of the best ways to debug it is to simplify things a bit. Start off in a brand new query, with just the two tables involved, if possible (the tables that "Lu Company" and "NextToGoStartDate" are found in). And then add in your calculated field and see if you can get it working. Once you get it working, then you can start to add in the other tables and fields.

 

[bwlytkr]

good thought. I'll give it a try. Thanks for the help!!

 

[Joe4]

You are welcome.

If you cannot get the most basic one to work, please post some samples of your two data tables and your query, so we can try to recreate it on our side and see if we can help you figure it out.