Identifier is too long error on VBA Code

[gwoman]

I have the following code attached to a multiselect list box control ... and I keep getting a ORA-00972: identifier is too long error ... I have searched for information on this error and foun dnothing so far ..... any ideas ...


Dim RepTo As String
Dim ctlList1
Dim sSql1 As String
Dim sSql2 As String
Dim strSQL As String
Dim strOldSQL As String
Dim varItm As Variant
Set ctlList1 = Me.lstBarCode

If ctlList1.ItemsSelected.Count = 0 Then
MsgBox "No BarCodes have been selected," & (Chr(13)) & (Chr(13)) & _
"Please select at least one BarCode from the list", vbExclamation, _
"Selection Error!"
Exit Sub

Else

For Each varItm In ctlList1.ItemsSelected
sSql1 = sSql1 & ctlList1.ItemData(varItm) & """, """
Next

sSql1 = Left(sSql1, Len(sSql1) - 3) & ")" 'Remove

strSQL = "select nvl(max(decode(c.attrid,2,valstr)),'-') field_name,"
strSQL = strSQL & "nvl(max(decode(c.attrid,36,valstr)),'-') well_file_type,"
strSQL = strSQL & "nvl(max(decode(c.attrid,16,valstr)),'-') well_name,"
strSQL = strSQL & "nvl(max(decode(c.attrid,23,valstr)),'-') api_no,'"
strSQL = strSQL & "nvl(max(decode(c.attrid,19,valstr)),'-') section,"
strSQL = strSQL & "nvl(max(decode(c.attrid,3,valstr)),'-') township,"
strSQL = strSQL & "nvl(max(decode(c.attrid,4,valstr)),'-') range,'"
strSQL = strSQL & "nvl(max(decode(c.attrid,18,valstr)),'-') log_report_title,"
strSQL = strSQL & "nvl(b.uniqueid,'-') bar_code,"
strSQL = strSQL & "nvl((select name from dtree where dataid=b.boxid),'-') box,"
strSQL = strSQL & "b.NODEID node_id"
strSQL = strSQL & "FROM (select a.nodeid,a.uniqueid,a.boxid"
strSQL = strSQL & "from edms.physitemco a"
strSQL = strSQL & "where a.uniqueid in (" & sSql1 & "))b, llattrdata c"
strSQL = strSQL & "where b.NODEID = c.id And c.defid = 2775746"
strSQL = strSQL & "group by b.nodeid,b.uniqueid,b.boxid"
strSQL = strSQL & "Order by nvl(max(decode(c.attrid,2,valstr)),'-'),nvl(max(decode(c.attrid,23,valstr)),' '),nvl(max(decode(c.attrid,36,valstr)),'-')"

strOldSQL = ChangeSQL("qry_UniqueID", strSQL)
'your query will now have a new sql value

End If

stDocName = "rpt_UniqueID"
DoCmd.OpenReport stDocName, acPreview


Thanks ... gwoman

 

[PHV]

strSQL = strSQL & "where a.uniqueid in (""" & sSql1 & """))b, llattrdata c"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gwoman]

Thanks ... after adding the additional quotes ... I still continue to get the same error ...


gwoman

 

[Remou]

[Oracle][ODBC][Ora]ORA-00972:identifier is too long?
thread759-607151

 

[gwoman]

So it is interpreting the name as the entire string ...
nvl(max(decode(c.attrid,36,valstr)),'-') well_file_type
Instead of just ... well_file_type

thanks
gwoman