Access Formula Error

[bwlytkr]

I could use some help with trying to clear this error. I keep getting this error when I run my query in Access. I've looked at the SQL data and can't see what the issue is. Below is a copy of the SQL statement, the error statement and the formula I am using. I would appreciate any help on getting this resolved. Thanks, John

ERROR:
The Specified Field (NextToGoStartDate) could refer to more than one table listed in the FROM clause of your SQL statement

FORMULA
PSC Ticket: IIF([LU COMPANY]="YES",IIF([NextToGoStartDate]>= # 6/29/21 # ,"YES","NO"),IIF([LU COMPANY?]="NO","NO"))

SQL Statement
SELECT [WPC01 COMPL].MonthBeginDate, Format([MonthBeginDate],"mmmm") AS [Completed Month], WLS07.[Const Director], WLS07.[Const Area Manager], WLS07.[Const Placing Network Manager], WLS07.[VIP Lead Const Engrg], WLS07.[DBT Area Manager], WLS07.[Primary WC Eng], [WPC01 COMPL].TicketNumber, [WPC01 COMPL].District, [WPC01 COMPL].SubDistrict, [WPC01 COMPL].WC_CLLI, [WPC01 COMPL].JobType, [WPC01 COMPL].hasJob, [WPC01 COMPL].hasAfoDropsTicket, [WPC01 COMPL].isInPlan, [WPC01 COMPL].isInPlan_old, [WPC01 COMPL].NtgStart_MonthBeginDate, [WPC01 COMPL].completed_MonthBeginDate, [WPC01 COMPL].compType, [WPC01 COMPL].pxfrTicketId, [WPC01 COMPL].pxfrStepId, [WPC01 COMPL].TicketStatus, [WPC01 COMPL].StepStatus, [WPC01 COMPL].StepJobType, [WPC01 COMPL].Member, [WPC01 COMPL].JobIdentifier, [WPC01 COMPL].NextToGoStartDate, [WPC01 COMPL].CompletedDate, [WPC01 COMPL].reopenedDate, [WPC01 COMPL].HouseNumber, [WPC01 COMPL].StreetName, [WPC01 COMPL].Place, [WPC01 COMPL].County, [WPC01 COMPL].State, [WPC01 COMPL].createdByMember, [WPC01 COMPL].updatedOn, [WPC01 COMPL].pulledFromNjuns_ts, [WPC01 COMPL].job_nm, [WPC01 COMPL].JobStatus, [WPC01 COMPL].fldNumStepsOp, [WPC01 COMPL].fldNumStepsCo, [WPC01 COMPL].fldLastWorkOnJob_dt, [WPC01 COMPL].Construction_Work_Comp_Dt, [WPC01 COMPL].JobAge_From_orig_approval_dt_Interval, [WPC01 COMPL].poleOwner, [WPC01 COMPL].FOK_Remarks, [WPC01 COMPL].FOK_CreatedBy, [FL COMP].NumberOfPoles, [FL COMP].Name, IIf([isinplan]=Yes,"IN PLAN","NOT IN PLAN") AS [IN PLAN/NOT IN PLAN], [WPC01 COMPL].compType, [FPL NOA].[FPL ABANDOMENT], [PSC INFO].[LU COMPANY], IIf([LU COMPANY]="YES",IIf([NextToGoStartDate]>=#6/29/2021#,"YES","NO"),IIf([LU COMPANY?]="NO","NO")) AS [PSC Ticket]
FROM ((([WPC01 COMPL] LEFT JOIN WLS07 ON [WPC01 COMPL].WC_CLLI = WLS07.CLLI) LEFT JOIN [FL COMP] ON [WPC01 COMPL].TicketNumber = [FL COMP].TicketNumber) LEFT JOIN [FPL NOA] ON [WPC01 COMPL].TicketNumber = [FPL NOA].TicketNumber) LEFT JOIN [PSC INFO] ON [WPC01 COMPL].poleOwner = [PSC INFO].PoleOwner
WHERE (((WLS07.[Const Director]) Like "frady*" Or (WLS07.[Const Director]) Like "Jack*" Or (WLS07.[Const Director]) Like "deap*"));

 

[Joe4]

I see multiple issues with that formula, specifically the middle part in red:

PSC Ticket: IIF([LU COMPANY]="YES",IIF([NextToGoStartDate]>= # 6/29/21 # ,"YES","NO"),IIF([LU COMPANY?]="NO","NO"))

First, get rid of the spaces between the # and your date.
Second, you cannot have both the TRUE and FALSE clauses populated in your middle IF, and THEN have another IF after it. That is two many FALSE clauses.

Is this what you are looking for?

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

If you explain the exact logic you want to incorporate in this formula.

 

[bwlytkr]

worked great. thanks for the help

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[bwlytkr]

I have another question. I'm looking for a access formula to do the same thing this formula does in excel. Basically I need the query to populate the date in column ab60 if one is populate and if not populate the date in column ak60. Column ab is labeled NextToGoStartDate and column AK is labeled updatedOn. Any help would be appreciated. Thanks, John


=IF(NOT(ISBLANK(AB60)),AB60,AK60)

 

[Joe4]

I have another question. I'm looking for a access formula to do the same thing this formula does in excel. Basically I need the query to populate the date in column ab60 if one is populate and if not populate the date in column ak60. Column ab is labeled NextToGoStartDate and column AK is labeled updatedOn. Any help would be appreciated. Thanks, John


=IF(NOT(ISBLANK(AB60)),AB60,AK60)

Maybe something like:

IIF([NextToGoStartDate]<>"",[NextToGoStartDate],[updateOn])

 

[bwlytkr]

I tried the formula and I keep getting the same error as on the original issue. I verified the column names and everything is correct. Any thoughts?

 

[Joe4]

I tried the formula and I keep getting the same error as on the original issue. I verified the column names and everything is correct. Any thoughts?

What exactly does the error message say?

 

[bwlytkr]

Sorry for the delay. The error is;

The Specified Field (NextToGoStartDate) could refer to more than one table listed in the FROM clause of your SQL statement

 

[welshgasman]

PMFJI,
Try qualifying it with the table name or give one of them an alias?

It would also help if you had formatted your SQL and used tags.?

SELECT [wpc01 compl].monthbegindate,
       Format([monthbegindate], "mmmm")                                  AS
       [Completed Month],
       wls07.[const director],
       wls07.[const area manager],
       wls07.[const placing network manager],
       wls07.[vip lead const engrg],
       wls07.[dbt area manager],
       wls07.[primary wc eng],
       [wpc01 compl].ticketnumber,
       [wpc01 compl].district,
       [wpc01 compl].subdistrict,
       [wpc01 compl].wc_clli,
       [wpc01 compl].jobtype,
       [wpc01 compl].hasjob,
       [wpc01 compl].hasafodropsticket,
       [wpc01 compl].isinplan,
       [wpc01 compl].isinplan_old,
       [wpc01 compl].ntgstart_monthbegindate,
       [wpc01 compl].completed_monthbegindate,
       [wpc01 compl].comptype,
       [wpc01 compl].pxfrticketid,
       [wpc01 compl].pxfrstepid,
       [wpc01 compl].ticketstatus,
       [wpc01 compl].stepstatus,
       [wpc01 compl].stepjobtype,
       [wpc01 compl].member,
       [wpc01 compl].jobidentifier,
       [wpc01 compl].nexttogostartdate,
       [wpc01 compl].completeddate,
       [wpc01 compl].reopeneddate,
       [wpc01 compl].housenumber,
       [wpc01 compl].streetname,
       [wpc01 compl].place,
       [wpc01 compl].county,
       [wpc01 compl].state,
       [wpc01 compl].createdbymember,
       [wpc01 compl].updatedon,
       [wpc01 compl].pulledfromnjuns_ts,
       [wpc01 compl].job_nm,
       [wpc01 compl].jobstatus,
       [wpc01 compl].fldnumstepsop,
       [wpc01 compl].fldnumstepsco,
       [wpc01 compl].fldlastworkonjob_dt,
       [wpc01 compl].construction_work_comp_dt,
       [wpc01 compl].jobage_from_orig_approval_dt_interval,
       [wpc01 compl].poleowner,
       [wpc01 compl].fok_remarks,
       [wpc01 compl].fok_createdby,
       [fl comp].numberofpoles,
       [fl comp].NAME,
       Iif([isinplan] = yes, "in plan", "not in plan")                   AS
       [IN PLAN/NOT IN PLAN],
       [wpc01 compl].comptype,
       [fpl noa].[fpl abandoment],
       [psc info].[lu company],
       Iif([lu company] = "yes", Iif([nexttogostartdate] >=# 6 / 29 / 2021 # ,
                                 "yes",
                                 "no"), Iif([lu company?] = "no", "no")) AS
       [PSC Ticket]
FROM   ((([wpc01 compl]
          LEFT JOIN wls07
                 ON [wpc01 compl].wc_clli = wls07.clli)
         LEFT JOIN [fl comp]
                ON [wpc01 compl].ticketnumber = [fl comp].ticketnumber)
        LEFT JOIN [fpl noa]
               ON [wpc01 compl].ticketnumber = [fpl noa].ticketnumber)
       LEFT JOIN [psc info]
              ON [wpc01 compl].poleowner = [psc info].poleowner
WHERE  (( ( wls07.[const director] ) LIKE "frady*"
           OR ( wls07.[const director] ) LIKE "jack*"
           OR ( wls07.[const director] ) LIKE "deap*" ));

Converted using https://www.dpriver.com/pp/sqlformat.htm