Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Sorting and quotation marks (97 & XP)

    My "Catalog" table has a field that I want to sort from top to bottom. The Item field lists the goods or services donated by various businesses and persons to a fund-raiser. Some of the items are paintings or other artworks that have names or titles enclosed in quotation marks. Other items are gift certificates with listings that start with, e.g., "$20 gift certificate..." (without quotes). When I sort this field, I get some really screwy results. To wit:

    "Above Hallowed Ground"
    "Get With the Program"

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sorting and quotation marks (97 & XP)

    What exactly is in your table?
    It will sort on the quotes as well.
    Post your DB and we can see why, do the usual and take out any sensitive data.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting and quotation marks (97 & XP)

    I'm attaching a copy of the DB. Please look at the table named tbl_Catalog and see what results you get when you try to sort the Item field alphabetically.

    Thanks!
    Attached Files Attached Files

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Sorting and quotation marks (97 & XP)

    I'd reckon you're right about the so-called "Smart Quotes" originating in Word - that's one of the first AutoCorrect "features" I disable when configuring Word.... I'd recommend update query to convert "Smart Quotes" to plain ole Dumb Quotes. These are a couple of sample functions to "dumb down" the Smart Quotes:

    If using A2K or later, use Replace function (simplest approach):

    Public Function ReplaceSmartQuotes1(ByVal strTxt As String) As String

    ' "Dumb Quotes" Char code: Chr(34)
    ' "Smart Quotes" Char codes: Chr(147), Chr(148)
    ' Note: Replace function available in VB/VBA 6.0 (A2K or later)

    strTxt = Replace(strTxt, Chr(147), Chr(34), , , 0)
    strTxt = Replace(strTxt, Chr(148), Chr(34), , , 0)

    ReplaceSmartQuotes1 = strTxt

    End Function

    If using Access 97, Replace function is not available; function was introduced in VB/VBA 6.0. Instead, use Mid Statement:

    Public Function ReplaceSmartQuotes2(ByVal strTxt As String) As String

    ' If using Access 97 use Mid statement (not function!) in place of Replace
    ' Assumes only 1 pair of "Smart Quotes" per text string

    Dim intStart As Integer

    intStart = InStr(1, strTxt, Chr(147), 0)
    If intStart > 0 Then
    Mid(strTxt, intStart, 1) = Chr(34)
    End If

    intStart = InStr(1, strTxt, Chr(148), 0)
    If intStart > 0 Then
    Mid(strTxt, intStart, 1) = Chr(34)
    End If

    ReplaceSmartQuotes2 = strTxt

    End Function

    Note the 2nd function is a little more complicated because you have to first test for string to be replaced to avoid errors or bogus results. Using either function in query will sort items in correct order. Example:

    SELECT tbl_Catalog.ItemName, ReplaceSmartQuotes1([ItemName]) AS Expr1, ReplaceSmartQuotes2([ItemName]) AS Expr2
    FROM tbl_Catalog
    ORDER BY ReplaceSmartQuotes2([ItemName]);

    As noted recommend use update query with one of above functions to replace the socalled "Smart Quotes". Also, you can modify either function to replace both smart & dumb quotes with a zero-length string if you want to sort all items w/o quotes, smart or otherwise.

    PS: Item # 206 would be a hot item around these parts....

    HTH

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Hidden 'Smart Quotes' in Data (97 & XP)

    <!profile=Caesar3>Caesar3<!/profile> posted the following paraphrased problem to start this thread:
    My "Catalog" table has a field that I want to sort from top to bottom. ... When I sort this field, I get some really screwy results. To wit:
    "Above Hallowed Ground"
    "Get With the Program"
    Wendell

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sorting and quotation marks (97 & XP)

    While I was looking at this problem, I thought I would make an Update query to change these characters to Chr(34) characters.
    When I ran the query it popped up a dialog box and asks if I want to update 11 records. I said NO and it went ahead and updated them anyway. I am presuming this because I ran it again and it said there was 1 record to be updated and again I said NO. etc,etc until there were 0 records to be updated.

    Am I missing something here?
    Pat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Sorting and quotation marks (97 & XP)

    What SQL did you use for update query, & which function did you use?? If you used first function (with Replace) you would only need to run update once for the 11 bogus records. If you used 2nd function (with Mid) then you would have to run update query twice: the 1st time 11 records are updated; the 2nd time, 1 record. That's because one record (ID = 134; Item = Registration for two people for "Learning about Wine" or "Basic Brewing" classes.) has 2 sets of "smart quotes"; the ReplaceSmartQuotes2 function shown in previous reply only changes one set of quotes (see modified version below). I tested update query with both functions using SQL like this:

    UPDATE tbl_Catalog SET tbl_Catalog.ItemName = ReplaceSmartQuotes1([ItemName])
    WHERE (((InStr(1,[ItemName],Chr(147),0))>0)) OR (((InStr(1,[ItemName],Chr(148),0))>0));

    In neither case did the update query update any records if I clicked "No" in response to standard "You are about to update x row(s)...." warning. I don't have an answer for that one - that's another issue altogether.

    NOTE: Revised version of ReplaceSmartQuotes2 function to account for possibility of more than one set of smart quotes in text string:

    Public Function ReplaceSmartQuotes2(ByVal strTxt As String) As String

    Dim intStart As Integer

    Do
    intStart = InStr(1, strTxt, Chr(147), 0)
    If intStart > 0 Then
    Mid(strTxt, intStart, 1) = Chr(34)
    Else
    Exit Do
    End If
    Loop

    Do
    intStart = InStr(1, strTxt, Chr(148), 0)
    If intStart > 0 Then
    Mid(strTxt, intStart, 1) = Chr(34)
    Else
    Exit Do
    End If
    Loop

    ReplaceSmartQuotes2 = strTxt

    End Function

    The revised function ran OK in test with original table; sorted Item field in correct order relatively quickly, though be advised that sorting on an expression (especially one using a user-defined function like this) will never be as efficient as sorting on an indexed field in table; another good reason to update table. Also this shows how the Replace function can simplify things when manipulating text strings; the ReplaceSmartQuotes1 function is simpler & quicker, since there's no additional testing or loops involved. For the record I tested this in AXP with downloaded db in A2K format.

    HTH

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sorting and quotation marks (97 & XP)

    I understand about the mid vs replace etc, but thank you for the explanation anyway.

    You wrote:
    >>In neither case did the update query update any records if I clicked "No" in response to standard "You are about to update x row(s)...." warning. I don't have an answer for that one - that's another issue altogether. <<
    I was only really asking about this question, no matter if I answered Yes or No it went ahead and updated.

    Anyone else?
    Pat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Sorting and quotation marks (97 & XP)

    Are you using Access 97? I vaguely remember having a problem with this in A97 - haven't used A97 in a long time. If so, recommend refer to this MSKB article:

    ACC97: Action Query Commits Changes to Data When Cancelled

    As article states, "This behavior is by design." The stated cause: "The action query's UseTransaction property is set to No." The "Resolution" provided: "Set the UseTransaction property to Yes before running the action query. " See article for more information. I'm not sure if this occurs in later versions of Access; I haven't encountered this problem in A2K. But if this behavior is "by design" then if you ask me the "design" is dumb - the warning msg is a liar!!

    HTH

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sorting and quotation marks (97 & XP)

    Mark, thanks for that.

    How dumb, a "feature" not a bug, Ha Ha.

    I'm getting back to you late because I have just been watching the Australian Grand Prix on TV.

    Pat

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting and quotation marks (97 & XP)

    Mark, Wendell, Patt et al: Reading your posts has been yet another educational experience for me. Before I encountered this problem, I knew kinda-sorta what "smart quotes" are, but I had absolutely no idea that smart quotes would derail a sorting scheme in Access!

    Mark, I know just enough about VBA and SQL to be a danger to myself and others. I've "written" a little code, but always with the help of people like yourself and other experts in this forum. I could never have gotten as far as I have without your help! In this event, I must ask for just a little more help.

    I'd like to update the table with an update query, as you and others have suggested. I have AXP at work and at home; the college campus uses AXP. However, the primary user works on the project at home, where she uses A97. So...I want to do all the fixing in AXP. When I've got things working the way they should, I'll convert to a dumbed-down version (97) and send it to her. Having said all that, what do I do now? I see the code you built, but I'm not sure how to add it to my project. I know, for example, how to create an "After Update" event procedure for a control on a form. But how do I employ your code? I'm afraid I don't have a clue how to proceed.

    Thanks for the help!

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Sorting and quotation marks (97 & XP)

    Hi Lucas,
    My advice would be to simply create a query like I did when I determined what the problem was. Once you've done that you can quickly see which records contain the smart quotes, and simply edit the entries by hand. I'm making the recommendation presuming what you attached on your subsequent post was the complete table, or at least a significant part of it. If you had hundreds of them, then writing the VBA as Mark suggested would be the more efficient choice.

    On the other hand, if you want to make it a learning experience, I would suggest you do it on your home PC. All you really have to do is take Mark's code, create a new module, paste his code into it, and save the module with whatever name you like, and then create the query he suggests by referencing the function in that query.

    Actually, to fix the basic problem - which is with the leading quotes, all you really need to do is create an update query that selects only those records where the leading character is 147. The remaining quotes in the middle or at the end of strings are not likely to cause any real problem from a sorting perspective. To do that, put the expression
    <font face="Georgia"><font color=blue>Chr$(147) & Mid([ItemName],2,Len(ItemName)-1)</font color=blue></font face=georgia>
    in the update to area for ItemName. Caution - for any of the automated approachs, be sure to do it in a copy of the database, or at least to a copy of the live table as a test before you run it against live data. Let us know if you need further guidance.
    Wendell

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting and quotation marks (97 & XP)

    A-ha! A module! Well, Wendell, I think I'll turn this into a learning experience, thanks to your help. I've never built a module before.

    I ran the query as you suggested, and sho' 'nuff, there are those 147s, just like you said. I've got maybe a dozen or so to fix, and it's really as simple as deleting the leading "smart quote" and replacing it with a dumb quote. Gee, y'know--you can't tell the smart ones from the dumb ones just by looking at them! This is fascinating stuff...!

    Thanks again!

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting and quotation marks (97 & XP)

    OK, I've cleaned up those smart quotes, and things are looking better...but we're still not where I want to be. Here's how the sorting looks:

    All item names enclosed in quotation marks (dumb quotes) appear first. The one name that starts with a number ("30 Meals in 30 Minutes") heads the list. Then it goes from A to W ("WWII: A Tribute in Art and Literature").

    Next come the 11 items that start with the US dollar sign ($20 gift certificate...).

    The third "cluster" includes items with names that begin with some sort of number (1/10 oz. 1999 Golden Eagle coin; 14K gold charm bracelet). These seem to be sorted within the cluster by first digit.

    The fourth cluster: Everything else, starting with the letter A.

    This is much better than what I had before, but is this what I'll have to live with? I can just hear it now: "Why can't we get the book and picture titles alphabetized with the rest of the items? That's the way we've always done it!" Somewhere I know there must be a Knowledge Base article or something that explains how Office or Windows sorts this kind of stuff. Can anyone point me to it?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting and quotation marks (97 & XP)

    If you want to change the sort order, you must add a field to the query based on your Catalog table. This field contains an expression that defines the desired sort order. Note: it should *only* be used to set the sort order, not for displaying the data.

    Here is an expression that removes leading (straight) quotes

    ItemOrdered:Mid([ItemName],1-(Left([ItemName],1)=""""))

    If you order your query or report by ItemOrdered, "WWII" will be sorted together with WWII, and "30 Meals" with 30 Meals. Numbers will still be sorted as characters, that is 1, 10, 100, 11, 12, ..., 2, 20, ... If you want them to be sorted numerically (1,2, ..., 10, 11, 12, ... 20, ...100), or alphabetically as if they were written out fully (30 sorts with Thirty, 11 sorts with Eleven), you would need to write a custom function. This would be quite complicated.

    Explanation for the above expression:

    Left([ItemName],1)="""" compares the first character of ItemName to a double quote. To put a double quote inside a string, we must use two double quotes, otherwise it would be interpreted as the end of the string; together with the beginning and ending double quotes we get 4 dobule quotes! Anyway, Left([ItemName],1)="""" returns True = -1 or False = 0.

    1-(Left([ItemName],1)="""") returns 1-(-1) = 2 if the first character is a quote, 1-0 = 1 if the first character is something else.

    So Mid([ItemName],1-(Left([ItemName],1)="""")) becomes Mid([ItemName],2) if the first character is a quote and Mid([ItemName],1) otherwise. The Mid function used this way returns all characters from the string starting a the position specified, so you get everything but the first character if that is a double quote, and the entire string otherwise.

    This may seem a convoluted way to do it, but I wanted to avoid the use of the inefficient IIf function. A direct calculation is much faster than an IIf.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •