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*"));

 

[bwlytkr]

Thanks for the info. I tried qualifying it with the table name but, no luck. not sure what you mean by alias. I am still learning access and I didn't know about tagging the SQL format. I'll keep trying and see what I can figure out.

 

[Joe4]

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

That means that there are more than one tables/queries in your query that have this field name, so you need to prefix the field name with the table/query to indicate which one it is.
Welshgasman shows you how to do that in his post, i.e.
[table name].[field name]

If that still does not work, then tell us what error message you receive then.

 

[bwlytkr]

I got the error to clear by putting the table name as shown below. However, now it is not populating the date in the NextToGoStartDate column when it's populated. I get a #ERROR in the cell when I run the query. However, it does populate the UpdatedOn date as it should when the NextToGoStartDate column is blank. Any Thoughts?

HAND OFF DATE: IIF([WPC01 COMPL].[NextToGoStartDate]<>"",[WPC01 COMPL].[NextToGoStartDate],[WPC01 COMPL].[updatedOn])

 

[Joe4]

Do you have any errors in any of the fields in your underlying tables?
What are the values in those fields where you are getting the error?

 

[bwlytkr]

Do you have any errors in any of the fields in your underlying tables?
What are the values in those fields where you are getting the error?

no errors in the tables and the value in both of these columns are Short Date Fields

 

[bwlytkr]

Do you have any errors in any of the fields in your underlying tables?
What are the values in those fields where you are getting the error?

As info, I just tried using another column called CompletedDate instead of NextToGoStartDate and got the same result with a #ERROR in the cell when I run the query. Like I said the UpdatedOn date populates as it should when the NextToGoStartDate field is blank. I'm stumped on this one

HAND OFF DATE: IIF([WPC01 COMPL].[CompletedDate]<>"",[WPC01 COMPL].[CompletedDate],[WPC01 COMPL].[UpdatedOn])

 

[Joe4]

I don't have Microsfot Access handy at my current location, but see if this makes a difference:

HAND OFF DATE: IIF([WPC01 COMPL].[CompletedDate]>0,[WPC01 COMPL].[CompletedDate],[WPC01 COMPL].[UpdatedOn])

 

[bwlytkr]

I also tried different columns to populate in the first statement and it doesn't populate them either. It looks like it is just not recognizing the first statement to populate if that field is not blank for some reason.

 

[bwlytkr]

That works better. However, it does not recognize dates prior to year 2023 so it populates the UpdatedOn date instead. Thanks again for sticking with me on this.

 

[Joe4]

Can you show me some sample data (of the different cases) and the results you are getting?