Error 3075 when entering Irish style last name

[tuesamlarry]

I have a data entry subform based on a query. When editing or adding Irish
names such as O'Toole, I get an "Error 3075, Syntax error (missing operator)
in query expression", whenever I try to put a single quote (apostrophe) in
the name. If I click the OK, the message disappears and the cursor continues
to next control and leaves the name with the apostrophe, as intended. Have
tried trapping the error in the Error handling routine in the Form's Before
Update event and the control's On Key Press event without success. What can I
do to either prevent the error in the first place or trap the error and not
display the message?
Windows XP Pro; Access 2007.
Thank you in advance.

 

[fredg]

I have a data entry subform based on a query. When editing or adding Irish
names such as O'Toole, I get an "Error 3075, Syntax error (missing operator)
in query expression", whenever I try to put a single quote (apostrophe) in
the name. If I click the OK, the message disappears and the cursor continues
to next control and leaves the name with the apostrophe, as intended. Have
tried trapping the error in the Error handling routine in the Form's Before
Update event and the control's On Key Press event without success. What can I
do to either prevent the error in the first place or trap the error and not
display the message?
Windows XP Pro; Access 2007.
Thank you in advance.

And the exact Query expression is?????

 

[Ken Sheridan]

It sounds like an SQL statement is being built somewhere using the
single-quote character as the text delimiter. Do you have any validation
code for instance, in the control's BeforeUpdate event procedure or
elsewhere, which is doing this? If so use a pair of contiguous double-quote
characters in pace of each single-quote character. These will be interpreted
as literal double-quote characters, e.g.

strSQL = "SELECT * FROM Contacts WHERE LastName = """ & Me.LastName & """"

It might also be something like a DLookup function call, e.g.

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """"

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

That should have been:

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """")

It sounds like an SQL statement is being built somewhere using the
single-quote character as the text delimiter. Do you have any validation
code for instance, in the control's BeforeUpdate event procedure or
elsewhere, which is doing this? If so use a pair of contiguous double-quote
characters in pace of each single-quote character. These will be interpreted
as literal double-quote characters, e.g.

strSQL = "SELECT * FROM Contacts WHERE LastName = """ & Me.LastName & """"

It might also be something like a DLookup function call, e.g.

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """"

Ken Sheridan
Stafford, England

I have a data entry subform based on a query. When editing or adding Irish
names such as O'Toole, I get an "Error 3075, Syntax error (missing operator)
in query expression", whenever I try to put a single quote (apostrophe) in
the name. If I click the OK, the message disappears and the cursor continues
to next control and leaves the name with the apostrophe, as intended. Have
tried trapping the error in the Error handling routine in the Form's Before
Update event and the control's On Key Press event without success. What can I
do to either prevent the error in the first place or trap the error and not
display the message?
Windows XP Pro; Access 2007.
Thank you in advance.

 

[tuesamlarry]

And the exact Query expression is?????

Here is the query which brings up the records for my Family members subform.
I use the master X child relationship connecting the clntClientID on the main
form to membClientID on the subform:

SELECT tblFamilyMembers.membClientID, tblFamilyMembers.membIndIndex,
tblFamilyMembers.membRelationToClient, tblFamilyMembers.membLastName,
tblFamilyMembers.membFirstName, tblFamilyMembers.membMiddleName,
tblFamilyMembers.membGender, tblFamilyMembers.membDateBirth,
tblFamilyMembers.membRace, tblFamilyMembers.membAdditionDate,
tblFamilyMembers.membRemovalDate,
IIf(DateDiff("yyyy",[membDateBirth],Now())+Int(Format(Now(),"mmdd")<Format([membDateBirth],"mmdd"))<1,DateDiff("m",[membDateBirth],Now()),DateDiff("yyyy",[membDateBirth],Now())+Int(Format(Now(),"mmdd")<Format([membDateBirth],"mmdd")))
AS Age, IIf([membDateBirth] Is Null,"",IIf([Age]>=18,"Adult","Children")) AS
AgeCl, IIf([membDateBirth] Is
Null,"",IIf([membDateBirth]<DateAdd("m",-12,Date()),"y","m")) AS AgePart,
IIf(Right([AgePart],2)="m",[Age] & [AgePart],[Age]) AS AgeEnglish,
AgeGroup([Age]) AS AG, tblFamilyMembers.membRemoved,
tblFamilyMembers.membReferenceAdult, tblFamilyMembers.EnteredOn,
tblFamilyMembers.EnteredBy, tblFamilyMembers.UpdatedOn,
tblFamilyMembers.UpdatedBy, tblFamilyMembers.membSuffix
FROM tblFamilyMembers
ORDER BY tblFamilyMembers.membClientID, tblFamilyMembers.membIndIndex;

