Update Query Works Once

[Lorlai]

I'm about to go insane here. I have an update query that works just fine when I run it given one set of data, then gives me a type conversion error when I try it with similar data that has a different filter.



This works:

UPDATE Products 
SET Products.[Worker Headcount Qrt1] = 
DLookUp("AvgQtr1","[Qtr_HeadCount_Worker]","[Product No]=" & Products.[ID]), Products.[Worker Headcount Qrt2] = 
DLookUp("AvgQtr2","[Qtr_HeadCount_Worker]","[Product No]=" & [Products].[ID]), Products.[Worker Headcount Qrt3] = 
DLookUp("AvgQtr3","[Qtr_HeadCount_Worker]","[Product No]=" & [Products].[ID]), Products.[Worker Headcount Qrt4] = 
DLookUp("AvgQtr4","[Qtr_HeadCount_Worker]","[Product No]=" & [Products].[ID]);

This does not:

UPDATE Products 
SET Products.[Manager Headcount Qrt1] =
 DLookUp("AvgQtr1","[Qtr_HeadCount_Manager]","[Product No]=" &  Products.[ID]), Products.[Manager Headcount Qrt2] = 
 DLookUp("AvgQtr2","[Qtr_HeadCount_Manager]","[Product No]=" &  [Products].[ID]), Products.[Manager Headcount Qrt3] =  
DLookUp("AvgQtr3","[Qtr_HeadCount_Manager]","[Product No]=" &  [Products].[ID]), Products.[Manager Headcount Qrt4] =  
DLookUp("AvgQtr4","[Qtr_HeadCount_Manager]","[Product No]=" &  [Products].[ID]);

I have checked that both tables have the same data types, and they do. The only difference between the two is Qtr_HeadCount_Worker counts the number of records associated with "Worker" and Qtr_HeadCount_Manager counts the records associated with "Manager".

Is there something I am missing?

Please help!

Thank you

[NTC]

are you implying that going insane is bad thing?

doubtful you have a syntax error - which is all we can see in the post.

so is the nonworking field actually a lookup field in the table? ..doubtful but just a thought.....if it were then what you see in the table could differ than what is actually bound.....

a sanity check is to back off - not use the existing syntax at all - make a simple UpdateQuery using query design - and do a manual try of it - and then build from there....you might find something along the way.....

hope it helps a little....

[rpeare]

can you supply a sample database?