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]

got it to work by using >-1 for some reason. Thanks for all the help. I really appreciated it.

 

[Joe4]

OK, glad you got it working.

 

[bwlytkr]

thanks. for some reason the query is just not seeing this column as a date format. I tried another formula to give me the difference in days between this NextToGoDate columns and another and it does not recognize it as a date format either. I made sure the column format on the import table was correct as well as the Access Query column. It seems no matter what I try I can't get it to recognize the date format. If I use the excel formula to calculate this on the table it works fine so it must be something happening in the conversion to access that is causing it I guess. Never had this issue before.

 

[Joe4]

That explains your errors. But I thought you said that it was a valid date field.

If you go to the underlying table where these fields are stored, what does it show as the Data Type for these two fields?

 

[bwlytkr]

That explains your errors. But I thought you said that it was a valid date field.

If you go to the underlying table where these fields are stored, what does it show as the Data Type for these two fields?

According to the column in my query it is a valid date field as well as in the underlying excel table shows it as a date field. I even did a trim feature on the date column in the underlying excel table and formatted the column to short date and I still get the same result. I can run an excel formula in the underlying excel table and it works fine so, I don't think the issue is there.

 

[Joe4]

So, the data is not in an Access table, but in an underlying Excel table?
Note that TRIM is a STRING function, not a numeric/date one.

Are these columns in Excel where these dates are coming from hard-coded values or the results of formulas?
If you choose one of these cells (say the date is in cell F10 in Excel), then pick some blank cell on your Excel sheet, and enter this formula and see what it returns:
=ISNUMBER(F10)

If that formula returns FALSE, then the entry in cell F10 is NOT a valid date entry in Excel.

 

[bwlytkr]

They are hard coded values. I will run another data pull for the underlying table and try this formula and see what I get.

 

[Joe4]

What do you mean by "data pull"?
Are you importing into an Access table?
Or are you just linking the Excel data?

 

[bwlytkr]

They are hard coded values. I will run another data pull for the underlying table and try this formula and see what I get.

it came back true

 

[bwlytkr]

They are hard coded values. I will run another data pull for the underlying table and try this formula and see what I get.

as info, after I run the query and export it to excel this column is shown as a date column and comes back as true when I run the formula.