how to forbid an apostrophe in text box (input mask?)

To prevent future apostrophe bugs and errors, isn't it just simpler to
forbid an apostrophe from being entered into a text field? For example,
couldn't "Alice's Restaurant" be changed to "Alices Restaurant" etc.
automatically and programmatically during data entry? This would eliminate
my concatinated strings from producing errors when I base the string on a
query. Think this is an example of the "Dreaded Apostrophe Bug." If I
enter a double apostrophe I still get the error in the StrSQL. Also, it
appears in the field as a double apostrophe which is uglier than a missing
apostrophe.

This is for a simple phone book. Ideas? Should I post the code?

Thanks.
Rich Hollenbeck

re: how to forbid an apostrophe in text box (input mask?)

You can destroy the apostrophy keystroke if you set each form's KeyPreview
to Yes, and in the KeyPress event of the form test if KeyAscii is 39, and if
so set it to zero.

That's probably too restrictive to apply application wide, though:
The O'Brians won't be happy.
It's difficult to accept users' comments.

A better idea is to always use double-quotes for your delimiters. You just
have to double them up if they are embedded so that VBA knows it's not the
end of the string, e.g.:
"This has a ""word"" in quotes."
If you want to block the double-quote character, you can probably get away
with that. It's rarely used in data, other than as a simple for inches.

The following code replaces the double-quote with a single quote as the user
types. It also replaces the pipe character with a backslash, as it also
causes problems. To use the code:
1. Paste it into a general module (Modules tab of Database window).
2. Set your form's KeyPreview to yes.
3. Set your form's On Key Press property to:
[Event Procedure]
4. Click the Build button (...) beside this. Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:
Call NoDblQuote(KeyAscii)


Sub NoDblQuote(KeyAscii As Integer)
Select Case KeyAscii
Case 34
KeyAscii = 39
Case 124
KeyAscii = 92
End Select
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
news:PiA8d.6520$r3.3117@trnddc05...[color=blue]
> To prevent future apostrophe bugs and errors, isn't it just simpler to
> forbid an apostrophe from being entered into a text field? For example,
> couldn't "Alice's Restaurant" be changed to "Alices Restaurant" etc.
> automatically and programmatically during data entry? This would
> eliminate
> my concatinated strings from producing errors when I base the string on a
> query. Think this is an example of the "Dreaded Apostrophe Bug." If I
> enter a double apostrophe I still get the error in the StrSQL. Also, it
> appears in the field as a double apostrophe which is uglier than a missing
> apostrophe.
>
> This is for a simple phone book. Ideas? Should I post the code?
>
> Thanks.
> Rich Hollenbeck[/color]

re: how to forbid an apostrophe in text box (input mask?)

Well, the real problem is that you are trying to use queries improperly.

First of all, whenever you can, use parameters instead of string insertion.
This allows the database library to handle the issue for you as well as
automatically handling the difference between a value and a Null, etc.

In the cases when you can't use parameters, make sure you always pre-process
the string, so that it can be inserted into the SQL properly, ...

....
strSql = "SELECT * " & _
"FROM tblPerson " & _
"WHERE tblPerson.Name=" & TextToSqlConstExpr("James O'Connel")
....

Public Function TextToSqlConstExpr(Text As String) As String
Const cstrQuote = "'"
TextToSqlConstExpr = cstrQuote & _
Replace(Text, cstrQuote, cstrQuote & cstrQuote) & _
cstrQuote
End Function

The string value data is not stored with the doubled single-quote. The
properly formatted string is generated at the same time the SQL command string
is built.

On Tue, 05 Oct 2004 16:51:59 GMT, "Richard Hollenbeck"
<richard.hollenbeck@verizon.net> wrote:
[color=blue]
>To prevent future apostrophe bugs and errors, isn't it just simpler to
>forbid an apostrophe from being entered into a text field? For example,
>couldn't "Alice's Restaurant" be changed to "Alices Restaurant" etc.
>automatically and programmatically during data entry? This would eliminate
>my concatinated strings from producing errors when I base the string on a
>query. Think this is an example of the "Dreaded Apostrophe Bug." If I
>enter a double apostrophe I still get the error in the StrSQL. Also, it
>appears in the field as a double apostrophe which is uglier than a missing
>apostrophe.
>
>This is for a simple phone book. Ideas? Should I post the code?
>
>Thanks.
>Rich Hollenbeck
>
>
>
>[/color]

re: how to forbid an apostrophe in text box (input mask?)

Thank you. I'm sorry, but I'm not entirely sure what you mean by double
quotes as delimiters. The part of my code causing me trouble is a single
line providing the SQL statement to a DAO.RecordSet:

StrSQL = "SELECT[list].[lname] & ', ' &[list].[fname] AS [Name],[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];"

Eventually I want to concatinate that with vbCrls & address1 & vbCrls &
address2 & vbCrls & city ...
(etc.)
(etc.)
(etc.)

.... if each len(FieldName) >0. That way I don't end up displaying a bunch
of meaningless blank lines.

Everything works fine unless I run into a line something like "O'Reilly,
Bill" which is "lname &', '& fname." I tried the following which didn't
work at all:

StrSQL = ""SELECT[list].[lname] & ', ' &[list].[fname] AS [strName],[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];""

....putting double quotation marks around the entire string. Nope, that
won't work. What am I missing? Thanks again for your frequent help.

Rich

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:4162d3c9$0$1272$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> You can destroy the apostrophy keystroke if you set each form's KeyPreview
> to Yes, and in the KeyPress event of the form test if KeyAscii is 39, and[/color]
if[color=blue]
> so set it to zero.
>
> That's probably too restrictive to apply application wide, though:
> The O'Brians won't be happy.
> It's difficult to accept users' comments.
>
> A better idea is to always use double-quotes for your delimiters. You just
> have to double them up if they are embedded so that VBA knows it's not the
> end of the string, e.g.:
> "This has a ""word"" in quotes."
> If you want to block the double-quote character, you can probably get away
> with that. It's rarely used in data, other than as a simple for inches.
>
> The following code replaces the double-quote with a single quote as the[/color]
user[color=blue]
> types. It also replaces the pipe character with a backslash, as it also
> causes problems. To use the code:
> 1. Paste it into a general module (Modules tab of Database window).
> 2. Set your form's KeyPreview to yes.
> 3. Set your form's On Key Press property to:
> [Event Procedure]
> 4. Click the Build button (...) beside this. Access opens the code window.
> Between the "Private Sub..." and "End Sub" lines, enter:
> Call NoDblQuote(KeyAscii)
>
>
> Sub NoDblQuote(KeyAscii As Integer)
> Select Case KeyAscii
> Case 34
> KeyAscii = 39
> Case 124
> KeyAscii = 92
> End Select
> End Sub
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
> news:PiA8d.6520$r3.3117@trnddc05...[color=green]
> > To prevent future apostrophe bugs and errors, isn't it just simpler to
> > forbid an apostrophe from being entered into a text field? For example,
> > couldn't "Alice's Restaurant" be changed to "Alices Restaurant" etc.
> > automatically and programmatically during data entry? This would
> > eliminate
> > my concatinated strings from producing errors when I base the string on[/color][/color]
a[color=blue][color=green]
> > query. Think this is an example of the "Dreaded Apostrophe Bug." If I
> > enter a double apostrophe I still get the error in the StrSQL. Also, it
> > appears in the field as a double apostrophe which is uglier than a[/color][/color]
missing[color=blue][color=green]
> > apostrophe.
> >
> > This is for a simple phone book. Ideas? Should I post the code?
> >
> > Thanks.
> > Rich Hollenbeck[/color]
>
>[/color]