I don't have any DLookups, or OpenArgs. Everything works fine except for
squawking when I put an apostrophe in the name. I have the following VBA in
the On Key Press of the membLastName control:

Private Sub membLastName_KeyPress(KeyAscii As Integer)
On Error GoTo Error_Handler

Select Case KeyAscii
Case 8 'backspace
Case 9 'tab
Case 13 'enter
Case 32 'space
Case 39 'single quote
Case 45 'hyphen
Case 46 'period
Case 65 To 90 'caps
Case 97 To 122 'lower case
Case Else
KeyAscii = 0: Beep

End Select

Exit_Procedure:
Exit Sub

Error_Handler:

MsgBox "An error has occurred in this application. " _
& "Please contact your technical support and " _
& "tell them this information:" _
& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
& Err.Description, _
Buttons:=vbCritical


Resume Exit_Procedure
End Sub

Thanks for looking at my problem. Hope this helps in pinpointing.

 

[Bob Vance]

Ken how would I get on with it in this case using O'Tool
=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),3))
Want to get OTO instead of 0'T..........Thanks Bob

That should have been:

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """")

It sounds like an SQL statement is being built somewhere using the
single-quote character as the text delimiter. Do you have any validation
code for instance, in the control's BeforeUpdate event procedure or
elsewhere, which is doing this? If so use a pair of contiguous
double-quote
characters in pace of each single-quote character. These will be
interpreted
as literal double-quote characters, e.g.

strSQL = "SELECT * FROM Contacts WHERE LastName = """ & Me.LastName &
""""

It might also be something like a DLookup function call, e.g.

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """"

Ken Sheridan
Stafford, England

I have a data entry subform based on a query. When editing or adding
Irish
names such as O'Toole, I get an "Error 3075, Syntax error (missing
operator)
in query expression", whenever I try to put a single quote (apostrophe)
in
the name. If I click the OK, the message disappears and the cursor
continues
to next control and leaves the name with the apostrophe, as intended.
Have
tried trapping the error in the Error handling routine in the Form's
Before
Update event and the control's On Key Press event without success. What
can I
do to either prevent the error in the first place or trap the error and
not
display the message?
Windows XP Pro; Access 2007.
Thank you in advance.

 

[Ken Sheridan]

Bob:

Add the following function to a standard module:

Public Function LeftName(varName As Variant, intLeftNumber As Integer) As
Variant

Const conALPHABET = "abcdefghijklmnopqrstuvxyz"
Dim n As Integer
Dim l As Integer
Dim strChr As String

If Not IsNull(varName) Then
For l = 1 To Len(varName)
strChr = Mid(varName, l, 1)
If InStr(conALPHABET, strChr) > 0 Then
LeftName = LeftName & strChr
n = n + 1
End If
If n = intLeftNumber Then Exit For
Next
End If

End Function

You can then return any number of leftmost characters, excluding
non-alphabetic characters, e.g. to return 3:

LeftName("O'Siridean", 3)

will return "Osi"

Ken Sheridan (aka Cináed O'Siridean)
Stafford, England

Ken how would I get on with it in this case using O'Tool
=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),3))
Want to get OTO instead of 0'T..........Thanks Bob

That should have been:

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """")

It sounds like an SQL statement is being built somewhere using the
single-quote character as the text delimiter. Do you have any validation
code for instance, in the control's BeforeUpdate event procedure or
elsewhere, which is doing this? If so use a pair of contiguous
double-quote
characters in pace of each single-quote character. These will be
interpreted
as literal double-quote characters, e.g.

strSQL = "SELECT * FROM Contacts WHERE LastName = """ & Me.LastName &
""""

It might also be something like a DLookup function call, e.g.

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """"

Ken Sheridan
Stafford, England

