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

 

Dear Duane,

Thanks a lot. Yes, I think I have to get used to debug.print. By the way, the single quote is not part of the SQL, but a part of the actual text needed to pass on to target table after running the sql. Thanks again,

Best Regards,

Kevin

发自 WPS邮箱客戶端

在 "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2019年1月24日 00:58写道：

 

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

<*> 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 (3)

---

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

.

__,_._,___