Skip to main content

[archive] 42000 Error using AcuODBC 6.1 Driver via ADO

  • August 19, 2008
  • 5 replies
  • 0 views
D

[Migrated content. Thread originally posted on 19 August 2008]

I started receiving the error after adding the "REPLACE" routine in the SQL SELECTstatement below. Using it to clean non-standard characters from the data. Without the REPLACE no problems.

ERROR [42000] [AcuCorp, Inc.][AcuODBC Driver]Expected lexical element not found: FROM expected, ( found, near line 1, column 158

80040E14

This is the whole select statement:

cmd.CommandText = "SELECT shipment_bin_num, " & _
"shipment_tracking_num, " & _
"shipment_carrier_service, " & _
"shipment_carrier, " & _
"shipment_acct_num, " & _
"shipment_acct_name, " & _
"shipment_contact, " & _
"REPLACE(REPLACE(shipment_address_1,'.',''),',',''), " & _
"REPLACE(REPLACE(shipment_address_2,'.',''),',',''), " & _
"REPLACE(REPLACE(shipment_city,'.',''),',',''), " & _
"shipment_state, " & _
"shipment_country, " & _
"REPLACE(REPLACE(shipment_postal_code,'-',''),' ',''), " & _
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(shipment_phone_number,'.',''),'-',''),'(',''),')',''),'Ext',''), " & _
"shipment_weight, " & _
"shipment_acct_email, " & _
"shipment_cust_cost " & _
"FROM omdynshp WHERE shipment_carrier = '"& varCarrier & "' AND shipment_bin_num
"AND shipment_tracking_num IS NULL"

