MsAccess SQL updating tables

I have an old tool that I’m trying to update to a new data source. Everything on the tool seems to be working other than when I try to update specific records based on a load number. Now, this column changed from a NUMBER format, to now a STRING format. That is the only difference.

I have a button on a form that when clicked it runs two separate macro’s. One that runs an update query to the WCL_DATABASE and the other updates the same records in another table (TrafficDatabase). When the button is clicked, I am getting the updates within my WCL_DATABASE table, but not my TrafficDatabase records.

Here is the SQL for the Update WCL_DATABASE

    UPDATE WCL_DATABASE_QUERY
SET WCL_DATABASE_QUERY.CARRIER = UCASE([Forms] ! [Edit_Load] ! [CARRIER])
    ,WCL_DATABASE_QUERY.COMMENTS = Forms ! Edit_Load ! [COMMENTS]
    ,WCL_DATABASE_QUERY.DISPATCH_NOTES = Forms ! Edit_Load ! [DISPATCH_NOTES]
    ,WCL_DATABASE_QUERY.CUSTOMER_CANCEL_DATE = [Forms] ! [Edit_Load] ! [CUSTOMER_CANCEL_DATE]
    ,WCL_DATABASE_QUERY.TRAFFIC_SPECIALIST = [Forms] ! [Edit_Load] ! [TRAFFIC_SPECIALIST]
    ,WCL_DATABASE_QUERY.RR_DATE = [Forms] ! [Edit_Load] ! [RR_DATE]
    ,WCL_DATABASE_QUERY.RA_DATE = [Forms] ! [Edit_Load] ! [RA_DATE]
    ,WCL_DATABASE_QUERY.PLANNED_SHIP_DATE = [Forms] ! [Edit_Load] ! [PLANNED_SHIP_DATE]
    ,WCL_DATABASE_QUERY.CONFIRMATION_NUMBER = [Forms] ! [Edit_Load] ! [CONFIRMATION_NUMBER]
    ,WCL_DATABASE_QUERY.WR_ID = [Forms] ! [Edit_Load] ! [WR_ID]
    ,WCL_DATABASE_QUERY.LAST_UPDATE = Now()
WHERE (
        ((WCL_DATABASE_QUERY ! SHIPPING_LOAD_NUMBER) = Forms ! Edit_Load ! SHIPPING_LOAD_NUMBER_Title)
        AND ((WCL_DATABASE_QUERY.SHIPPING_LOAD_NUMBER) <> "0")
        );

and here is my SQL for updating the TrafficDatabase

UPDATE TrafficDatabase_Query
SET TrafficDatabase_Query.CARRIER_ID = ucase([Forms] ! [Edit_Delivery] ! [CARRIER])
    ,TrafficDatabase_Query.COMMENTS = Forms ! Edit_Delivery ! [COMMENTS]
    ,TrafficDatabase_Query.DISPATCH_NOTES = Forms ! Edit_Delivery ! [DISPATCH_NOTES]
    ,TrafficDatabase_Query.CUSTOMER_CANCEL_DATE = [Forms] ! [Edit_Delivery] ! [CUSTOMER_CANCEL_DATE]
    ,TrafficDatabase_Query.TRAFFIC_SPECIALIST = [Forms] ! [Edit_Delivery] ! [TRAFFIC_SPECIALIST]
    ,TrafficDatabase_Query.RR_DATE = [Forms] ! [Edit_Delivery] ! [RR_DATE]
    ,TrafficDatabase_Query.RA_DATE = [Forms] ! [Edit_Delivery] ! [RA_DATE]
    ,TrafficDatabase_Query.PLANNED_SHIP_DATE = [Forms] ! [Edit_Delivery] ! [PLANNED_SHIP_DATE]
    ,TrafficDatabase_Query.CONFIRMATION_NUMBER = [Forms] ! [Edit_Delivery] ! [CONFIRMATION_NUMBER]
    ,TrafficDatabase_Query.WR_ID = [Forms] ! [Edit_Delivery] ! [WR_ID]
    ,TrafficDatabase_Query.LAST_UPDATE = NOW()
WHERE (
        (([TrafficDatabase_Query] ! SHIPPING_LOAD_NUMBER) = Forms ! Edit_Load ! SHIPPING_LOAD_NUMBER_Title)
        AND (([TRAFFICDATABASE_Query] ! [SHIPPING_LOAD_NUMBER]) <> "0")
        );

I am getting an error when I run this and it errors on the TrafficDatabase update portion.

At this point I’m just not sure what to do, I’ve double checked all the fields to make sure they are all strings instead of numeric.

You need to sign in to view this answers

Related Post