locked
Updating Zipcode distances but getting field not updateable error? RRS feed

  • Question

  • Hello,

    So I'm trying to create a way to import a customer database and then update the table to include customer distances from three different locations.

    What tables I'm using:

    • ZipCodes database - give all relevent information to all current zipcodes in the U.S. including the distances from the three locations.
    • ZipCalc - calculates and updates ZipCodes with the correct distance calculations
    • CustomerDB - Updates Distance fields with data from the ZipCodes.Distance##### field

    CustomerDB table is created with every field filled in except for the distances since they haven't been calculated yet. And here is the code I'm using to accomplish this update:

    databaseSQL = "UPDATE CustomerDB" & YearTB.Value & " INNER JOIN ZipCalc ON LEFT(CustomerDB" & YearTB.Value & ".ZipCode,5)=ZipCalc.zip_code SET CustomerDB" & YearTB.Value & ".Distance80011 = ZipCalc.Distance80011 WHERE LEFT(CustomerDB" & YearTB.Value & ".ZipCode,5)=ZipCalc.zip_code"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (databaseSQL)
    DoCmd.SetWarnings True
    databaseSQL = "UPDATE CustomerDB" & YearTB.Value & " INNER JOIN ZipCalc ON LEFT(CustomerDB" & YearTB.Value & ".ZipCode,5)=ZipCalc.zip_code SET CustomerDB" & YearTB.Value & ".Distance90810 = ZipCalc.Distance90810 WHERE LEFT(CustomerDB" & YearTB.Value & ".ZipCode,5)=ZipCalc.zip_code"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (databaseSQL)
    DoCmd.SetWarnings True
    databaseSQL = "UPDATE CustomerDB" & YearTB.Value & " INNER JOIN ZipCalc ON LEFT(CustomerDB" & YearTB.Value & ".ZipCode,5)=ZipCalc.zip_code SET CustomerDB" & YearTB.Value & ".Distance48108 = ZipCalc.Distance48108 WHERE LEFT(CustomerDB" & YearTB.Value & ".ZipCode,5)=ZipCalc.zip_code"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (databaseSQL)
    DoCmd.SetWarnings True

    This works for the first two distance updates (i.e. for Distance80011 and Distance90810). However, for some reason, when I try and update the Distance48108 field, it prompts me like so and then gives me an error:

    What could be causing this error?

    Monday, December 10, 2012 2:53 PM

Answers

  • Does the CustomerDB2009 table contain a field named Distance48108?

    (I'd prefer to use a table with fewer fields and more records, say with fields Zip5 and Distance, or Year, Zip5 and Distance)


    Regards, Hans Vogelaar

    • Marked as answer by Peterc13 Monday, December 10, 2012 4:01 PM
    Monday, December 10, 2012 3:40 PM

All replies

  • Does the CustomerDB2009 table contain a field named Distance48108?

    (I'd prefer to use a table with fewer fields and more records, say with fields Zip5 and Distance, or Year, Zip5 and Distance)


    Regards, Hans Vogelaar

    • Marked as answer by Peterc13 Monday, December 10, 2012 4:01 PM
    Monday, December 10, 2012 3:40 PM
  • I feel so dumb. When renaming the field names I used 48104 instead of 48108. Dyslexia doesn't not suit programming. Thank you for your help Hans!
    Monday, December 10, 2012 4:01 PM