:

I have a data entry subform based on a query. When editing or adding
Irish
names such as O'Toole, I get an "Error 3075, Syntax error (missing
operator)
in query expression", whenever I try to put a single quote (apostrophe)
in
the name. If I click the OK, the message disappears and the cursor
continues
to next control and leaves the name with the apostrophe, as intended.
Have
tried trapping the error in the Error handling routine in the Form's
Before
Update event and the control's On Key Press event without success. What
can I
do to either prevent the error in the first place or trap the error and
not
display the message?
Windows XP Pro; Access 2007.
Thank you in advance.

 

[Bob Vance]

Thanks Ken I am getting an error on "Variant"
Sub Or Function Not Difined
Thanks Bob

Bob:

Add the following function to a standard module:

Public Function LeftName(varName As Variant, intLeftNumber As Integer) As
Variant

Const conALPHABET = "abcdefghijklmnopqrstuvxyz"
Dim n As Integer
Dim l As Integer
Dim strChr As String

If Not IsNull(varName) Then
For l = 1 To Len(varName)
strChr = Mid(varName, l, 1)
If InStr(conALPHABET, strChr) > 0 Then
LeftName = LeftName & strChr
n = n + 1
End If
If n = intLeftNumber Then Exit For
Next
End If

End Function

You can then return any number of leftmost characters, excluding
non-alphabetic characters, e.g. to return 3:

LeftName("O'Siridean", 3)

will return "Osi"

Ken Sheridan (aka Cináed O'Siridean)
Stafford, England

Ken how would I get on with it in this case using O'Tool
=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),3))
Want to get OTO instead of 0'T..........Thanks Bob

That should have been:

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """")

:

It sounds like an SQL statement is being built somewhere using the
single-quote character as the text delimiter. Do you have any
validation
code for instance, in the control's BeforeUpdate event procedure or
elsewhere, which is doing this? If so use a pair of contiguous
double-quote
characters in pace of each single-quote character. These will be
interpreted
as literal double-quote characters, e.g.

strSQL = "SELECT * FROM Contacts WHERE LastName = """ & Me.LastName &
""""

It might also be something like a DLookup function call, e.g.

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """"

Ken Sheridan
Stafford, England

:

I have a data entry subform based on a query. When editing or adding
Irish
names such as O'Toole, I get an "Error 3075, Syntax error (missing
operator)
in query expression", whenever I try to put a single quote
(apostrophe)
in
the name. If I click the OK, the message disappears and the cursor
continues
to next control and leaves the name with the apostrophe, as
intended.
Have
tried trapping the error in the Error handling routine in the Form's
Before
Update event and the control's On Key Press event without success.
What
can I
do to either prevent the error in the first place or trap the error
and
not
display the message?
Windows XP Pro; Access 2007.
Thank you in advance.

 

[Ken Sheridan]

Bob:

The curse of the newsreader strikes again!

The newsreader has split the first line. Variant should be at the end of
the first line, immediately after As (with a space between of course).

Ken Sheridan
Stafford, England

Thanks Ken I am getting an error on "Variant"
Sub Or Function Not Difined
Thanks Bob

Bob:

Add the following function to a standard module:

Public Function LeftName(varName As Variant, intLeftNumber As Integer) As
Variant

Const conALPHABET = "abcdefghijklmnopqrstuvxyz"
Dim n As Integer
Dim l As Integer
Dim strChr As String

If Not IsNull(varName) Then
For l = 1 To Len(varName)
strChr = Mid(varName, l, 1)
If InStr(conALPHABET, strChr) > 0 Then
LeftName = LeftName & strChr
n = n + 1
End If
If n = intLeftNumber Then Exit For
Next
End If

End Function

You can then return any number of leftmost characters, excluding
non-alphabetic characters, e.g. to return 3:

LeftName("O'Siridean", 3)

will return "Osi"

Ken Sheridan (aka Cináed O'Siridean)
Stafford, England

Ken how would I get on with it in this case using O'Tool
=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),3))
Want to get OTO instead of 0'T..........Thanks Bob

