cmdSQLData.Execute character limit 1

[AlzCranium]

Hello, I have the following SQL coded in a module in Access.

sSql = "SELECT distinct (Primary_Nme)" & _
        "FROM DB.TableA," & _
        "(SELECT distinct (client_id)" & _
        "FROM DB.TableB" & _
        "WHERE carrier_operational_id = " & Chr$(39) & strCarrier & Chr$(39) & _
        "AND eff_dte <= date " & _
        "AND end_dte >= date " & _
        "AND end_eff_dte >= date " & _
        "AND client_org_operational_id > ' ') CBHR (org_id)" & _
        "WHERE operational_id = CBHR.org_id" & _
        "AND eff_dte <= date" & _
        "AND end_dte >= date" & _
        "AND end_eff_dte >= date" & _
        "AND client_type_cde = 'CO';"

This is followed by:

cmdSQLData.CommandText = sSql       
cmdSQLData.CommandType = adCmdText  
cmdSQLData.CommandTimeout = 0       
Set rs = cmdSQLData.Execute()

On the last line (cmdSQLData.Execute) I am getting an error "Name is longer than 30 characters." Does someone know how to get around this or what I am doing wrong?
Thank you in advance for your time and effort.

 

[dhookom]

It looks like you are missing lots of spaces and possibly other issues. Typically you would place a breakpoint in the code following the creation of sSQL so you can debug.

At a minimum try:

sSql = "SELECT distinct (Primary_Nme)" & _
        " FROM DB.TableA, " & _
        " (SELECT distinct (client_id)" & _
        " FROM DB.TableB" & _
        " WHERE carrier_operational_id = " & Chr$(39) & strCarrier & Chr$(39) & _
        " AND eff_dte <= date " & _
        " AND end_dte >= date " & _
        " AND end_eff_dte >= date " & _
        " AND client_org_operational_id > ' ') CBHR (org_id)" & _
        " WHERE operational_id = CBHR.org_id" & _
        " AND eff_dte <= date" & _
        " AND end_dte >= date" & _
        " AND end_eff_dte >= date" & _
        " AND client_type_cde = 'CO';"

Duane
Hook'D on Access
MS Access MVP