Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24

Need help with "strCriteria" and HAVING clause

  1. #16
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    312
    Ok... I know why's it's crashes now. There are a few values (organizations) that have a single quote in their name.

    Is there a way I can use double-quotes in the strSQL? Or, if that's not doable, is there a work-around given the the single quote **within the string value** gets confused with the single quote at beginning/end of the text?

  2. #17
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    Should I start responding to "John", even though my name is Paul?

    The problem is in the data, but can be accommodated with code. The apostrophe in "Commander's" is terminating the string and causing a problem. Instead of:

    Code:
    strCriteria = "T11_CrossFunctionalTeam.CFT_Description = '" & Me!lstMET_CFTs.ItemData(varItem) & "'"
    try

    Code:
    strCriteria = "T11_CrossFunctionalTeam.CFT_Description = " & Chr(34) & Me!lstMET_CFTs.ItemData(varItem) & Chr(34)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #18
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    312
    Ok... I know now what caused the problem. Some organization have use a single quote in their org name. Based on some research, that single quote terminates the procedure.

    Please see picture below.

    My final question:
    How can I implement that "escaping the quote" in Access (i.e., my particular VBA shown above)?Click image for larger version. 

Name:	Delimiter.JPG 
Views:	10 
Size:	65.5 KB 
ID:	39511

  4. #19
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    275
    Tom, that's cause the problem.
    Code:
    T11_CrossFunctionalTeam.CFT_Description = 'Commander's Assessment Board'
    Also, take a look at this: https://www.dofactory.com/sql/having

    Cheers,
    John

    Edit: Please ignore this message. I missed your last messages.

  5. #20
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    312
    Paul -- first of all, I apologize for the name mix-up. Thank you for the continued support.

    And, yes, your last recommendation fixed the problem. All reports are now generated in secs w/o the VBA crashing. SUCCESS!!!!!!

  6. #21
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    275
    Also, you can say:
    Code:
    strCriteria = "T11_CrossFunctionalTeam.CFT_Description = '" & Replace(Me!lstMET_CFTs.ItemData(varItem),"'","''") & "'"

  7. #22
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    312
    I will join dofactory.com. Thank you agai, Paul.

  8. #23
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    312
    Thank you accesstos... appreciate the feedback as well.

  9. #24
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    Quote Originally Posted by skydivetom View Post
    Paul -- first of all, I apologize for the name mix-up. Thank you for the continued support.

    And, yes, your last recommendation fixed the problem. All reports are now generated in secs w/o the VBA crashing. SUCCESS!!!!!!
    No worries and happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. Replies: 2
    Last Post: 03-11-2015, 03:40 PM
  3. "AND" or "&" in the FROM clause of a query
    By kelann in forum Queries
    Replies: 21
    Last Post: 11-09-2012, 08:41 AM
  4. Custum Functions in Query "WHERE" Clause
    By trb5016 in forum Queries
    Replies: 1
    Last Post: 02-15-2012, 03:30 PM
  5. Report "WHERE" clause not working
    By rsearing in forum Reports
    Replies: 4
    Last Post: 05-26-2009, 02:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums