Syntax Error: incomplete query clause.

This update query is not working.
UPDATE PickListT INNER JOIN ProcessingT ON PickListT.PID = ProcessingT.PID SET PickListT.Sloc = [ProcessingT].[Sloc], PickListT.NeedByDate = [ProcessingT].[NeedByDate], PickListT.Priority = [ProcessingT].[Priority], PickListT.DaysPastPickDate = Date()-[ProcessingT].[NeedByDate], PickListT.Pcs = [ProcessingT].[Pcs], PickListT.Description = [ProcessingT].[Description], PickListT.Links = [ProcessingT].[Links], PickListT.ResOnly = [ProcessingT].[ResOnly], PickListT.ClosedOnDate = [ProcessingT].[ClosedOnDate], PickListT.WBS = [ProcessingT].[WBS], PickListT.DateEntered = [ProcessingT].[DateEntered], PickListT.HXSlocInv = [ProcessingT].[HXSlocInv], PickListT.SlocLineLocation1 = [ProcessingT].[SlocLineLocation1], PickListT.SlocLineLocation2 = [ProcessingT].[SlocLineLocation2], PickListT.SlocLineLocation3 = [ProcessingT].[SlocLineLocation3], PickListT.LeadTimeDays = [ProcessingT].[NeedByDate]-[ProcessingT].[DateEntered], PickListT.Countdown = [PickListT].[NeedByDate]-Date();

Open in new window

Love some help. I'm clueless.
Jay WilliamsOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
1.  Is this a 1 to 0-or-1 relationship between PickListT and ProcessingT?

2.  Is PID the primary key (or at lease unique) in table PickListT?
Jay WilliamsOwnerAuthor Commented:
1-1; yes, PID is the primary key.
Dale FyeOwner, Developing Solutions LLCCommented:
and what isn't working?  Are you getting any kind of error message?  or is it simply not updating the fields?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dale FyeOwner, Developing Solutions LLCCommented:
Is there any chance that some of the Date fields in ProcessingT are NULL?
Jay WilliamsOwnerAuthor Commented:
The ClosedOnDate field is not updating, and yes, there are null values in that field in some of those records.  The database is updated daily by imported macro enabled Excel files (front end).  Those records are to close and not appear in the daily refreshed export sheet.
Rey Obrero (Capricorn1)Commented:
try using the IIF() function in your date fields

UPDATE PickListT INNER JOIN ProcessingT ON PickListT.PID = ProcessingT.PID
 SET PickListT.Sloc = [ProcessingT].[Sloc]
 , PickListT.NeedByDate = [ProcessingT].[NeedByDate]
 , PickListT.Priority = [ProcessingT].[Priority]
 , PickListT.DaysPastPickDate = Date()-[ProcessingT].[NeedByDate]
 , PickListT.Pcs = [ProcessingT].[Pcs]
 , PickListT.Description = [ProcessingT].[Description]
 , PickListT.Links = [ProcessingT].[Links]
 , PickListT.ResOnly = [ProcessingT].[ResOnly]
, PickListT.ClosedOnDate = IIF([ProcessingT].[ClosedOnDate] & ""<>"",[ProcessingT].[ClosedOnDate],Null)
 , PickListT.WBS = [ProcessingT].[WBS]
 , PickListT.DateEntered = [ProcessingT].[DateEntered]
 , PickListT.HXSlocInv = [ProcessingT].[HXSlocInv]
 , PickListT.SlocLineLocation1 = [ProcessingT].[SlocLineLocation1]
 , PickListT.SlocLineLocation2 = [ProcessingT].[SlocLineLocation2]
 , PickListT.SlocLineLocation3 = [ProcessingT].[SlocLineLocation3]
 , PickListT.LeadTimeDays = [ProcessingT].[NeedByDate]-[ProcessingT].[DateEntered]
 , PickListT.Countdown = [PickListT].[NeedByDate]-Date();

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jay WilliamsOwnerAuthor Commented:
Yeah, you guys are onto it.  The null values in that field were the holdup.  When Dale gave me the clue, I ran another separate update on just that field where Not Null.  That worked.  I like your solution, Rey, since that means I can do it in one step instead of two.  Thank you, gentlemen.
Dale FyeOwner, Developing Solutions LLCCommented:
Are none of the [ClosedOnDate] fields updating?  Is the ClosedOnDate field in ProcessingT actually a date field?  Or did you possibly use the Format( ) function to convert it into a date format "mm/dd/yyyy" (which is actually a string), and fail to use the cDate() function to convert it to a date?

I'm not sure what you mean by "Those records are to close and not appear in the daily refreshed export sheet. " This update query will only update the values, it has nothing to do with "not appear".
Jay WilliamsOwnerAuthor Commented:
Yes they are all formatted "mm/dd/yyyy" date fields--and the null values in Access were not being updated with the dated Excel records.  The idea was to turn the whole block of data on the sheet around, and refresh the exported front end with only records "not closed" (null values in the ClosedOnDate field).  As my previous post reflects, you got me pointed in the right direction.  Thanks!  Problem solved!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.