My standard advice is to NEVER use special characters in table, field, query,
forms, reports, and most other object names. Just the 123s and ABCs. The only
exception is the _ underscore. That might stop the problem; however, would
take a large rewrite. Something like Rick Fisher's Find and Replace could
help.
Notice that you have a period between [contracts - all2] and the field names
while a ! between [Monthly Update] .
Try this:
UPDATE [contracts - all2]
INNER JOIN [Monthly Update]
ON [contracts - all2]!Contract_Number = [Monthly Update]!Contract_Number
SET
[contracts - all2]![CGI Property Mgt Site] = [Monthly Update]![CGI Region
Name],
[contracts - all2]!Property_Name = [Monthly Update]![Property_NameU],
[contracts - all2]!Property_Address_1 = [Monthly
Update]![Property_Address_1U],
[contracts - all2]!Property_Address_2 = [Monthly
Update]![Property_Address_2],
[contracts - all2]!Property_City = [Monthly Update]![Property_CityU],
[contracts - all2]!Property_State = [Monthly Update]![Property_StateU],
[contracts - all2]!Property_Zip = [Monthly Update]![Property_ZipU],
[contracts - all2]![Property County] = [Monthly Update]![Property CountyU],
[contracts - all2]!Agent_Name = [Monthly Update]![Agent_Name],
[contracts - all2]!Agent_Address_1 = [Monthly Update]![Agent_Address_1],
[contracts - all2]!Agent_Address_2 = [Monthly Update]![Agent_Address_2],
[contracts - all2]!Agent_City = [Monthly Update]![Agent_City],
[contracts - all2]!Agent_State = [Monthly Update]![Agent_State],
[contracts - all2]!Agent_ZIP = [Monthly Update]![Agent_ZIP],
[contracts - all2]!Agent_Telephone = [Monthly Update]![Agent_Telephone],
[contracts - all2]!Agent_FAX = [Monthly Update]![Agent_FAX],
[contracts - all2]!Agent_Email = [Monthly Update]![Agent_Email],
[contracts - all2]!Owner_Name = [Monthly Update]![Owner_Name],
[contracts - all2]!Owner_Address_1 = [Monthly Update]![Owner_Address_1],
[contracts - all2]!Owner_Address_2 = [Monthly Update]![Owner_Address_2],
[contracts - all2]!Owner_City = [Monthly Update]![Owner_City],
[contracts - all2]!Owner_State = [Monthly Update]![Owner_State],
[contracts - all2]!Owner_ZIP = [Monthly Update]![Owner_ZIP],
[contracts - all2]!Owner_Telephone = [Monthly Update]![Owner_Telephone],
[contracts - all2]!Owner_FAX = [Monthly Update]![Owner_FAX],
[contracts - all2]!Owner_Email = [Monthly Update]![Owner_Email],
[contracts - all2]![Expiration Date] = [Monthly
Update]![Contract_Expiration_Date],
[contracts - all2]!Program = [Monthly Update]![Program],
[contracts - all2]!Effective_Date = [Monthly
Update]![Funding_Expiration_Date],
[contracts - all2]!Contract_Start_Date = [Monthly
Update]![Contract_Start_Date];
You could also simplify and shorten by using a table alias or two:
UPDATE [contracts - all2] as C2
INNER JOIN [Monthly Update] as MU
ON [c2]!Contract_Number = [MU]!Contract_Number
SET
[c2]![CGI Property Mgt Site] = [MU]![CGI Region Name],
[c2]!Property_Name = [MU]![Property_NameU],
[c2]!Property_Address_1 = [MU]![Property_Address_1U],
[c2]!Property_Address_2 = [MU]![Property_Address_2],
[c2]!Property_City = [MU]![Property_CityU],
[c2]!Property_State = [MU]![Property_StateU],
[c2]!Property_Zip = [MU]![Property_ZipU],
[c2]![Property County] = [MU]![Property CountyU],
[c2]!Agent_Name = [MU]![Agent_Name],
[c2]!Agent_Address_1 = [MU]![Agent_Address_1],
[c2]!Agent_Address_2 = [MU]![Agent_Address_2],
[c2]!Agent_City = [MU]![Agent_City],
[c2]!Agent_State = [MU]![Agent_State],
[c2]!Agent_ZIP = [MU]![Agent_ZIP],
[c2]!Agent_Telephone = [MU]![Agent_Telephone],
[c2]!Agent_FAX = [MU]![Agent_FAX],
[c2]!Agent_Email = [MU]![Agent_Email],
[c2]!Owner_Name = [MU]![Owner_Name],
[c2]!Owner_Address_1 = [MU]![Owner_Address_1],
[c2]!Owner_Address_2 = [MU]![Owner_Address_2],
[c2]!Owner_City = [MU]![Owner_City],
[c2]!Owner_State = [MU]![Owner_State],
[c2]!Owner_ZIP = [MU]![Owner_ZIP],
[c2]!Owner_Telephone = [MU]![Owner_Telephone],
[c2]!Owner_FAX = [MU]![Owner_FAX],
[c2]!Owner_Email = [MU]![Owner_Email],
[c2]![Expiration Date] = [MU]![Contract_Expiration_Date],
[c2]!Program = [MU]![Program],
[c2]!Effective_Date = [MU]![Funding_Expiration_Date],
[c2]!Contract_Start_Date = [MU]![Contract_Start_Date];
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
GMahar said:
Thanks for your help
Here's the SQL that works:
UPDATE [contracts - all2] INNER JOIN [Monthly Update] ON [contracts -
all2].Contract_Number = [Monthly Update].Contract_Number SET [contracts -
all2].[CGI Property Mgt Site] = [Monthly Update]![CGI Region Name],
[contracts - all2].Property_Name = [Monthly Update]![Property_NameU],
[contracts - all2].Property_Address_1 = [Monthly
Update]![Property_Address_1U], [contracts - all2].Property_Address_2 =
[Monthly Update]![Property_Address_2], [contracts - all2].Property_City =
[Monthly Update]![Property_CityU], [contracts - all2].Property_State =
[Monthly Update]![Property_StateU], [contracts - all2].Property_Zip =
[Monthly Update]![Property_ZipU], [contracts - all2].[Property County] =
[Monthly Update]![Property CountyU], [contracts - all2].Agent_Name = [Monthly
Update]![Agent_Name], [contracts - all2].Agent_Address_1 = [Monthly
Update]![Agent_Address_1], [contracts - all2].Agent_Address_2 = [Monthly
Update]![Agent_Address_2], [contracts - all2].Agent_City = [Monthly
Update]![Agent_City], [contracts - all2].Agent_State = [Monthly
Update]![Agent_State], [contracts - all2].Agent_ZIP = [Monthly
Update]![Agent_ZIP], [contracts - all2].Agent_Telephone = [Monthly
Update]![Agent_Telephone], [contracts - all2].Agent_FAX = [Monthly
Update]![Agent_FAX], [contracts - all2].Agent_Email = [Monthly
Update]![Agent_Email], [contracts - all2].Owner_Name = [Monthly
Update]![Owner_Name], [contracts - all2].Owner_Address_1 = [Monthly
Update]![Owner_Address_1], [contracts - all2].Owner_Address_2 = [Monthly
Update]![Owner_Address_2], [contracts - all2].Owner_City = [Monthly
Update]![Owner_City], [contracts - all2].Owner_State = [Monthly
Update]![Owner_State], [contracts - all2].Owner_ZIP = [Monthly
Update]![Owner_ZIP], [contracts - all2].Owner_Telephone = [Monthly
Update]![Owner_Telephone], [contracts - all2].Owner_FAX = [Monthly
Update]![Owner_FAX], [contracts - all2].Owner_Email = [Monthly
Update]![Owner_Email], [contracts - all2].[Expiration Date] = [Monthly
Update]![Contract_Expiration_Date], [contracts - all2].Program = [Monthly
Update]![Program], [contracts - all2].Effective_Date = [Monthly
Update]![Funding_Expiration_Date], [contracts - all2].Contract_Start_Date =
[Monthly Update]![Contract_Start_Date];
Here's the SQL after it breaks (note that 2 fields work properly - 'CGI
Property Mgt Site' and 'Property County'):
UPDATE [contracts - all2] INNER JOIN [Monthly Update] ON [contracts -
all2].Contract_Number = [Monthly Update].Contract_Number SET [contracts -
all2].[CGI Property Mgt Site] = [Monthly Update]![CGI Region Name],
[contracts-all2].Property_Name = [Monthly Update]!Property_NameU,
[contracts-all2].Property_Address_1 = [Monthly Update]!Property_Address_1U,
[contracts-all2].Property_Address_2 = [Monthly Update]!Property_Address_2,
[contracts-all2].Property_City = [Monthly Update]!Property_CityU,
[contracts-all2].Property_State = [Monthly Update]!Property_StateU,
[contracts-all2].Property_Zip = [Monthly Update]!Property_ZipU, [contracts -
all2].[Property County] = [Monthly Update]![Property CountyU],
[contracts-all2].Agent_Name = [Monthly Update]!Agent_Name,
[contracts-all2].Agent_Address_1 = [Monthly Update]!Agent_Address_1,
[contracts-all2].Agent_Address_2 = [Monthly Update]!Agent_Address_2,
[contracts-all2].Agent_City = [Monthly Update]!Agent_City,
[contracts-all2].Agent_State = [Monthly Update]!Agent_State,
[contracts-all2].Agent_ZIP = [Monthly Update]!Agent_ZIP,
[contracts-all2].Agent_Telephone = [Monthly Update]!Agent_Telephone,
[contracts-all2].Agent_FAX = [Monthly Update]!Agent_FAX,
[contracts-all2].Agent_Email = [Monthly Update]!Agent_Email,
[contracts-all2].Owner_Name = [Monthly Update]!Owner_Name,
[contracts-all2].Owner_Address_1 = [Monthly Update]!Owner_Address_1,
[contracts-all2].Owner_Address_2 = [Monthly Update]!Owner_Address_2,
[contracts-all2].Owner_City = [Monthly Update]!Owner_City,
[contracts-all2].Owner_State = [Monthly Update]!Owner_State,
[contracts-all2].Owner_ZIP = [Monthly Update]!Owner_ZIP,
[contracts-all2].Owner_Telephone = [Monthly Update]!Owner_Telephone,
[contracts-all2].Owner_FAX = [Monthly Update]!Owner_FAX,
[contracts-all2].Owner_Email = [Monthly Update]!Owner_Email, [contracts -
all2].[Expiration Date] = [Monthly Update]!Contract_Expiration_Date,
[contracts-all2].Program = [Monthly Update]!Program,
[contracts-all2].Effective_Date = [Monthly Update]!Funding_Expiration_Date,
[contracts-all2].Contract_Start_Date = [Monthly Update]!Contract_Start_Date;
Thanks again!
Jerry Whittle said:
Access often tries to help by rewriting the SQL statements. This can get ugly
for certain queries. Please post the SQL as it works and after it breaks so
that we can see what is happening.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
My update query contains two tables, fields from one are updated to the
other. The query runs fine until I view it in design view. At this point,
on the bottom, the "field" lines are changed to expressions and the "table"
lines are blank (although the two tables are still up top). When I run the
query after viewing it in design view I'm prompted for parameter values. The
only way the query works after this is if I go into design view and set all
the fields and tables back to their original configuration. I don't know why
this is happening, and I'd appreciate any help.
