We help IT Professionals succeed at work.

Syntax Error: incomplete query clause.

Jay Williams
Jay Williams asked
on
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.
Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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?

Author

Commented:
1-1; yes, PID is the primary key.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
and what isn't working?  Are you getting any kind of error message?  or is it simply not updating the fields?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Is there any chance that some of the Date fields in ProcessingT are NULL?

Author

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.
Top Expert 2016
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();

Author

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, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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".

Author

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!