Re: [MS_AccessPros] Top right comma won't be inserted .

 

Wrap the expressions in Replace([Your Expression], "'","''")

Make sure the quotes are straight quotes not smart quotes. 

Get Outlook for iOS

 

---

From: ms_access_professionals@yahoogroups.com on behalf of qingqinga@yahoo.com [MS_Access_Professionals] <ms_access_professionals@yahoogroups.com>
Sent: Wednesday, January 23, 2019 7:28 PM
To: ms_access_professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Top right comma won't be inserted .

 

Dear Duane,
After running the debug.print, I got the result as following, by the way I w

INSERT INTO tblCityPlaceToVisit(DayCityID,CPTVGroupID,SSSeqNo,ScenicSpotID,PlaceName,PlaceChineseName,EntranceFee,EntranceCurrencyID,BuyTicket) VALUES(60,1,4,346,'Grande Place'','布鲁塞尔广场','0',2,'0')

By the way, I deliberately add a single quote on Grande Place (Grande Place') to test if a text with single quote, it didn't pass the data to target table. Please help. Thanks.

Best Regards,
Kevin


--------------------------------------------
On Thu, 1/24/19, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Subject: Re: [MS_AccessPros] Top right comma won't be inserted .
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Date: Thursday, January 24, 2019, 12:07 AM


 













Kevin,





Always add debug.print when you are developing or
troubleshooting:







debug.Print strSQL

CurrentDb.Execute
strSQL, dbFailOnError







What
do you see in the debug window?





You
often need to replace a single quote with two single quotes
in your SQL statements but I'm not sure this is your
issue since you are pulling most of the fields values from
another record.





Regards,

Duane

From:
MS_Access_Professionals@yahoogroups.com
<MS_Access_Professionals@yahoogroups.com> on behalf of
Qingqing qingqinga@yahoo.com [MS_Access_Professionals]
<MS_Access_Professionals@yahoogroups.com>

Sent: Wednesday, January 23, 2019 5:13 AM

To: MS_Access_Professionals@yahoogroups.com

Subject: [MS_AccessPros] Top right comma won't be
inserted .
 


Dear
All,

I've been using SQL Insert method for quite a while, and
always get the best result I wanted, except for this one,the
text with top right comma " ' " , it will stop
the SQL insert. Can this be solved?




Here is the code:

Dim strSQL As String

strSQL = "INSERT INTO tblSubRoomingList (SeqNo,
CityBookingID, SRLGroupID, ChineseName, EnglishName,
GuestGender, PassportNumber, " & _

"DateOfBirth, Birthplace, PlaceOfIssue, IssuingDate,
ExpiryDate, GuestType, RoomType, Remark) " & _

"SELECT tblRoomingList.SeqNo, " &
Nz(Me.CityBookingID, 0) & ", " &
Me.CBGroupID & ", tblRoomingList.ChineseName,
" & _

"tblRoomingList.EnglishName,
tblRoomingList.GuestGender, tblRoomingList.PassportNumber,
tblRoomingList.DateOfBirth, " & _

"tblRoomingList.Birthplace,
tblRoomingList.PlaceOfIssue, tblRoomingList.IssuingDate,
tblRoomingList.ExpiryDate, " & _

"tblRoomingList.GuestType, tblRoomingList.RoomType,
tblRoomingList.Remark " & _

"FROM tblRoomingList " & _

"WHERE tblRoomingList.GroupID = " &
Me.CBGroupID



CurrentDb.Execute strSQL, dbFailOnError

Me.Requery





If the "EnglishName" contains " ' "，It
won't start the process. Please help! Thanks.



Best Regards,

Kevin







------------------------------------

Posted by: Qingqing <qingqinga@yahoo.com>

------------------------------------





------------------------------------



Yahoo Groups Links



    http://groups.yahoo.com/group/MS_Access_Professionals/



    Individual Email | Traditional



    http://groups.yahoo.com/group/MS_Access_Professionals/join

    (Yahoo! ID required)



    MS_Access_Professionals-digest@yahoogroups.com

   
MS_Access_Professionals-fullfeatured@yahoogroups.com



   
MS_Access_Professionals-unsubscribe@yahoogroups.com



    https://info.yahoo.com/legal/us/yahoo/utos/terms/


















#yiv4306276588 #yiv4306276588 --
#yiv4306276588ygrp-mkp {
border:1px solid #d8d8d8;font-family:Arial;margin:10px
0;padding:0 10px;}

#yiv4306276588 #yiv4306276588ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv4306276588 #yiv4306276588ygrp-mkp #yiv4306276588hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}

#yiv4306276588 #yiv4306276588ygrp-mkp #yiv4306276588ads {
margin-bottom:10px;}

#yiv4306276588 #yiv4306276588ygrp-mkp .yiv4306276588ad {
padding:0 0;}

#yiv4306276588 #yiv4306276588ygrp-mkp .yiv4306276588ad p {
margin:0;}

#yiv4306276588 #yiv4306276588ygrp-mkp .yiv4306276588ad a {
color:#0000ff;text-decoration:none;}
#yiv4306276588 #yiv4306276588ygrp-sponsor
#yiv4306276588ygrp-lc {
font-family:Arial;}

#yiv4306276588 #yiv4306276588ygrp-sponsor
#yiv4306276588ygrp-lc #yiv4306276588hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv4306276588 #yiv4306276588ygrp-sponsor
#yiv4306276588ygrp-lc .yiv4306276588ad {
margin-bottom:10px;padding:0 0;}

#yiv4306276588 #yiv4306276588actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

#yiv4306276588 #yiv4306276588activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#yiv4306276588 #yiv4306276588activity span {
font-weight:700;}

#yiv4306276588 #yiv4306276588activity span:first-child {
text-transform:uppercase;}

#yiv4306276588 #yiv4306276588activity span a {
color:#5085b6;text-decoration:none;}

#yiv4306276588 #yiv4306276588activity span span {
color:#ff7900;}

#yiv4306276588 #yiv4306276588activity span
..yiv4306276588underline {
text-decoration:underline;}

#yiv4306276588 .yiv4306276588attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}

#yiv4306276588 .yiv4306276588attach div a {
text-decoration:none;}

#yiv4306276588 .yiv4306276588attach img {
border:none;padding-right:5px;}

#yiv4306276588 .yiv4306276588attach label {
display:block;margin-bottom:5px;}

#yiv4306276588 .yiv4306276588attach label a {
text-decoration:none;}

#yiv4306276588 blockquote {
margin:0 0 0 4px;}

#yiv4306276588 .yiv4306276588bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv4306276588 .yiv4306276588bold a {
text-decoration:none;}

#yiv4306276588 dd.yiv4306276588last p a {
font-family:Verdana;font-weight:700;}

#yiv4306276588 dd.yiv4306276588last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#yiv4306276588 dd.yiv4306276588last p
span.yiv4306276588yshortcuts {
margin-right:0;}

#yiv4306276588 div.yiv4306276588attach-table div div a {
text-decoration:none;}

#yiv4306276588 div.yiv4306276588attach-table {
width:400px;}

#yiv4306276588 div.yiv4306276588file-title a, #yiv4306276588
div.yiv4306276588file-title a:active, #yiv4306276588
div.yiv4306276588file-title a:hover, #yiv4306276588
div.yiv4306276588file-title a:visited {
text-decoration:none;}

#yiv4306276588 div.yiv4306276588photo-title a,
#yiv4306276588 div.yiv4306276588photo-title a:active,
#yiv4306276588 div.yiv4306276588photo-title a:hover,
#yiv4306276588 div.yiv4306276588photo-title a:visited {
text-decoration:none;}

#yiv4306276588 div#yiv4306276588ygrp-mlmsg
#yiv4306276588ygrp-msg p a span.yiv4306276588yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv4306276588 .yiv4306276588green {
color:#628c2a;}

#yiv4306276588 .yiv4306276588MsoNormal {
margin:0 0 0 0;}

#yiv4306276588 o {
font-size:0;}

#yiv4306276588 #yiv4306276588photos div {
float:left;width:72px;}

#yiv4306276588 #yiv4306276588photos div div {
border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}

#yiv4306276588 #yiv4306276588photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#yiv4306276588 #yiv4306276588reco-category {
font-size:77%;}

#yiv4306276588 #yiv4306276588reco-desc {
font-size:77%;}

#yiv4306276588 .yiv4306276588replbq {
margin:4px;}

#yiv4306276588 #yiv4306276588ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}

#yiv4306276588 #yiv4306276588ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean,
sans-serif;}

#yiv4306276588 #yiv4306276588ygrp-mlmsg table {
font-size:inherit;font:100%;}

#yiv4306276588 #yiv4306276588ygrp-mlmsg select,
#yiv4306276588 input, #yiv4306276588 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#yiv4306276588 #yiv4306276588ygrp-mlmsg pre, #yiv4306276588
code {
font:115% monospace;}

#yiv4306276588 #yiv4306276588ygrp-mlmsg * {
line-height:1.22em;}

#yiv4306276588 #yiv4306276588ygrp-mlmsg #yiv4306276588logo {
padding-bottom:10px;}


#yiv4306276588 #yiv4306276588ygrp-msg p a {
font-family:Verdana;}

#yiv4306276588 #yiv4306276588ygrp-msg
p#yiv4306276588attach-count span {
color:#1E66AE;font-weight:700;}

#yiv4306276588 #yiv4306276588ygrp-reco
#yiv4306276588reco-head {
color:#ff7900;font-weight:700;}

#yiv4306276588 #yiv4306276588ygrp-reco {
margin-bottom:20px;padding:0px;}

#yiv4306276588 #yiv4306276588ygrp-sponsor #yiv4306276588ov
li a {
font-size:130%;text-decoration:none;}

#yiv4306276588 #yiv4306276588ygrp-sponsor #yiv4306276588ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}

#yiv4306276588 #yiv4306276588ygrp-sponsor #yiv4306276588ov
ul {
margin:0;padding:0 0 0 8px;}

#yiv4306276588 #yiv4306276588ygrp-text {
font-family:Georgia;}

#yiv4306276588 #yiv4306276588ygrp-text p {
margin:0 0 1em 0;}

#yiv4306276588 #yiv4306276588ygrp-text tt {
font-size:120%;}

#yiv4306276588 #yiv4306276588ygrp-vital ul li:last-child {
border-right:none !important;
}
#yiv4306276588



------------------------------------
Posted by: <zhao.liqing@yahoo.com>
------------------------------------


------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___