Ambiguous Name Detected:Left

[jlg5454]

I experience this error in Access 2000 trying to construct a list box criteria search:

Ambiguous Name Detected:Left here is code:

Private Sub cmdOpenQuery_Click()

Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strteam As String
Dim strSQL As String
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "Team3" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM Service Sells"
cat.Views.Append "Team3", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "Team3") = acObjStateOpen Then
DoCmd.Close acQuery, "Team3"
End If
' Build criteria string for Office
For Each varItem In Me.lstteam.ItemsSelected
strteam = strteam & ",'" & Me.lstteam.ItemData(varItem) & "'"
Next varItem
If Len(strteam) = 0 Then
strteam = "Like '*'"
Else
strteam = Left(strteam, Len(strteam) - 1)
strteam = "IN(" & strteam & ")"
End If
' Build SQL statement
strSQL = "SELECT [Service Sells].[Team Namea], [Service Sells].[Last Name], [Service Sells].[First Name], [Service Sells].[66 General Deposit], [Service Sells].[34 Loans], [Service Sells].[84 Advisor Deposit], [Service Sells].[164 Retention Deposit]FROM [Service Sells];"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("Team3").Command
cmd.CommandText = strSQL
Set cat.Views("Team3").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "Team3"
' If required the dialog can be closed at this point
' DoCmd.Close acForm, Me.Name
' Restore screen updating
DoCmd.Echo True
End Sub

 

[bat17]

Just a stab in the dark! Have you used Left as a field name in the form anywhere?

Peter

 

[jlg5454]

No, I haven't

 

[Joe4]

Out of curiosity, which references do you have selected (in the VB editor, go to Tools | References)?

You should at least have the following selected:
-Visual Basic for Applications
-Microsoft Access X.X Object Library (where X.X is a version number)

 

[jlg5454]

I'll check it towmorrow and let you know.