That should have been:

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """")

:

It sounds like an SQL statement is being built somewhere using the
single-quote character as the text delimiter. Do you have any
validation
code for instance, in the control's BeforeUpdate event procedure or
elsewhere, which is doing this? If so use a pair of contiguous
double-quote
characters in pace of each single-quote character. These will be
interpreted
as literal double-quote characters, e.g.

strSQL = "SELECT * FROM Contacts WHERE LastName = """ & Me.LastName &
""""

It might also be something like a DLookup function call, e.g.

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """"

Ken Sheridan
Stafford, England

:

I have a data entry subform based on a query. When editing or adding
Irish
names such as O'Toole, I get an "Error 3075, Syntax error (missing
operator)
in query expression", whenever I try to put a single quote
(apostrophe)
in
the name. If I click the OK, the message disappears and the cursor
continues
to next control and leaves the name with the apostrophe, as
intended.
Have
tried trapping the error in the Error handling routine in the Form's
Before
Update event and the control's On Key Press event without success.
What
can I
do to either prevent the error in the first place or trap the error
and
not
display the message?
Windows XP Pro; Access 2007.
Thank you in advance.

 

[Bob Vance]

Thanks Ken entered it in a Module code, no debugging problem
But still getting the ' In O'T
Is this code ok form my text box
=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),3))
Or does your Private Sub have to be somewhere special to work....Regards Bob

Bob:

The curse of the newsreader strikes again!

The newsreader has split the first line. Variant should be at the end of
the first line, immediately after As (with a space between of course).

Ken Sheridan
Stafford, England

Thanks Ken I am getting an error on "Variant"
Sub Or Function Not Difined
Thanks Bob

Bob:

Add the following function to a standard module:

Public Function LeftName(varName As Variant, intLeftNumber As Integer)
As
Variant

Const conALPHABET = "abcdefghijklmnopqrstuvxyz"
Dim n As Integer
Dim l As Integer
Dim strChr As String

If Not IsNull(varName) Then
For l = 1 To Len(varName)
strChr = Mid(varName, l, 1)
If InStr(conALPHABET, strChr) > 0 Then
LeftName = LeftName & strChr
n = n + 1
End If
If n = intLeftNumber Then Exit For
Next
End If

End Function

You can then return any number of leftmost characters, excluding
non-alphabetic characters, e.g. to return 3:

LeftName("O'Siridean", 3)

will return "Osi"

Ken Sheridan (aka Cináed O'Siridean)
Stafford, England

:

Ken how would I get on with it in this case using O'Tool
=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = "
&
[tbOwnerID] & ""),""),3))
Want to get OTO instead of 0'T..........Thanks Bob

message
That should have been:

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """")

:

It sounds like an SQL statement is being built somewhere using the
single-quote character as the text delimiter. Do you have any
validation
code for instance, in the control's BeforeUpdate event procedure or
elsewhere, which is doing this? If so use a pair of contiguous
double-quote
characters in pace of each single-quote character. These will be
interpreted
as literal double-quote characters, e.g.

strSQL = "SELECT * FROM Contacts WHERE LastName = """ & Me.LastName
&
""""

It might also be something like a DLookup function call, e.g.

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """"

Ken Sheridan
Stafford, England

:

I have a data entry subform based on a query. When editing or
adding
Irish
names such as O'Toole, I get an "Error 3075, Syntax error
(missing
operator)
in query expression", whenever I try to put a single quote
(apostrophe)
in
the name. If I click the OK, the message disappears and the
cursor
continues
to next control and leaves the name with the apostrophe, as
intended.
Have
tried trapping the error in the Error handling routine in the
Form's
Before
Update event and the control's On Key Press event without
success.
What
can I
do to either prevent the error in the first place or trap the
error
and
not
display the message?
Windows XP Pro; Access 2007.
Thank you in advance.

 

[Bob Vance]

Thanks Ken, got it Now, put it on the code behind formClientInfo and report
changed Left for leftname .........Brilliant thanks Bob
=UCase(LeftName(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),3))

Bob:

The curse of the newsreader strikes again!

The newsreader has split the first line. Variant should be at the end of
the first line, immediately after As (with a space between of course).

Ken Sheridan
Stafford, England

Thanks Ken I am getting an error on "Variant"
Sub Or Function Not Difined
Thanks Bob

Bob:

Add the following function to a standard module:

Public Function LeftName(varName As Variant, intLeftNumber As Integer)
As
Variant

