	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

9.1

Issue with apostrophe in query

Asked by Dezzar82 in Lotus Notes, SQL Query Syntax, Access Coding/Macros

Tags: query, apostrophe, access sql

Hi All,

I have setup a process loop that runs through a record set one by one to do various things.

Basically, the database imports new data from a spreadsheet daily, then the process loop selects each record, and based on a number of variables, creates windows folders, .txt files, sends an email and then moves the current to another table.

Its the part where a record gets added to an existing table thats creating the problem when the source data includes an apostrophe '

The data being added with the INSERT INTO query is already defined as a string and might be:
strContactName = "John O'Shea"

I cannot substitute the ' for another character, I cannot eliminate it, I must pass it to the table as is becuase it is translated to the persons email address which contains the apostrophe. Ie: "John.O'Shea@company.com" which is something I also pass but haven't included in the code example below.

There will not always be an apostrophe, so is there a way to change the syntax to deal with the apostrophe that will not affect other records not containing an apostrophe?

An example of the code portion is noted below:

View the Solution FREE for 30 Days

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

           
           
             '*** EXAMPLE SNIPET ****
 
'Select the record set
cSQL = " SELECT tbl_DailyDataImport.SystemID, tbl_DailyDataImport.QuoteNumber, tbl_DailyDataImport.CustomerName, tbl_DailyDataImport.Contact "
cSQL = cSQL & " FROM tbl_DailyDataImport "
 
'Process the record set in a loop
With rsImportProcess
        .MoveFirst
        Do Until rsImportProcess.EOF
        
        If .Fields(1) = this, then do that, etc etc etc
        
        'Also take the contact name and do some translation etc etc to end up with
        strContactName = "John O'Shea"
 
        'Then after processing various things, move the record to a new table
CurrentDb.Execute " INSERT INTO tbl_CurrentQuotes (SystemID, QuoteNumber, CustomerName, Contact)" & _
" SELECT '" & .Fields(0) & "','" & .Fields(1) & "','" & Replace(RTrim(.Fields(2)), "/", ".") & "','" & strContactName & "'"

           
         

20091118-EE-VQP-93 - Hierarchy / EE_QW_3_20080625

Hall of Fame

1. sjef_bos...506,454
2. SysExpert172,873
3. Bill-Hanson171,555
4. mbonaci122,855
5. brwwiggins48,793
6. fgrushev...40,685
7. akhafaf37,470
8. madhees...24,814
9. LotusDave24,155
10. daj_uk23,351
11. CRAK20,940
12. GregBussey19,237
13. mekhet3016,495
14. doninja14,014
15. qwaletee13,750
16. madgino12,350
17. cezarF12,332
18. sreeser9,000
19. Blackmoo...8,808
20. tomsparrow8,575
21. kcarrim8,500
22. lakshmidu...7,889
23. larsberntrop6,999
24. KeremE6,850
25. chuckalici...5,543

1. sjef_bos...2,055,732
2. qwaletee1,669,865
3. SysExpert1,234,180
4. Hemantha...1,122,137
5. Bill-Hanson712,403
6. marilyng624,963
7. madhees...605,884
8. Bozzie4417,223
9. mbonaci387,667
10. p_partha352,219
11. brwwiggins295,862
12. Zvonko277,807
13. CRAK238,547
14. Arunkumar195,091
15. fgrushev...191,455
16. cezarF132,084
17. zvonko130,604
18. RanjeetRain111,054
19. olaraak108,291
20. jerrith107,809
21. HappyFun...102,184
22. mshogren94,313
23. ghassan9990,401
24. dragon-it80,007
25. scottrma68,271

Issue with apostrophe in query

Asked by Dezzar82 in Lotus Notes, SQL Query Syntax, Access Coding/Macros

Tags: query, apostrophe, access sql

About this solution