Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303

    Unanswered: The object name has a ( in it.

    How do I refer to an object's name when it has a special character as part of the name?

    There are dozens of object names like "Edit(Invoice)"
    I wish they named it "Edit-Invoice", but they didn't.


    strOldDescription = DLookup("Description", "[1_tblMasterVersionControl]", _
    "(MasterReleaseNumber ='" & strOldRev & "') AND " & _
    "(ObjectName ='" & rs!ObjectName & "')")


    My code only works on object names without the goofy special characters. Because of object names with parentheses, I get "invalid use of null" because VB is concluding the DLookup criteria too soon.

    Is there a way to treat the "(" or ")" like a string and not like some VBA special text? Help please?
    Last edited by jpshay; 01-25-08 at 14:37.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm not sure that's your problem. The statement looks malformed to me. Missing " before ...(MasterReleaseNumber =.....??

  3. #3
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Very observant. However, I just quickly threw that together for an example. My question/problem still exists.

    I think I'll edit the first submission so that syntax problem you found won't distract others.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You change the bjec name to avoid special characters

    The workaround-which I'm notthriled about sharing here (do it right, please!) is to use square parens aroud the [name].
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Thanks georgev. Sometimes I have to use square brackets in my code (e.g. if a person uses a space in an object name).

    In this case, I am building a Dlookup string. The criteria for Dlookup is created with a variable as I loop through all the database objects (tables, queries, reports, etc.) The square bracket idea did not work.

    When rs!ObjectName may have a parenthesis in it, how do I create this...

    strOldDescription = DLookup("Description", "[1_tblMasterVersionControl]", _
    "(MasterReleaseNumber ='" & strOldRev & "') AND " & _
    "(ObjectName ='" & rs!ObjectName & "')")
    Last edited by jpshay; 01-25-08 at 15:08.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jpshay
    Very observant. However, I just quickly threw that together for an example. My question/problem still exists.
    Please don't ever just type away in the forum window if you have an error - copy and paste your code exactly. I once helped someone debug 100 lines of VBA code here. It turned out that the problem was a single missing fullstop ("."). We could never have found this if he had not copied and pasted the exact fragment.

    So the code in your last post is exactly as is and fails yes?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There is another error in your code.... (I hope you copied and pasted )

    EDIT - my mistake - no syntax error in your code - my misread.

    If that line is still giving you an invalid use of null it is because no record in the table meets the criteria. It is not because of any premature conclusion of the function. You can have whatever characters you like as the data in a table - Access will handle it just fine. You can wrap the DLOOKUP() function up in the NZ() function to account for any null returns.

    HTH
    Last edited by pootle flump; 01-28-08 at 05:08.

  8. #8
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Thanks again for your help, pootle flump, - if that is your name.

    I was misinterpreting the problem as relating to a parenthesis. My mistake. Nz to the rescue.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^^ No way that's his name!!

    http://en.wikipedia.org/wiki/The_Flumps
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Of course it's my name. Here's some pictures of me with my family:
    http://www.thechestnut.com/flumps.htm

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    LOL

    You're closely related to the tribble family aren't you?!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by pootle flump
    Of course it's my name. Here's some pictures of me with my family:
    http://www.thechestnut.com/flumps.htm
    Are you adopted?

  13. #13
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Cool. I've never met someone small and fuzzy who has such great MDB skills. I see you only have three fingers. Do you find it difficult to type?
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I struggle reaching SHIFT and some of the middle keys but otherwise I am ok.

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    He uses two dialing sticks; it's slow and steady progres
    George
    Home | Blog

Posting Permissions

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