re: how to forbid an apostrophe in text box (input mask?)

Hello Steve Jorgensen,
Thank you for your reply with a good idea. Perhaps I should have posted
the troublesome StrSQL string so you can see what I'm trying to accomplish:

StrSQL = "SELECT[list].[lname] & ', ' &[list].[fname] AS [Name],[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];"

This string works perfectly whenever there is no apostrophe. I have no way
of knowing at programming time what names may eventually be in the phone
book so I can't see the value of making a constant to query against (such as
"James O'Connel" for example) at the time of programming. Maybe my StrSQL
line will make my question more clear.

"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
news:2fl5m05vukbf27dq2flqa9ag2ibsfkjpnv@4ax.com...[color=blue]
> Well, the real problem is that you are trying to use queries improperly.
>
> First of all, whenever you can, use parameters instead of string[/color]
insertion.[color=blue]
> This allows the database library to handle the issue for you as well as
> automatically handling the difference between a value and a Null, etc.
>
> In the cases when you can't use parameters, make sure you always[/color]
pre-process[color=blue]
> the string, so that it can be inserted into the SQL properly, ...
>
> ...
> strSql = "SELECT * " & _
> "FROM tblPerson " & _
> "WHERE tblPerson.Name=" & TextToSqlConstExpr("James[/color]
O'Connel")[color=blue]
> ...
>
> Public Function TextToSqlConstExpr(Text As String) As String
> Const cstrQuote = "'"
> TextToSqlConstExpr = cstrQuote & _
> Replace(Text, cstrQuote, cstrQuote & cstrQuote) &[/color]
_[color=blue]
> cstrQuote
> End Function
>
> The string value data is not stored with the doubled single-quote. The
> properly formatted string is generated at the same time the SQL command[/color]
string[color=blue]
> is built.
>
> On Tue, 05 Oct 2004 16:51:59 GMT, "Richard Hollenbeck"
> <richard.hollenbeck@verizon.net> wrote:
>[color=green]
> >To prevent future apostrophe bugs and errors, isn't it just simpler to
> >forbid an apostrophe from being entered into a text field? For example,
> >couldn't "Alice's Restaurant" be changed to "Alices Restaurant" etc.
> >automatically and programmatically during data entry? This would[/color][/color]
eliminate[color=blue][color=green]
> >my concatinated strings from producing errors when I base the string on a
> >query. Think this is an example of the "Dreaded Apostrophe Bug." If I
> >enter a double apostrophe I still get the error in the StrSQL. Also, it
> >appears in the field as a double apostrophe which is uglier than a[/color][/color]
missing[color=blue][color=green]
> >apostrophe.
> >
> >This is for a simple phone book. Ideas? Should I post the code?
> >
> >Thanks.
> >Rich Hollenbeck
> >
> >
> >
> >[/color]
>[/color]

re: how to forbid an apostrophe in text box (input mask?)

Here's a handful

HostAddress: IIf([HostAddHouseName]>"",[HostAddHouseName] &
IIf(Len(Nz([HostAddHouseNo]))+Len(Nz([HostAddStreet]))+Len(Nz([HostAddVillage]))+Len(Nz([Town]![Town]))+Len(Nz([AddCounty]))+Len(Nz([HostPostCode]))>0,",
")) & IIf([HostAddHouseNo]>"",[HostAddHouseNo] &
IIf(Len(Nz([HostAddStreet]))+Len(Nz([HostAddVillage]))+Len(Nz([Town]![Town]))+Len(Nz([AddCounty]))+Len(Nz([HostPostCode]))>0,"
")) & IIf([HostAddStreet]>"",[HostAddStreet] &
IIf(Len(Nz([HostAddVillage]))+Len(Nz([Town]![Town]))+Len(Nz([AddCounty]))+Len(Nz([HostPostCode]))>0,",
")) & IIf([HostAddVillage]>"",[HostAddVillage] &
IIf(Len(Nz([Town]![Town]))+Len(Nz([AddCounty]))+Len(Nz([HostPostCode]))>0,",
")) & IIf([Town]![Town]>"",[Town]![Town] &
IIf(Len(Nz([AddCounty]))+Len(Nz([HostPostCode]))>0,", ")) &
IIf([AddCounty]>"",[AddCounty] & IIf(Len(Nz([HostPostCode]))>0,", ")) &
IIf([HostPostCode]>"",Format([HostPostCode],"@@@@ @@@"))


This is some of the data in various tables (Host, Town and County)
HostAddHouseName
HostAddHouseNo
HostAddStreet
HostAddVillage
Town
AddCounty
HostPostCode

The mess above prints the address in 1 line with commas after each field if
it is there. It might be a help playing about with addresses

Th other bit of your question and various replies

Try something like

SQLStg = "SELECT County.* FROM County "
SQLStg = SQLStg & "WHERE County = " & Chr$(34) & AreaCounty & Chr$(34) &
";"

The Chr$(34) is a double quote

HTH

Phil

"Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
news:PiA8d.6520$r3.3117@trnddc05...[color=blue]
> To prevent future apostrophe bugs and errors, isn't it just simpler to
> forbid an apostrophe from being entered into a text field? For example,
> couldn't "Alice's Restaurant" be changed to "Alices Restaurant" etc.
> automatically and programmatically during data entry? This would
> eliminate
> my concatinated strings from producing errors when I base the string on a
> query. Think this is an example of the "Dreaded Apostrophe Bug." If I
> enter a double apostrophe I still get the error in the StrSQL. Also, it
> appears in the field as a double apostrophe which is uglier than a missing
> apostrophe.
>
> This is for a simple phone book. Ideas? Should I post the code?
>
> Thanks.
> Rich Hollenbeck
>
>
>
>
>[/color]

re: how to forbid an apostrophe in text box (input mask?)

Don't try to eliminate apostrophes. While it may seem like a good idea for
names, what will you do for O'Conner Street, and St. Mary's Way?

I use the following function as a wrapper around every field that may have
an apostrope (this was a slight modification of someone else's code, but I
can't remember whose):

Public Function S2SQL(ByVal inString As String) As String
Dim s As String
Dim i As Long
Do
i = InStr(inString, "'") ' double, single, double
If i = 0 Then Exit Do
s = s & Left$(inString, i - 1) & "''" ' double, single, single,
double
inString = Mid$(inString, i + 1)
Loop
S2SQL = "'" & s & inString & "'" ' double, single, double x2
End Function

Use:

cnxn.Execute "INSERT INTO su_street (str_name, update_by) VALUES (" & _
S2SQL(Me.txtStreet) & ", '" & GetUserLogin() & "')"


Darryl Kerkeslager



"Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
news:dwB8d.8542$1g5.5282@trnddc07...[color=blue]
> Thank you. I'm sorry, but I'm not entirely sure what you mean by double
> quotes as delimiters. The part of my code causing me trouble is a single
> line providing the SQL statement to a DAO.RecordSet:
>
> StrSQL = "SELECT[list].[lname] & ', ' &[list].[fname] AS [Name],
>[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];"
>
> Eventually I want to concatinate that with vbCrls & address1 & vbCrls &
> address2 & vbCrls & city ...
> (etc.)
> (etc.)
> (etc.)
>
> ... if each len(FieldName) >0. That way I don't end up displaying a bunch
> of meaningless blank lines.
>
> Everything works fine unless I run into a line something like "O'Reilly,
> Bill" which is "lname &', '& fname." I tried the following which didn't
> work at all:
>
> StrSQL = ""SELECT[list].[lname] & ', ' &[list].[fname] AS [strName],
>[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];""
>
> ...putting double quotation marks around the entire string. Nope, that
> won't work. What am I missing? Thanks again for your frequent help.
>
> Rich
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:4162d3c9$0$1272$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=green]
> > You can destroy the apostrophy keystroke if you set each form's[/color][/color]
KeyPreview[color=blue][color=green]
> > to Yes, and in the KeyPress event of the form test if KeyAscii is 39,[/color][/color]
and[color=blue]
> if[color=green]
> > so set it to zero.
> >
> > That's probably too restrictive to apply application wide, though:
> > The O'Brians won't be happy.
> > It's difficult to accept users' comments.
> >
> > A better idea is to always use double-quotes for your delimiters. You[/color][/color]
just[color=blue][color=green]
> > have to double them up if they are embedded so that VBA knows it's not[/color][/color]
the[color=blue][color=green]
> > end of the string, e.g.:
> > "This has a ""word"" in quotes."
> > If you want to block the double-quote character, you can probably get[/color][/color]
away[color=blue][color=green]
> > with that. It's rarely used in data, other than as a simple for inches.
> >
> > The following code replaces the double-quote with a single quote as the[/color]
> user[color=green]
> > types. It also replaces the pipe character with a backslash, as it also
> > causes problems. To use the code:
> > 1. Paste it into a general module (Modules tab of Database window).
> > 2. Set your form's KeyPreview to yes.
> > 3. Set your form's On Key Press property to:
> > [Event Procedure]
> > 4. Click the Build button (...) beside this. Access opens the code[/color][/color]
window.[color=blue][color=green]
> > Between the "Private Sub..." and "End Sub" lines, enter:
> > Call NoDblQuote(KeyAscii)
> >
> >
> > Sub NoDblQuote(KeyAscii As Integer)
> > Select Case KeyAscii
> > Case 34
> > KeyAscii = 39
> > Case 124
> > KeyAscii = 92
> > End Select
> > End Sub
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
> > news:PiA8d.6520$r3.3117@trnddc05...[color=darkred]
> > > To prevent future apostrophe bugs and errors, isn't it just simpler to
> > > forbid an apostrophe from being entered into a text field? For[/color][/color][/color]
example,[color=blue][color=green][color=darkred]
> > > couldn't "Alice's Restaurant" be changed to "Alices Restaurant" etc.
> > > automatically and programmatically during data entry? This would
> > > eliminate
> > > my concatinated strings from producing errors when I base the string[/color][/color][/color]
on[color=blue]
> a[color=green][color=darkred]
> > > query. Think this is an example of the "Dreaded Apostrophe Bug." If[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> > > enter a double apostrophe I still get the error in the StrSQL. Also,[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > appears in the field as a double apostrophe which is uglier than a[/color][/color]
> missing[color=green][color=darkred]
> > > apostrophe.
> > >
> > > This is for a simple phone book. Ideas? Should I post the code?
> > >
> > > Thanks.
> > > Rich Hollenbeck[/color]
> >
> >[/color]
>
>[/color]

re: how to forbid an apostrophe in text box (input mask?)

HI Richard

The idea is to use double-quotes, not single quotes, so you need to end up
with:
SELECT[list].[lname] & ", " &[list].[fname] AS [Name], ...
That solves the problem with the apostrophy, because the string contains the
double-quote character instead of the single quote character.

The next question is how to get one of those inside the string. If you code:
StrSQL = "SELECT[list].[lname] & ", " &[list].[fname] AS [Name], ...
then VBA will read the string, come to the closing quote mark before the
comma, and it will think the string is ended. Then it does not know what to
do with the rest of the line. The convention is to double-up the quote marks
where you want one of them in the string, so:
StrSQL = "SELECT[list].[lname] & "", "" &[list].[fname] AS [Name],[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
news:dwB8d.8542$1g5.5282@trnddc07...[color=blue]
> Thank you. I'm sorry, but I'm not entirely sure what you mean by double
> quotes as delimiters. The part of my code causing me trouble is a single
> line providing the SQL statement to a DAO.RecordSet:
>
> StrSQL = "SELECT[list].[lname] & ', ' &[list].[fname] AS [Name],
>[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];"
>
> Eventually I want to concatinate that with vbCrls & address1 & vbCrls &
> address2 & vbCrls & city ...
> (etc.)
> (etc.)
> (etc.)
>
> ... if each len(FieldName) >0. That way I don't end up displaying a bunch
> of meaningless blank lines.
>
> Everything works fine unless I run into a line something like "O'Reilly,
> Bill" which is "lname &', '& fname." I tried the following which didn't
> work at all:
>
> StrSQL = ""SELECT[list].[lname] & ', ' &[list].[fname] AS [strName],
>[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];""
>
> ...putting double quotation marks around the entire string. Nope, that
> won't work. What am I missing? Thanks again for your frequent help.
>
> Rich
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:4162d3c9$0$1272$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=green]
>> You can destroy the apostrophy keystroke if you set each form's
>> KeyPreview
>> to Yes, and in the KeyPress event of the form test if KeyAscii is 39, and[/color]
> if[color=green]
>> so set it to zero.
>>
>> That's probably too restrictive to apply application wide, though:
>> The O'Brians won't be happy.
>> It's difficult to accept users' comments.
>>
>> A better idea is to always use double-quotes for your delimiters. You
>> just
>> have to double them up if they are embedded so that VBA knows it's not
>> the
>> end of the string, e.g.:
>> "This has a ""word"" in quotes."
>> If you want to block the double-quote character, you can probably get
>> away
>> with that. It's rarely used in data, other than as a simple for inches.
>>
>> The following code replaces the double-quote with a single quote as the[/color]
> user[color=green]
>> types. It also replaces the pipe character with a backslash, as it also
>> causes problems. To use the code:
>> 1. Paste it into a general module (Modules tab of Database window).
>> 2. Set your form's KeyPreview to yes.
>> 3. Set your form's On Key Press property to:
>> [Event Procedure]
>> 4. Click the Build button (...) beside this. Access opens the code
>> window.
>> Between the "Private Sub..." and "End Sub" lines, enter:
>> Call NoDblQuote(KeyAscii)
>>
>>
>> Sub NoDblQuote(KeyAscii As Integer)
>> Select Case KeyAscii
>> Case 34
>> KeyAscii = 39
>> Case 124
>> KeyAscii = 92
>> End Select
>> End Sub
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
>> news:PiA8d.6520$r3.3117@trnddc05...[color=darkred]
>> > To prevent future apostrophe bugs and errors, isn't it just simpler to
>> > forbid an apostrophe from being entered into a text field? For
>> > example,
>> > couldn't "Alice's Restaurant" be changed to "Alices Restaurant" etc.
>> > automatically and programmatically during data entry? This would
>> > eliminate
>> > my concatinated strings from producing errors when I base the string on[/color][/color]
> a[color=green][color=darkred]
>> > query. Think this is an example of the "Dreaded Apostrophe Bug." If I
>> > enter a double apostrophe I still get the error in the StrSQL. Also,
>> > it
>> > appears in the field as a double apostrophe which is uglier than a[/color][/color]
> missing[color=green][color=darkred]
>> > apostrophe.
>> >
>> > This is for a simple phone book. Ideas? Should I post the code?
>> >
>> > Thanks.
>> > Rich Hollenbeck[/color][/color][/color]

re: how to forbid an apostrophe in text box (input mask?)

On Tue, 05 Oct 2004 18:47:19 GMT, "Richard Hollenbeck"
<richard.hollenbeck@verizon.net> wrote:
[color=blue]
>Hello Steve Jorgensen,
> Thank you for your reply with a good idea. Perhaps I should have posted
>the troublesome StrSQL string so you can see what I'm trying to accomplish:
>
>StrSQL = "SELECT[list].[lname] & ', ' &[list].[fname] AS [Name],
>[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];"
>
>This string works perfectly whenever there is no apostrophe. I have no way
>of knowing at programming time what names may eventually be in the phone
>book so I can't see the value of making a constant to query against (such as
>"James O'Connel" for example) at the time of programming. Maybe my StrSQL
>line will make my question more clear.
>
>"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
>news:2fl5m05vukbf27dq2flqa9ag2ibsfkjpnv@4ax.com.. .[color=green]
>> Well, the real problem is that you are trying to use queries improperly.
>>
>> First of all, whenever you can, use parameters instead of string[/color]
>insertion.[color=green]
>> This allows the database library to handle the issue for you as well as
>> automatically handling the difference between a value and a Null, etc.
>>
>> In the cases when you can't use parameters, make sure you always[/color]
>pre-process[color=green]
>> the string, so that it can be inserted into the SQL properly, ...
>>
>> ...
>> strSql = "SELECT * " & _
>> "FROM tblPerson " & _
>> "WHERE tblPerson.Name=" & TextToSqlConstExpr("James[/color]
>O'Connel")[color=green]
>> ...
>>
>> Public Function TextToSqlConstExpr(Text As String) As String
>> Const cstrQuote = "'"
>> TextToSqlConstExpr = cstrQuote & _
>> Replace(Text, cstrQuote, cstrQuote & cstrQuote) &[/color]
>_[color=green]
>> cstrQuote
>> End Function
>>
>> The string value data is not stored with the doubled single-quote. The
>> properly formatted string is generated at the same time the SQL command[/color]
>string[color=green]
>> is built.
>>
>> On Tue, 05 Oct 2004 16:51:59 GMT, "Richard Hollenbeck"
>> <richard.hollenbeck@verizon.net> wrote:
>>[color=darkred]
>> >To prevent future apostrophe bugs and errors, isn't it just simpler to
>> >forbid an apostrophe from being entered into a text field? For example,
>> >couldn't "Alice's Restaurant" be changed to "Alices Restaurant" etc.
>> >automatically and programmatically during data entry? This would[/color][/color]
>eliminate[color=green][color=darkred]
>> >my concatinated strings from producing errors when I base the string on a
>> >query. Think this is an example of the "Dreaded Apostrophe Bug." If I
>> >enter a double apostrophe I still get the error in the StrSQL. Also, it
>> >appears in the field as a double apostrophe which is uglier than a[/color][/color]
>missing[color=green][color=darkred]
>> >apostrophe.
>> >
>> >This is for a simple phone book. Ideas? Should I post the code?
>> >
>> >Thanks.
>> >Rich Hollenbeck
>> >
>> >
>> >
>> >[/color]
>>[/color]
>[/color]

re: how to forbid an apostrophe in text box (input mask?)

The point was not the hard coded string, that was just an example. The point
is that you can build a SQL string with an embedded constant based on whatever
text you want with the help of the TextToSqlConstExpr function.

From your example, I'm afraid I don't see what you're getting at with the
single-quote issue at all. Since the concatenation is being done by a SQL
query, not by code that's generating text to be processed -as- a SQL query,
there should not be any quotation mark bugs.

On Tue, 05 Oct 2004 18:47:19 GMT, "Richard Hollenbeck"
<richard.hollenbeck@verizon.net> wrote:
[color=blue]
>Hello Steve Jorgensen,
> Thank you for your reply with a good idea. Perhaps I should have posted
>the troublesome StrSQL string so you can see what I'm trying to accomplish:
>
>StrSQL = "SELECT[list].[lname] & ', ' &[list].[fname] AS [Name],
>[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];"
>
>This string works perfectly whenever there is no apostrophe. I have no way
>of knowing at programming time what names may eventually be in the phone
>book so I can't see the value of making a constant to query against (such as
>"James O'Connel" for example) at the time of programming. Maybe my StrSQL
>line will make my question more clear.
>
>"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
>news:2fl5m05vukbf27dq2flqa9ag2ibsfkjpnv@4ax.com.. .[color=green]
>> Well, the real problem is that you are trying to use queries improperly.
>>
>> First of all, whenever you can, use parameters instead of string[/color]
>insertion.[color=green]
>> This allows the database library to handle the issue for you as well as
>> automatically handling the difference between a value and a Null, etc.
>>
>> In the cases when you can't use parameters, make sure you always[/color]
>pre-process[color=green]
>> the string, so that it can be inserted into the SQL properly, ...
>>
>> ...
>> strSql = "SELECT * " & _
>> "FROM tblPerson " & _
>> "WHERE tblPerson.Name=" & TextToSqlConstExpr("James[/color]
>O'Connel")[color=green]
>> ...
>>
>> Public Function TextToSqlConstExpr(Text As String) As String
>> Const cstrQuote = "'"
>> TextToSqlConstExpr = cstrQuote & _
>> Replace(Text, cstrQuote, cstrQuote & cstrQuote) &[/color]
>_[color=green]
>> cstrQuote
>> End Function
>>
>> The string value data is not stored with the doubled single-quote. The
>> properly formatted string is generated at the same time the SQL command[/color]
>string[color=green]
>> is built.
>>
>> On Tue, 05 Oct 2004 16:51:59 GMT, "Richard Hollenbeck"
>> <richard.hollenbeck@verizon.net> wrote:
>>[color=darkred]
>> >To prevent future apostrophe bugs and errors, isn't it just simpler to
>> >forbid an apostrophe from being entered into a text field? For example,
>> >couldn't "Alice's Restaurant" be changed to "Alices Restaurant" etc.
>> >automatically and programmatically during data entry? This would[/color][/color]
>eliminate[color=green][color=darkred]
>> >my concatinated strings from producing errors when I base the string on a
>> >query. Think this is an example of the "Dreaded Apostrophe Bug." If I
>> >enter a double apostrophe I still get the error in the StrSQL. Also, it
>> >appears in the field as a double apostrophe which is uglier than a[/color][/color]
>missing[color=green][color=darkred]
>> >apostrophe.
>> >
>> >This is for a simple phone book. Ideas? Should I post the code?
>> >
>> >Thanks.
>> >Rich Hollenbeck
>> >
>> >
>> >
>> >[/color]
>>[/color]
>[/color]

re: how to forbid an apostrophe in text box (input mask?)

Thanks.
It's still not working right.

I ** REALLY **, ** REALLY ** appreciate your patience with this bone-head
(me).

Here's part of my code:

Private Sub lstMain_Click()

Dim txtCurrentData As String, _
db As DAO.database, _
rs As DAO.Recordset, _
StrSQL As String, _
strTemp As String

txtCurrentData = lstMain ' basically[list].[lname] & ", " &[list].[fname]
lblData.Caption = txtCurrentData 'probably should have called it
strCurrentData
Set db = CurrentDb()


'get address1 to append to string
StrSQL = "SELECT[list].[address1] FROM[list] WHERE[list].[lname] &
"", "" &[list].[fname] = '" & txtCurrentData & "'"
Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
txtCurrentData = Nz(rs.Fields(0))

If Len(txtCurrentData) > 0 Then
lblData.Caption = lblData.Caption & vbCrLf & txtCurrentData
End If

' get address1a to append to string
txtCurrentData = lstMain
StrSQL = "SELECT[list].[address1a] FROM[list] WHERE[list].[lname] &
"", "" &[list].[fname] = '" & txtCurrentData & "'"
Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
txtCurrentData = Nz(rs.Fields(0), "")

If Len(txtCurrentData) > 0 Then
lblData.Caption = lblData.Caption & vbCrLf & txtCurrentData
End If

' get city1 to append to string
txtCurrentData = lstMain
StrSQL = "SELECT[list].[city1] FROM[list] WHERE[list].[lname] & "",
"" &[list].[fname] = '" & txtCurrentData & "'"
Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
txtCurrentData = Nz(rs.Fields(0), "")

If Len(txtCurrentData) > 0 Then
lblData.Caption = lblData.Caption & vbCrLf & txtCurrentData
End If
' . . .

' then I continue to append the data
' from each field into the label. I'm using
' a label instead of a text box for other reasons.
' It's one big label that covers much of the form
' and is to the right of the list box. It still works
' perfectly unless there is an apostrophe.
' Notice that I'm using double quotation marks
' ("", "") instead of single quotation marks (", ") .

' . . .
End Sub

Thanks, Allen
Rich

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:41634274$0$1265$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> HI Richard
>
> The idea is to use double-quotes, not single quotes, so you need to end up
> with:
> SELECT[list].[lname] & ", " &[list].[fname] AS [Name], ...
> That solves the problem with the apostrophy, because the string contains[/color]
the[color=blue]
> double-quote character instead of the single quote character.
>
> The next question is how to get one of those inside the string. If you[/color]
code:[color=blue]
> StrSQL = "SELECT[list].[lname] & ", " &[list].[fname] AS [Name], ...
> then VBA will read the string, come to the closing quote mark before the
> comma, and it will think the string is ended. Then it does not know what[/color]
to[color=blue]
> do with the rest of the line. The convention is to double-up the quote[/color]
marks[color=blue]
> where you want one of them in the string, so:
> StrSQL = "SELECT[list].[lname] & "", "" &[list].[fname] AS [Name],
>[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];"
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
> news:dwB8d.8542$1g5.5282@trnddc07...[color=green]
> > Thank you. I'm sorry, but I'm not entirely sure what you mean by double
> > quotes as delimiters. The part of my code causing me trouble is a[/color][/color]
single[color=blue][color=green]
> > line providing the SQL statement to a DAO.RecordSet:
> >
> > StrSQL = "SELECT[list].[lname] & ', ' &[list].[fname] AS [Name],
> >[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];"
> >
> > Eventually I want to concatinate that with vbCrls & address1 & vbCrls &
> > address2 & vbCrls & city ...
> > (etc.)
> > (etc.)
> > (etc.)
> >
> > ... if each len(FieldName) >0. That way I don't end up displaying a[/color][/color]
bunch[color=blue][color=green]
> > of meaningless blank lines.
> >
> > Everything works fine unless I run into a line something like "O'Reilly,
> > Bill" which is "lname &', '& fname." I tried the following which didn't
> > work at all:
> >
> > StrSQL = ""SELECT[list].[lname] & ', ' &[list].[fname] AS [strName],
> >[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];""
> >
> > ...putting double quotation marks around the entire string. Nope, that
> > won't work. What am I missing? Thanks again for your frequent help.
> >
> > Rich
> >
> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> > news:4162d3c9$0$1272$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=darkred]
> >> You can destroy the apostrophy keystroke if you set each form's
> >> KeyPreview
> >> to Yes, and in the KeyPress event of the form test if KeyAscii is 39,[/color][/color][/color]
and[color=blue][color=green]
> > if[color=darkred]
> >> so set it to zero.
> >>
> >> That's probably too restrictive to apply application wide, though:
> >> The O'Brians won't be happy.
> >> It's difficult to accept users' comments.
> >>
> >> A better idea is to always use double-quotes for your delimiters. You
> >> just
> >> have to double them up if they are embedded so that VBA knows it's not
> >> the
> >> end of the string, e.g.:
> >> "This has a ""word"" in quotes."
> >> If you want to block the double-quote character, you can probably get
> >> away
> >> with that. It's rarely used in data, other than as a simple for inches.
> >>
> >> The following code replaces the double-quote with a single quote as the[/color]
> > user[color=darkred]
> >> types. It also replaces the pipe character with a backslash, as it also
> >> causes problems. To use the code:
> >> 1. Paste it into a general module (Modules tab of Database window).
> >> 2. Set your form's KeyPreview to yes.
> >> 3. Set your form's On Key Press property to:
> >> [Event Procedure]
> >> 4. Click the Build button (...) beside this. Access opens the code
> >> window.
> >> Between the "Private Sub..." and "End Sub" lines, enter:
> >> Call NoDblQuote(KeyAscii)
> >>
> >>
> >> Sub NoDblQuote(KeyAscii As Integer)
> >> Select Case KeyAscii
> >> Case 34
> >> KeyAscii = 39
> >> Case 124
> >> KeyAscii = 92
> >> End Select
> >> End Sub
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
> >> news:PiA8d.6520$r3.3117@trnddc05...
> >> > To prevent future apostrophe bugs and errors, isn't it just simpler[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> >> > forbid an apostrophe from being entered into a text field? For
> >> > example,
> >> > couldn't "Alice's Restaurant" be changed to "Alices Restaurant" etc.
> >> > automatically and programmatically during data entry? This would
> >> > eliminate
> >> > my concatinated strings from producing errors when I base the string[/color][/color][/color]
on[color=blue][color=green]
> > a[color=darkred]
> >> > query. Think this is an example of the "Dreaded Apostrophe Bug." If[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> >> > enter a double apostrophe I still get the error in the StrSQL. Also,
> >> > it
> >> > appears in the field as a double apostrophe which is uglier than a[/color]
> > missing[color=darkred]
> >> > apostrophe.
> >> >
> >> > This is for a simple phone book. Ideas? Should I post the code?
> >> >
> >> > Thanks.
> >> > Rich Hollenbeck[/color][/color]
>
>[/color]

re: how to forbid an apostrophe in text box (input mask?)

Well, you doubled the quote that doesn't matter, but not the one that does
matter. There was never any need to double the quotes around the comma
because the constant comma-space has no quotes in it, and since SQL itself is
doing the concatenation with the fields (the fields are not constant
expressions embedded in the SQL query text itself), there's no issue if the
fields have apostrophes in them.

The issue you -do- have to worry about is where you are inserting the WHERE
condition. You would have to change {... &[list].[fname] = '" &
txtCurrentData & "'" } to {... &[list].[fname] = """ & txtCurrentData &
""""}.

Again, I don't recommend this technique because it still allows for problems
if a double-quote character ever manages to get in there. Since you are
opening a DAO recordset in code, the "right" way to do this is with a querydef
and parameters. Barring that, pass the value from lstMain through a function
like the one I described in my other reply to make sure any apostrophes it
contains are properly converted to doubled apostrophes when inserting the text
into the SQL string (where it will be treated as a constant by SQL).

Here's an example using a query parameter - the approach I highly recommend.

Dim strSql As String
Dim dbs As DAO.database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

strSql = "SELECT[list].[address1] " & _
"FROM[list] " & _
"WHERE[list].[lname] & ', ' &[list].[fname] = prmFullName"
Set dbs = CurrentDb
Set qdf = dbs.CreateQuerydef("",strSql)
qdf.Parameters("prmFullName") = Me!lstMain
Set rst = qdf.OpenRecordset

....


On Wed, 06 Oct 2004 16:41:15 GMT, "Richard Hollenbeck"
<richard.hollenbeck@verizon.net> wrote:
[color=blue]
>Thanks.
>It's still not working right.
>
>I ** REALLY **, ** REALLY ** appreciate your patience with this bone-head
>(me).
>
>Here's part of my code:
>
>Private Sub lstMain_Click()
>
> Dim txtCurrentData As String, _
> db As DAO.database, _
> rs As DAO.Recordset, _
> StrSQL As String, _
> strTemp As String
>
> txtCurrentData = lstMain ' basically[list].[lname] & ", " &
>[list].[fname]
> lblData.Caption = txtCurrentData 'probably should have called it
>strCurrentData
> Set db = CurrentDb()
>
>
>'get address1 to append to string
> StrSQL = "SELECT[list].[address1] FROM[list] WHERE[list].[lname] &
>"", "" &[list].[fname] = '" & txtCurrentData & "'"
> Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
> txtCurrentData = Nz(rs.Fields(0))
>
> If Len(txtCurrentData) > 0 Then
> lblData.Caption = lblData.Caption & vbCrLf & txtCurrentData
> End If
>
>' get address1a to append to string
> txtCurrentData = lstMain
> StrSQL = "SELECT[list].[address1a] FROM[list] WHERE[list].[lname] &
>"", "" &[list].[fname] = '" & txtCurrentData & "'"
> Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
> txtCurrentData = Nz(rs.Fields(0), "")
>
> If Len(txtCurrentData) > 0 Then
> lblData.Caption = lblData.Caption & vbCrLf & txtCurrentData
> End If
>
>' get city1 to append to string
> txtCurrentData = lstMain
> StrSQL = "SELECT[list].[city1] FROM[list] WHERE[list].[lname] & "",
>"" &[list].[fname] = '" & txtCurrentData & "'"
> Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
> txtCurrentData = Nz(rs.Fields(0), "")
>
> If Len(txtCurrentData) > 0 Then
> lblData.Caption = lblData.Caption & vbCrLf & txtCurrentData
> End If
>' . . .
>
>' then I continue to append the data
>' from each field into the label. I'm using
>' a label instead of a text box for other reasons.
>' It's one big label that covers much of the form
>' and is to the right of the list box. It still works
>' perfectly unless there is an apostrophe.
>' Notice that I'm using double quotation marks
>' ("", "") instead of single quotation marks (", ") .
>
>' . . .
>End Sub
>
>Thanks, Allen
>Rich
>
>"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
>news:41634274$0$1265$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=green]
>> HI Richard
>>
>> The idea is to use double-quotes, not single quotes, so you need to end up
>> with:
>> SELECT[list].[lname] & ", " &[list].[fname] AS [Name], ...
>> That solves the problem with the apostrophy, because the string contains[/color]
>the[color=green]
>> double-quote character instead of the single quote character.
>>
>> The next question is how to get one of those inside the string. If you[/color]
>code:[color=green]
>> StrSQL = "SELECT[list].[lname] & ", " &[list].[fname] AS [Name], ...
>> then VBA will read the string, come to the closing quote mark before the
>> comma, and it will think the string is ended. Then it does not know what[/color]
>to[color=green]
>> do with the rest of the line. The convention is to double-up the quote[/color]
>marks[color=green]
>> where you want one of them in the string, so:
>> StrSQL = "SELECT[list].[lname] & "", "" &[list].[fname] AS [Name],
>>[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];"
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
>> news:dwB8d.8542$1g5.5282@trnddc07...[color=darkred]
>> > Thank you. I'm sorry, but I'm not entirely sure what you mean by double
>> > quotes as delimiters. The part of my code causing me trouble is a[/color][/color]
>single[color=green][color=darkred]
>> > line providing the SQL statement to a DAO.RecordSet:
>> >
>> > StrSQL = "SELECT[list].[lname] & ', ' &[list].[fname] AS [Name],
>> >[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];"
>> >
>> > Eventually I want to concatinate that with vbCrls & address1 & vbCrls &
>> > address2 & vbCrls & city ...
>> > (etc.)
>> > (etc.)
>> > (etc.)
>> >
>> > ... if each len(FieldName) >0. That way I don't end up displaying a[/color][/color]
>bunch[color=green][color=darkred]
>> > of meaningless blank lines.
>> >
>> > Everything works fine unless I run into a line something like "O'Reilly,
>> > Bill" which is "lname &', '& fname." I tried the following which didn't
>> > work at all:
>> >
>> > StrSQL = ""SELECT[list].[lname] & ', ' &[list].[fname] AS [strName],
>> >[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];""
>> >
>> > ...putting double quotation marks around the entire string. Nope, that
>> > won't work. What am I missing? Thanks again for your frequent help.
>> >
>> > Rich
>> >
>> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
>> > news:4162d3c9$0$1272$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
>> >> You can destroy the apostrophy keystroke if you set each form's
>> >> KeyPreview
>> >> to Yes, and in the KeyPress event of the form test if KeyAscii is 39,[/color][/color]
>and[color=green][color=darkred]
>> > if
>> >> so set it to zero.
>> >>
>> >> That's probably too restrictive to apply application wide, though:
>> >> The O'Brians won't be happy.
>> >> It's difficult to accept users' comments.
>> >>
>> >> A better idea is to always use double-quotes for your delimiters. You
>> >> just
>> >> have to double them up if they are embedded so that VBA knows it's not
>> >> the
>> >> end of the string, e.g.:
>> >> "This has a ""word"" in quotes."
>> >> If you want to block the double-quote character, you can probably get
>> >> away
>> >> with that. It's rarely used in data, other than as a simple for inches.
>> >>
>> >> The following code replaces the double-quote with a single quote as the
>> > user
>> >> types. It also replaces the pipe character with a backslash, as it also
>> >> causes problems. To use the code:
>> >> 1. Paste it into a general module (Modules tab of Database window).
>> >> 2. Set your form's KeyPreview to yes.
>> >> 3. Set your form's On Key Press property to:
>> >> [Event Procedure]
>> >> 4. Click the Build button (...) beside this. Access opens the code
>> >> window.
>> >> Between the "Private Sub..." and "End Sub" lines, enter:
>> >> Call NoDblQuote(KeyAscii)
>> >>
>> >>
>> >> Sub NoDblQuote(KeyAscii As Integer)
>> >> Select Case KeyAscii
>> >> Case 34
>> >> KeyAscii = 39
>> >> Case 124
>> >> KeyAscii = 92
>> >> End Select
>> >> End Sub
>> >>
>> >> --
>> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> >> Tips for Access users - http://allenbrowne.com/tips.html
>> >> Reply to group, rather than allenbrowne at mvps dot org.
>> >>
>> >> "Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
>> >> news:PiA8d.6520$r3.3117@trnddc05...
>> >> > To prevent future apostrophe bugs and errors, isn't it just simpler[/color][/color]
>to[color=green][color=darkred]
>> >> > forbid an apostrophe from being entered into a text field? For
>> >> > example,
>> >> > couldn't "Alice's Restaurant" be changed to "Alices Restaurant" etc.
>> >> > automatically and programmatically during data entry? This would
>> >> > eliminate
>> >> > my concatinated strings from producing errors when I base the string[/color][/color]
>on[color=green][color=darkred]
>> > a
>> >> > query. Think this is an example of the "Dreaded Apostrophe Bug." If[/color][/color]
>I[color=green][color=darkred]
>> >> > enter a double apostrophe I still get the error in the StrSQL. Also,
>> >> > it
>> >> > appears in the field as a double apostrophe which is uglier than a
>> > missing
>> >> > apostrophe.
>> >> >
>> >> > This is for a simple phone book. Ideas? Should I post the code?
>> >> >
>> >> > Thanks.
>> >> > Rich Hollenbeck[/color]
>>
>>[/color]
>[/color]

re: how to forbid an apostrophe in text box (input mask?)

Ohhh!! I think I'm about to really learn something! I'm going to read the
section in my book about querydef, then look online. I have never used
querydefs. I never understood querydefs--so I avoided them. In fact, I'm
pretty new to DAO altogether. Now I HAVE to learn it. Way Cool! I think I
beginning to understand what you have been trying to tell me. I think
you're saying it will compare the value of the list box against the querydef
instead of comparing against a string, eliminating the apostrophe problem.
I'll study for about an hour online and in my book and then try it. I'll
report back to this NewsGroup. Many Thanks.

Rich

"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
news:2fl5m05vukbf27dq2flqa9ag2ibsfkjpnv@4ax.com...[color=blue]
> Well, the real problem is that you are trying to use queries improperly.
>
> First of all, whenever you can, use parameters instead of string[/color]
insertion.[color=blue]
> This allows the database library to handle the issue for you as well as
> automatically handling the difference between a value and a Null, etc.
>
> In the cases when you can't use parameters, make sure you always[/color]
pre-process[color=blue]
> the string, so that it can be inserted into the SQL properly, ...
>
> ...
> strSql = "SELECT * " & _
> "FROM tblPerson " & _
> "WHERE tblPerson.Name=" & TextToSqlConstExpr("James[/color]
O'Connel")[color=blue]
> ...
>
> Public Function TextToSqlConstExpr(Text As String) As String
> Const cstrQuote = "'"
> TextToSqlConstExpr = cstrQuote & _
> Replace(Text, cstrQuote, cstrQuote & cstrQuote) &[/color]
_[color=blue]
> cstrQuote
> End Function
>
> The string value data is not stored with the doubled single-quote. The
> properly formatted string is generated at the same time the SQL command[/color]
string[color=blue]
> is built.
>
> On Tue, 05 Oct 2004 16:51:59 GMT, "Richard Hollenbeck"
> <richard.hollenbeck@verizon.net> wrote:
>[color=green]
> >To prevent future apostrophe bugs and errors, isn't it just simpler to
> >forbid an apostrophe from being entered into a text field? For example,
> >couldn't "Alice's Restaurant" be changed to "Alices Restaurant" etc.
> >automatically and programmatically during data entry? This would[/color][/color]
eliminate[color=blue][color=green]
> >my concatinated strings from producing errors when I base the string on a
> >query. Think this is an example of the "Dreaded Apostrophe Bug." If I
> >enter a double apostrophe I still get the error in the StrSQL. Also, it
> >appears in the field as a double apostrophe which is uglier than a[/color][/color]
missing[color=blue][color=green]
> >apostrophe.
> >
> >This is for a simple phone book. Ideas? Should I post the code?
> >
> >Thanks.
> >Rich Hollenbeck
> >
> >
> >
> >[/color]
>[/color]

re: how to forbid an apostrophe in text box (input mask?)

Doh!

This is why I put ID in the query. I don't need to compare against that
stupid string at all. Just compare the ID (hidden second column in the list
box) with the table's ID. Three days of ridiculous frustration. I still
need to learn about using QueryDef, but this temporarily stays the execution
of that murdurous topic. Here's part of the code which eventually worked:

Private Sub lstMain_Click()

Dim txtCurrentData As String
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim StrSQL As String
Dim strTemp As String
lblData.Caption = lstMain ' lname & ", " & fname

'get address1

StrSQL = "SELECT[list].[address1] FROM[list] WHERE[list].[ID] = " &
lstMain.Column(1)
Set db = CurrentDb()
Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
txtCurrentData = Nz(rs.Fields(0))

lblData.Caption = lblData.Caption & vbCrLf & txtCurrentData

etc.

Thanks, Steve, and Everybody for all your great ideas!

"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
news:t788m09d63n1nqi9al9kcl91euahju40u7@4ax.com...[color=blue]
> Well, you doubled the quote that doesn't matter, but not the one that does
> matter. There was never any need to double the quotes around the comma
> because the constant comma-space has no quotes in it, and since SQL itself[/color]
is[color=blue]
> doing the concatenation with the fields (the fields are not constant
> expressions embedded in the SQL query text itself), there's no issue if[/color]
the[color=blue]
> fields have apostrophes in them.
>
> The issue you -do- have to worry about is where you are inserting the[/color]
WHERE[color=blue]
> condition. You would have to change {... &[list].[fname] = '" &
> txtCurrentData & "'" } to {... &[list].[fname] = """ & txtCurrentData &
> """"}.
>
> Again, I don't recommend this technique because it still allows for[/color]
problems[color=blue]
> if a double-quote character ever manages to get in there. Since you are
> opening a DAO recordset in code, the "right" way to do this is with a[/color]
querydef[color=blue]
> and parameters. Barring that, pass the value from lstMain through a[/color]
function[color=blue]
> like the one I described in my other reply to make sure any apostrophes it
> contains are properly converted to doubled apostrophes when inserting the[/color]
text[color=blue]
> into the SQL string (where it will be treated as a constant by SQL).
>
> Here's an example using a query parameter - the approach I highly[/color]
recommend.[color=blue]
>
> Dim strSql As String
> Dim dbs As DAO.database
> Dim qdf As DAO.QueryDef
> Dim rst As DAO.Recordset
>
> strSql = "SELECT[list].[address1] " & _
> "FROM[list] " & _
> "WHERE[list].[lname] & ', ' &[list].[fname] = prmFullName"
> Set dbs = CurrentDb
> Set qdf = dbs.CreateQuerydef("",strSql)
> qdf.Parameters("prmFullName") = Me!lstMain
> Set rst = qdf.OpenRecordset
>
> ...
>
>
> On Wed, 06 Oct 2004 16:41:15 GMT, "Richard Hollenbeck"
> <richard.hollenbeck@verizon.net> wrote:
>[color=green]
> >Thanks.
> >It's still not working right.
> >
> >I ** REALLY **, ** REALLY ** appreciate your patience with this bone-head
> >(me).
> >
> >Here's part of my code:
> >
> >Private Sub lstMain_Click()
> >
> > Dim txtCurrentData As String, _
> > db As DAO.database, _
> > rs As DAO.Recordset, _
> > StrSQL As String, _
> > strTemp As String
> >
> > txtCurrentData = lstMain ' basically[list].[lname] & ", " &
> >[list].[fname]
> > lblData.Caption = txtCurrentData 'probably should have called it
> >strCurrentData
> > Set db = CurrentDb()
> >
> >
> >'get address1 to append to string
> > StrSQL = "SELECT[list].[address1] FROM[list] WHERE[list].[lname] &
> >"", "" &[list].[fname] = '" & txtCurrentData & "'"
> > Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
> > txtCurrentData = Nz(rs.Fields(0))
> >
> > If Len(txtCurrentData) > 0 Then
> > lblData.Caption = lblData.Caption & vbCrLf & txtCurrentData
> > End If
> >
> >' get address1a to append to string
> > txtCurrentData = lstMain
> > StrSQL = "SELECT[list].[address1a] FROM[list] WHERE[list].[lname][/color][/color]
&[color=blue][color=green]
> >"", "" &[list].[fname] = '" & txtCurrentData & "'"
> > Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
> > txtCurrentData = Nz(rs.Fields(0), "")
> >
> > If Len(txtCurrentData) > 0 Then
> > lblData.Caption = lblData.Caption & vbCrLf & txtCurrentData
> > End If
> >
> >' get city1 to append to string
> > txtCurrentData = lstMain
> > StrSQL = "SELECT[list].[city1] FROM[list] WHERE[list].[lname] &[/color][/color]
"",[color=blue][color=green]
> >"" &[list].[fname] = '" & txtCurrentData & "'"
> > Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
> > txtCurrentData = Nz(rs.Fields(0), "")
> >
> > If Len(txtCurrentData) > 0 Then
> > lblData.Caption = lblData.Caption & vbCrLf & txtCurrentData
> > End If
> >' . . .
> >
> >' then I continue to append the data
> >' from each field into the label. I'm using
> >' a label instead of a text box for other reasons.
> >' It's one big label that covers much of the form
> >' and is to the right of the list box. It still works
> >' perfectly unless there is an apostrophe.
> >' Notice that I'm using double quotation marks
> >' ("", "") instead of single quotation marks (", ") .
> >
> >' . . .
> >End Sub
> >
> >Thanks, Allen
> >Rich
> >
> >"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> >news:41634274$0$1265$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=darkred]
> >> HI Richard
> >>
> >> The idea is to use double-quotes, not single quotes, so you need to end[/color][/color][/color]
up[color=blue][color=green][color=darkred]
> >> with:
> >> SELECT[list].[lname] & ", " &[list].[fname] AS [Name], ...
> >> That solves the problem with the apostrophy, because the string[/color][/color][/color]
contains[color=blue][color=green]
> >the[color=darkred]
> >> double-quote character instead of the single quote character.
> >>
> >> The next question is how to get one of those inside the string. If you[/color]
> >code:[color=darkred]
> >> StrSQL = "SELECT[list].[lname] & ", " &[list].[fname] AS [Name],[/color][/color][/color]
....[color=blue][color=green][color=darkred]
> >> then VBA will read the string, come to the closing quote mark before[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >> comma, and it will think the string is ended. Then it does not know[/color][/color][/color]
what[color=blue][color=green]
> >to[color=darkred]
> >> do with the rest of the line. The convention is to double-up the quote[/color]
> >marks[color=darkred]
> >> where you want one of them in the string, so:
> >> StrSQL = "SELECT[list].[lname] & "", "" &[list].[fname] AS[/color][/color][/color]
[Name],[color=blue][color=green][color=darkred]
> >>[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];"
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
> >> news:dwB8d.8542$1g5.5282@trnddc07...
> >> > Thank you. I'm sorry, but I'm not entirely sure what you mean by[/color][/color][/color]
double[color=blue][color=green][color=darkred]
> >> > quotes as delimiters. The part of my code causing me trouble is a[/color]
> >single[color=darkred]
> >> > line providing the SQL statement to a DAO.RecordSet:
> >> >
> >> > StrSQL = "SELECT[list].[lname] & ', ' &[list].[fname] AS [Name],
> >> >[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];"
> >> >
> >> > Eventually I want to concatinate that with vbCrls & address1 & vbCrls[/color][/color][/color]
&[color=blue][color=green][color=darkred]
> >> > address2 & vbCrls & city ...
> >> > (etc.)
> >> > (etc.)
> >> > (etc.)
> >> >
> >> > ... if each len(FieldName) >0. That way I don't end up displaying a[/color]
> >bunch[color=darkred]
> >> > of meaningless blank lines.
> >> >
> >> > Everything works fine unless I run into a line something like[/color][/color][/color]
"O'Reilly,[color=blue][color=green][color=darkred]
> >> > Bill" which is "lname &', '& fname." I tried the following which[/color][/color][/color]
didn't[color=blue][color=green][color=darkred]
> >> > work at all:
> >> >
> >> > StrSQL = ""SELECT[list].[lname] & ', ' &[list].[fname] AS[/color][/color][/color]
[strName],[color=blue][color=green][color=darkred]
> >> >[list].[ID] FROM[list] ORDER BY[list].[lname],[list].[fname];""
> >> >
> >> > ...putting double quotation marks around the entire string. Nope,[/color][/color][/color]
that[color=blue][color=green][color=darkred]
> >> > won't work. What am I missing? Thanks again for your frequent help.
> >> >
> >> > Rich
> >> >
> >> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> >> > news:4162d3c9$0$1272$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
> >> >> You can destroy the apostrophy keystroke if you set each form's
> >> >> KeyPreview
> >> >> to Yes, and in the KeyPress event of the form test if KeyAscii is[/color][/color][/color]
39,[color=blue][color=green]
> >and[color=darkred]
> >> > if
> >> >> so set it to zero.
> >> >>
> >> >> That's probably too restrictive to apply application wide, though:
> >> >> The O'Brians won't be happy.
> >> >> It's difficult to accept users' comments.
> >> >>
> >> >> A better idea is to always use double-quotes for your delimiters.[/color][/color][/color]
You[color=blue][color=green][color=darkred]
> >> >> just
> >> >> have to double them up if they are embedded so that VBA knows it's[/color][/color][/color]
not[color=blue][color=green][color=darkred]
> >> >> the
> >> >> end of the string, e.g.:
> >> >> "This has a ""word"" in quotes."
> >> >> If you want to block the double-quote character, you can probably[/color][/color][/color]
get[color=blue][color=green][color=darkred]
> >> >> away
> >> >> with that. It's rarely used in data, other than as a simple for[/color][/color][/color]
inches.[color=blue][color=green][color=darkred]
> >> >>
> >> >> The following code replaces the double-quote with a single quote as[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >> > user
> >> >> types. It also replaces the pipe character with a backslash, as it[/color][/color][/color]
also[color=blue][color=green][color=darkred]
> >> >> causes problems. To use the code:
> >> >> 1. Paste it into a general module (Modules tab of Database window).
> >> >> 2. Set your form's KeyPreview to yes.
> >> >> 3. Set your form's On Key Press property to:
> >> >> [Event Procedure]
> >> >> 4. Click the Build button (...) beside this. Access opens the code
> >> >> window.
> >> >> Between the "Private Sub..." and "End Sub" lines, enter:
> >> >> Call NoDblQuote(KeyAscii)
> >> >>
> >> >>
> >> >> Sub NoDblQuote(KeyAscii As Integer)
> >> >> Select Case KeyAscii
> >> >> Case 34
> >> >> KeyAscii = 39
> >> >> Case 124
> >> >> KeyAscii = 92
> >> >> End Select
> >> >> End Sub
> >> >>
> >> >> --
> >> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> >> Reply to group, rather than allenbrowne at mvps dot org.
> >> >>
> >> >> "Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in[/color][/color][/color]
message[color=blue][color=green][color=darkred]
> >> >> news:PiA8d.6520$r3.3117@trnddc05...
> >> >> > To prevent future apostrophe bugs and errors, isn't it just[/color][/color][/color]
simpler[color=blue][color=green]
> >to[color=darkred]
> >> >> > forbid an apostrophe from being entered into a text field? For
> >> >> > example,
> >> >> > couldn't "Alice's Restaurant" be changed to "Alices Restaurant"[/color][/color][/color]
etc.[color=blue][color=green][color=darkred]
> >> >> > automatically and programmatically during data entry? This would
> >> >> > eliminate
> >> >> > my concatinated strings from producing errors when I base the[/color][/color][/color]
string[color=blue][color=green]
> >on[color=darkred]
> >> > a
> >> >> > query. Think this is an example of the "Dreaded Apostrophe Bug."[/color][/color][/color]
If[color=blue][color=green]
> >I[color=darkred]
> >> >> > enter a double apostrophe I still get the error in the StrSQL.[/color][/color][/color]
Also,[color=blue][color=green][color=darkred]
> >> >> > it
> >> >> > appears in the field as a double apostrophe which is uglier than a
> >> > missing
> >> >> > apostrophe.
> >> >> >
> >> >> > This is for a simple phone book. Ideas? Should I post the code?
> >> >> >
> >> >> > Thanks.
> >> >> > Rich Hollenbeck
> >>
> >>[/color]
> >[/color]
>[/color]