Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - FixMixedQuotes.mdb
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Please consider donating $1 per sample downloaded, (find out why here)


 Post Reply Post Reply
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP

Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: FixMixedQuotes.mdb
    Posted: 09 Jul 2008 at 10:40pm

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 

Back to Top
Sponsored Links

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.