Thanks in advance

    5 replies

    D

    [Migrated content. Thread originally posted on 19 August 2008]

    I started receiving the error after adding the "REPLACE" routine in the SQL SELECTstatement below. Using it to clean non-standard characters from the data. Without the REPLACE no problems.

    ERROR [42000] [AcuCorp, Inc.][AcuODBC Driver]Expected lexical element not found: FROM expected, ( found, near line 1, column 158

    80040E14

    This is the whole select statement:

    cmd.CommandText = "SELECT shipment_bin_num, " & _
    "shipment_tracking_num, " & _
    "shipment_carrier_service, " & _
    "shipment_carrier, " & _
    "shipment_acct_num, " & _
    "shipment_acct_name, " & _
    "shipment_contact, " & _
    "REPLACE(REPLACE(shipment_address_1,'.',''),',',''), " & _
    "REPLACE(REPLACE(shipment_address_2,'.',''),',',''), " & _
    "REPLACE(REPLACE(shipment_city,'.',''),',',''), " & _
    "shipment_state, " & _
    "shipment_country, " & _
    "REPLACE(REPLACE(shipment_postal_code,'-',''),' ',''), " & _
    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(shipment_phone_number,'.',''),'-',''),'(',''),')',''),'Ext',''), " & _
    "shipment_weight, " & _
    "shipment_acct_email, " & _
    "shipment_cust_cost " & _
    "FROM omdynshp WHERE shipment_carrier = '"& varCarrier & "' AND shipment_bin_num
    "AND shipment_tracking_num IS NULL"

    Thanks in advance
    I don't know if this is causing your particular error, but you have a space in the middle of your "shipment_ phone_number" column name.
    D

    [Migrated content. Thread originally posted on 19 August 2008]

    I started receiving the error after adding the "REPLACE" routine in the SQL SELECTstatement below. Using it to clean non-standard characters from the data. Without the REPLACE no problems.

    ERROR [42000] [AcuCorp, Inc.][AcuODBC Driver]Expected lexical element not found: FROM expected, ( found, near line 1, column 158

    80040E14

    This is the whole select statement:

    cmd.CommandText = "SELECT shipment_bin_num, " & _
    "shipment_tracking_num, " & _
    "shipment_carrier_service, " & _
    "shipment_carrier, " & _
    "shipment_acct_num, " & _
    "shipment_acct_name, " & _
    "shipment_contact, " & _
    "REPLACE(REPLACE(shipment_address_1,'.',''),',',''), " & _
    "REPLACE(REPLACE(shipment_address_2,'.',''),',',''), " & _
    "REPLACE(REPLACE(shipment_city,'.',''),',',''), " & _
    "shipment_state, " & _
    "shipment_country, " & _
    "REPLACE(REPLACE(shipment_postal_code,'-',''),' ',''), " & _
    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(shipment_phone_number,'.',''),'-',''),'(',''),')',''),'Ext',''), " & _
    "shipment_weight, " & _
    "shipment_acct_email, " & _
    "shipment_cust_cost " & _
    "FROM omdynshp WHERE shipment_carrier = '"& varCarrier & "' AND shipment_bin_num
    "AND shipment_tracking_num IS NULL"

    Thanks in advance
    I don't know if this is causing your particular error, but you have a space in the middle of your "shipment_ phone_number" column name.
    Hmmm, don't see that in the column name? The only space I am using is under the shipment_postal_code, replacing it with nothing. This is to remove spaces from Canadian or British zip codes. Maybe I need to represent the space differently?
    D

    [Migrated content. Thread originally posted on 19 August 2008]

    I started receiving the error after adding the "REPLACE" routine in the SQL SELECTstatement below. Using it to clean non-standard characters from the data. Without the REPLACE no problems.

    ERROR [42000] [AcuCorp, Inc.][AcuODBC Driver]Expected lexical element not found: FROM expected, ( found, near line 1, column 158

    80040E14

    This is the whole select statement:

    cmd.CommandText = "SELECT shipment_bin_num, " & _
    "shipment_tracking_num, " & _
    "shipment_carrier_service, " & _
    "shipment_carrier, " & _
    "shipment_acct_num, " & _
    "shipment_acct_name, " & _
    "shipment_contact, " & _
    "REPLACE(REPLACE(shipment_address_1,'.',''),',',''), " & _
    "REPLACE(REPLACE(shipment_address_2,'.',''),',',''), " & _
    "REPLACE(REPLACE(shipment_city,'.',''),',',''), " & _
    "shipment_state, " & _
    "shipment_country, " & _
    "REPLACE(REPLACE(shipment_postal_code,'-',''),' ',''), " & _
    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(shipment_phone_number,'.',''),'-',''),'(',''),')',''),'Ext',''), " & _
    "shipment_weight, " & _
    "shipment_acct_email, " & _
    "shipment_cust_cost " & _
    "FROM omdynshp WHERE shipment_carrier = '"& varCarrier & "' AND shipment_bin_num
    "AND shipment_tracking_num IS NULL"

    Thanks in advance
    Sorry if I was unclear. What I mean is that you have a space between "shipment_" and "phone_number", which should be removed to read "shipment_phone_number".
    D

    [Migrated content. Thread originally posted on 19 August 2008]

    I started receiving the error after adding the "REPLACE" routine in the SQL SELECTstatement below. Using it to clean non-standard characters from the data. Without the REPLACE no problems.

    ERROR [42000] [AcuCorp, Inc.][AcuODBC Driver]Expected lexical element not found: FROM expected, ( found, near line 1, column 158

    80040E14

    This is the whole select statement:

    cmd.CommandText = "SELECT shipment_bin_num, " & _
    "shipment_tracking_num, " & _
    "shipment_carrier_service, " & _
    "shipment_carrier, " & _
    "shipment_acct_num, " & _
    "shipment_acct_name, " & _
    "shipment_contact, " & _
    "REPLACE(REPLACE(shipment_address_1,'.',''),',',''), " & _
    "REPLACE(REPLACE(shipment_address_2,'.',''),',',''), " & _
    "REPLACE(REPLACE(shipment_city,'.',''),',',''), " & _
    "shipment_state, " & _
    "shipment_country, " & _
    "REPLACE(REPLACE(shipment_postal_code,'-',''),' ',''), " & _
    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(shipment_phone_number,'.',''),'-',''),'(',''),')',''),'Ext',''), " & _
    "shipment_weight, " & _
    "shipment_acct_email, " & _
    "shipment_cust_cost " & _
    "FROM omdynshp WHERE shipment_carrier = '"& varCarrier & "' AND shipment_bin_num
    "AND shipment_tracking_num IS NULL"

    Thanks in advance
    Sorry if I was unclear. What I mean is that you have a space between "shipment_" and "phone_number", which should be removed to read "shipment_phone_number".
    D

    [Migrated content. Thread originally posted on 19 August 2008]

    I started receiving the error after adding the "REPLACE" routine in the SQL SELECTstatement below. Using it to clean non-standard characters from the data. Without the REPLACE no problems.

    ERROR [42000] [AcuCorp, Inc.][AcuODBC Driver]Expected lexical element not found: FROM expected, ( found, near line 1, column 158

    80040E14

    This is the whole select statement:

    cmd.CommandText = "SELECT shipment_bin_num, " & _
    "shipment_tracking_num, " & _
    "shipment_carrier_service, " & _
    "shipment_carrier, " & _
    "shipment_acct_num, " & _
    "shipment_acct_name, " & _
    "shipment_contact, " & _
    "REPLACE(REPLACE(shipment_address_1,'.',''),',',''), " & _
    "REPLACE(REPLACE(shipment_address_2,'.',''),',',''), " & _
    "REPLACE(REPLACE(shipment_city,'.',''),',',''), " & _
    "shipment_state, " & _
    "shipment_country, " & _
    "REPLACE(REPLACE(shipment_postal_code,'-',''),' ',''), " & _
    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(shipment_phone_number,'.',''),'-',''),'(',''),')',''),'Ext',''), " & _
    "shipment_weight, " & _
    "shipment_acct_email, " & _
    "shipment_cust_cost " & _
    "FROM omdynshp WHERE shipment_carrier = '"& varCarrier & "' AND shipment_bin_num
    "AND shipment_tracking_num IS NULL"

    Thanks in advance
    Sorry if I was unclear. What I mean is that you have a space between "shipment_" and "phone_number", which should be removed to read "shipment_phone_number".
    Ahh, I see it now. I rewrote the line and it worked.

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement