Sabtu, 26 Januari 2019

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

 

Dear Duane,
You are right. If I change the single quote to double single quote, the problem will be solved, thanks a lot.
Best Regards,
Kevin

发自 WPS邮箱客戶端
在 "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2019年1月24日 23:18写道：

 

Kevin,
Your code and your example debug.print are not aligned. They are from different tables and use a different method (VALUES vs SELECT). Please provide accurate information so we can assist.

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

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

Duane
From: MS_Access_Professionals@yahoogroups.com <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:25 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: qingqinga <qingqinga@yahoo.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (7)
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.

SPONSORED LINKS
.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)