syntax error!

[alex]

Hello all,

using Access '03...

I have the following code in my database:

End Sub

This code works fine, except when an Identifier has an apostrophe (I
get a Run-time error '3075'). I tried using &Chr(34) on each side of
the identifier as well as using double quotation marks. Either way,
the code does not run successfully.

Thoughts?
alex

 

[schasteen]

The problem is the apostrophe. In your code you get something that looks
like WHERE (((TBL_HOUSING.Identifier) like ' identifier's*'))
The extra apostrophe is ending the string then you have another starting a
new string value.

It is best if you just remove all apostrophes from your identifiers. If
this is not an option, maybe try replacing them with another character to run
your code and then use replace to put them back. Not sure how well the
second suggestion will work.

 

[Arvin Meyer [MVP]]

How about stripping out the apostrophe?

Public Function RemoveChars(strString As String, strChars As String) As
String
Dim i As Integer
Dim strOut As String
Dim c As String

strOut = ""

If ((Not IsNull(strString)) And (Not IsNull(strChars)) And _
(strString <> "") And (strChars <> "")) Then

For i = 1 To Len(strString)
c = Mid(strString, i, 1)
If (InStr(1, strChars, c) = 0) Then
strOut = strOut + c
End If
Next i

End If

RemoveChars = strOut

End Function

?RemoveChars("ab'cd","'")
abcd

 

[John Spencer]

Private Sub Identifier_Click()
DoCmd.SetWarnings False

If Len(Me.Identifier) > 10 Then
DoCmd.RunSQL "SELECT TBL_HOUSING.* INTO TBL_HOUSING2" & _
" FROM TBL_HOUSING " & _
" WHERE TBL_HOUSING.Identifier like """ & Left(Me.Identifier, 10) & "*"" "
Else
DoCmd.RunSQL "SELECT TBL_HOUSING.* INTO TBL_HOUSING2" & _
" FROM TBL_HOUSING " & _
" WHERE TBL_HOUSING.Identifier=""" & Me.Identifier & """ "
End If
DoCmd.SetWarnings True

Or use replace to double up the apostrophe as in the following example

If Len(Me.Identifier) > 10 Then
DoCmd.RunSQL "SELECT TBL_HOUSING.* INTO TBL_HOUSING2" & _
" FROM TBL_HOUSING " & _
" WHERE TBL_HOUSING.Identifier like '" & _
Replace(Left(Me.Identifier, 10),"'","''") & "*' "
Else
DoCmd.RunSQL "SELECT TBL_HOUSING.* INTO TBL_HOUSING2" & _
" FROM TBL_HOUSING " & _
" WHERE TBL_HOUSING.Identifier='" & _
Replace(Me.Identifier,"'","''") & "' "
End If
DoCmd.SetWarnings True

Of you could use a third method
" WHERE TBL_HOUSING.Identifier like " & Chr(34) & Left(Me.Identifier, 10) &
"*" & Chr(34)
" WHERE TBL_HOUSING.Identifier=" & chr(34) & Me.Identifier & Chr(34)
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[alex]

Private Sub Identifier_Click()
DoCmd.SetWarnings False

If Len(Me.Identifier) > 10 Then
DoCmd.RunSQL "SELECT TBL_HOUSING.* INTO TBL_HOUSING2" & _
   " FROM TBL_HOUSING " & _
   " WHERE TBL_HOUSING.Identifier like """ & Left(Me.Identifier, 10) & "*"" "
Else
DoCmd.RunSQL "SELECT TBL_HOUSING.* INTO TBL_HOUSING2" & _
    " FROM TBL_HOUSING " & _
    " WHERE TBL_HOUSING.Identifier=""" & Me.Identifier & """ "
End If
DoCmd.SetWarnings True

Or use replace to double up the apostrophe as in the following example

If Len(Me.Identifier) > 10 Then
DoCmd.RunSQL "SELECT TBL_HOUSING.* INTO TBL_HOUSING2" & _
   " FROM TBL_HOUSING " & _
   " WHERE TBL_HOUSING.Identifier like '" & _
     Replace(Left(Me.Identifier, 10),"'","''") & "*' "
Else
DoCmd.RunSQL "SELECT TBL_HOUSING.* INTO TBL_HOUSING2" & _
    " FROM TBL_HOUSING " & _
    " WHERE TBL_HOUSING.Identifier='" & _
     Replace(Me.Identifier,"'","''") & "' "
End If
DoCmd.SetWarnings True

Of you could use a third method
" WHERE TBL_HOUSING.Identifier like " & Chr(34) & Left(Me.Identifier, 10)&
"*" & Chr(34)
    " WHERE TBL_HOUSING.Identifier=" & chr(34) & Me.Identifier & Chr(34)
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County









- Show quoted text -

Thanks for your help everyone. Looks like I have plenty to work from.

alex