Apostrophe: Finding Data in a Table

[Sorrells]

Greetings,

I have looked through the discussions with this as a keyword and have not found a fix to my problem.

In general it is this. I have two tables LOOKUP and ORDERD. In lookup are some records that have apostrophes in them. In my form I allow the user to select a record from LOOKUP to add to ORDERED. This data can only be entered into ORDERED once so I check the table prior to writing the record. In the same respect, I allow the user to delete a record from ORDERED if they desire.

This works fine in all respects unless the data text includes an apostrophe. My problem manifests with the FindFirst method because I cannot get my string criteria correct. I am beginning to think this is impossible since by necessity for comparison, there has to be an odd number of single quotes at best and this trips an error in FindFirst.

The solution I found here, adds another single quote to the text but then the comparison fails.

I am beginning to think my only solution is to loop through all of the text in the ORDERED table when the text-value in the LOOKUP table has an apostrophe. For both I could change the apostrophe to something like "XXX" and then perform the comparison, and keep looping until all records are checked. Since there should be no more than 50 records with text less than 50 characters, this seems doable. I just keep thinking there is a better solution.

Any thoughts and/or suggestions will be greatly appreciated.

Regards, Sorrells

 

[cpod]

Just put double quotes around it:

rst.FindFirst "description = """ & Me!Text1 & """"

This will find the first record were descrption equals the text in Text1 whether it has an apostrophe in it or not.

 

[Sorrells]

Cpod,

You know, I did not try your solution although I have added it to my notes for the next time (which may be today!).
I did solve the problem. Elsewhere on this discussion [search on apostrophe] a nice little function that will search the string and identify where the apostrophe is located.
Finally realizing that when add the record to the table, it did not matter; my only problem remained in deleting the record with the apostrophe as the entire recordset must be searched and compared to the user selected one for deletion.
I used this function and replaced the apostrophe with "XXX" both in the user-selected string and for every text in the recordset that had the apostrophe. This was of course under secondary variable names. Once the match was found, the delete could take place.
Obviously, if I had tested and agreed with your statement, some coding could have been avoided.
Many thanks for your response!

Regards, Sorrells