Const conALPHABET = "abcdefghijklmnopqrstuvxyz"
Dim n As Integer
Dim l As Integer
Dim strChr As String

If Not IsNull(varName) Then
For l = 1 To Len(varName)
strChr = Mid(varName, l, 1)
If InStr(conALPHABET, strChr) > 0 Then
LeftName = LeftName & strChr
n = n + 1
End If
If n = intLeftNumber Then Exit For
Next
End If

End Function

You can then return any number of leftmost characters, excluding
non-alphabetic characters, e.g. to return 3:

LeftName("O'Siridean", 3)

will return "Osi"

Ken Sheridan (aka Cináed O'Siridean)
Stafford, England

:

Ken how would I get on with it in this case using O'Tool
=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = "
&
[tbOwnerID] & ""),""),3))
Want to get OTO instead of 0'T..........Thanks Bob

message
That should have been:

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """")

:

It sounds like an SQL statement is being built somewhere using the
single-quote character as the text delimiter. Do you have any
validation
code for instance, in the control's BeforeUpdate event procedure or
elsewhere, which is doing this? If so use a pair of contiguous
double-quote
characters in pace of each single-quote character. These will be
interpreted
as literal double-quote characters, e.g.

strSQL = "SELECT * FROM Contacts WHERE LastName = """ & Me.LastName
&
""""

It might also be something like a DLookup function call, e.g.

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """"

Ken Sheridan
Stafford, England

:

I have a data entry subform based on a query. When editing or
adding
Irish
names such as O'Toole, I get an "Error 3075, Syntax error
(missing
operator)
in query expression", whenever I try to put a single quote
(apostrophe)
in
the name. If I click the OK, the message disappears and the
cursor
continues
to next control and leaves the name with the apostrophe, as
intended.
Have
tried trapping the error in the Error handling routine in the
Form's
Before
Update event and the control's On Key Press event without
success.
What
can I
do to either prevent the error in the first place or trap the
error
and
not
display the message?
Windows XP Pro; Access 2007.
Thank you in advance.

 

[Bob Vance]

wells become ell ...Regards Bob

Bob:

Add the following function to a standard module:

Public Function LeftName(varName As Variant, intLeftNumber As Integer) As
Variant

Const conALPHABET = "abcdefghijklmnopqrstuvxyz"
Dim n As Integer
Dim l As Integer
Dim strChr As String

If Not IsNull(varName) Then
For l = 1 To Len(varName)
strChr = Mid(varName, l, 1)
If InStr(conALPHABET, strChr) > 0 Then
LeftName = LeftName & strChr
n = n + 1
End If
If n = intLeftNumber Then Exit For
Next
End If

End Function

You can then return any number of leftmost characters, excluding
non-alphabetic characters, e.g. to return 3:

LeftName("O'Siridean", 3)

will return "Osi"

Ken Sheridan (aka Cináed O'Siridean)
Stafford, England

Ken how would I get on with it in this case using O'Tool
=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),3))
Want to get OTO instead of 0'T..........Thanks Bob

That should have been:

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """")

:

It sounds like an SQL statement is being built somewhere using the
single-quote character as the text delimiter. Do you have any
validation
code for instance, in the control's BeforeUpdate event procedure or
elsewhere, which is doing this? If so use a pair of contiguous
double-quote
characters in pace of each single-quote character. These will be
interpreted
as literal double-quote characters, e.g.

strSQL = "SELECT * FROM Contacts WHERE LastName = """ & Me.LastName &
""""

It might also be something like a DLookup function call, e.g.

DLookup("LastName", "Contacts", "LastName = """ & [LastName] & """"

Ken Sheridan
Stafford, England

:

I have a data entry subform based on a query. When editing or adding
Irish
names such as O'Toole, I get an "Error 3075, Syntax error (missing
operator)
in query expression", whenever I try to put a single quote
(apostrophe)
in
the name. If I click the OK, the message disappears and the cursor
continues
to next control and leaves the name with the apostrophe, as
intended.
Have
tried trapping the error in the Error handling routine in the Form's
Before
Update event and the control's On Key Press event without success.
What
can I
do to either prevent the error in the first place or trap the error
and
not
display the message?
Windows XP Pro; Access 2007.
Thank you in advance.