FixMixedQuotes (Updated On 14-Dec-2009)
Explanatory Notes

    This sample db demonstrates handling of embedded single and / or double quotes in VBA strings

    Typically, the situation arises when contents of a text box, containing embedded quotes in miscellaneous combinations (single, double or both), are sought to be used in any of the following:
    (a) Append query.
    (b) Update query.
    (c) Where clause of a query - Exact Match.
    (d) Where clause of a query - Partial Match (Like operator).
    (e) Filter string for form - Exact Match.   
    (f) Filter string for form - Partial Match (Like operator).

    For handling embedded quotes, alternative styles are demonstrated as follows:

    1 - Avoid fixing any quotes - By using direct reference to values held by form controls:
    1.1 - This is an extremely useful method, based upon form control reference embedded within the SQL string. Major advantages are:
    (a) No need to fix any embedded quotes, whether single or double or a combination of both.
    (b) No need to worry about specific delimiters while concatenating values for various data types (e.g. '  for text type and  #  for date type data).
    (c) No risk of dates getting mis-interpreted (in the process of concatenation into SQL string) when local system settings for short date are not as per US date format  (mm/dd/yyyy).
    (d) There is no problem if the value held by any of the form controls happens to be Null (provided destination table is in a position to accept Null value in the field concerned).

    1.2 - Three styles of direct reference to values held by form controls are demonstrated:
    (a) Normal style (Forms!FormName!ControlName embedded within SQL string) - This requires use of DoCmd.RunSQL method. In this case,  CurrentDb.Execute method if used, would attract error 3061 (Too few parameters. Expected 1).
    (b) Enclosing form reference within Eval() function embedded within SQL string. This is amenable to use of CurrentDb.Execute method.
    (c) Fetching form control value via wrapper function embedded within SQL string. This is amenable to use of CurrentDb.Execute method.

    2 - Fixing of embedded quotes prior to concatenation. This involves substitution of each occurrence of a given quote by  twin occurrence of the same. Two styles are demonstrated:
    (a) Fixing of embedded single quotes only. While concatenating the result, outer single quotes are to be used as per normal practice.
    (b) Fixing of embedded double quotes only. While concatenating the result, outer double quotes are adequate (outer single quotes are not to be used).

    Note: When fixing embedded quotes, only one type (either single or double - Not Both) is to be fixed as per 2 (a) or 2 (b).

    Version: Access 2000 file format.

    References: DAO 3.6