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 am still not quite clear on exactly where the data is coming from.

What exactly is "WPC01 COMPL"?
Is it a native Access table?
Is it a linked Access table (linked directly from Excel)?
Is it a query?

And how does the data get into "WPC01 COMPL"?

 

[bwlytkr]

I am still not quite clear on exactly where the data is coming from.

What exactly is "WPC01 COMPL"?
Is it a native Access table?
Is it a linked Access table (linked directly from Excel)?
Is it a query?

And how does the data get into "WPC01 COMPL"?

it is a linked excel table from my pc and this comes directly from a raw data pull I import from one of our databases. It is basically a YTD list of completed tickets

 

[Joe4]

it is a linked excel table from my pc and this comes directly from a raw data pull I import from one of our databases. It is basically a YTD list of completed tickets

If you open up the linked table in Access, what does it show as the Data Type for these two fields?

 

[bwlytkr]

Thought you may have had something there but, the data type is shown as date/time and the general properties are the same as the other date fields that work with this formula.

 

[bwlytkr]

Here is the formula I am using. Do you know of something I can add to force it to recognize a date. I tried # in various places but, none worked.

PSC DAYS TO COMPLETE: ([WPC01 COMPL].[NextToGoStartDate]-[HAND OFF DATE])

 

[Joe4]

I wonder if your subtraction is changing it from being a date.
Instead of subtraction, try using the "DateAdd" function as shown here: Add to or subtract from date and time values - Microsoft Support

 

[bwlytkr]

I wonder if your subtraction is changing it from being a date.
Instead of subtraction, try using the "DateAdd" function as shown here: Add to or subtract from date and time values - Microsoft Support

already tried this and datediff and no luck with these either

 

[Joe4]

I don't know then. Without having access to your database, it if very difficult to debug.

Often when I encounter these situations, I start over from scratch. Re-create the query, starting with JUST the part giving you trouble. That way you can work on that without any complications from the other stuff. You may also want to start out with a simplified data table, i.e. something with just a few records, so you can discount any data issues.

Good luck!
If you do figure it out, please post back here. I am curious to know what the issue is.

 

[bwlytkr]

thanks, will do. thanks again for everything.

 

[Micron]

If you remain stuck, I don't know if Joe4 would download your files from a drop box but I think I would. You'd have to include everything required to replicate the process (Excel wb and both Access files if the db is split).