Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    105

    Unanswered: triky sql syntax question :(

    hi everybody,
    i would have a question.
    im working with an access 2003 database.
    in a table i have a field "field1" now this field has some entries like:
    abcdefg[1].abc[2]
    via a query i need to select all entries like 'abcdefg[1].abc[*'
    now the problem is that [ and ] have special meanings for sql but here i just need it as a string
    i tried to do it like 'abcdefg\[1\].abc\[*' but thats not working either
    does somebody know how to solve this problem ?
    thanks a real lot

  2. #2
    Join Date
    Dec 2003
    Posts
    268

    try attacking it from the other end

    I wonder if you could use a right() function

    right(fieldx,2) like '*]'

    Maybe that will work.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Try doubling the special characters ... Like: ...[[1]].ABC[[*...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Here's your original string: abcdefg[1].abc[2]

    Try the Chr() function, as follows:

    'abcdefg' & Chr(91) & '1' & Chr(93) & '.abc' & Chr(91) & '2' & chr(93)

    Sam

  5. #5
    Join Date
    Oct 2004
    Posts
    105
    Hi thanks for the suggestiones, i tried all of them but neither worked
    so far i only get it to work with abcdefg?2...
    but that of course means that i would get all strings with abcdefg then any other letter or symbol and then 2...

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Good morning,

    Three people made three diverse suggestions, and you said they didn't work. That's all right; no one's perfect. However, to satisfy our collective curiosity (I'm sure the other guys are as curious as I), would you mind copying and pasting the three non-working statements so we can identify the problems? Also, would you mind telling us the exact results, or error messages that resulted, from each? It's not too much trouble, I hope.

    Thanks,

    Sam

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just to muddy the waters further

    Code:
    WHERE MID(MyCol, 1, 15) = "abcdefg[1].abc["
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Oct 2004
    Posts
    105
    Hi all,
    sorry its been a while
    thanks for the suggestion pootle flump
    however i get the same as with the others -> an empty table
    guess replacing the [ ] with a different sign bevor saving the it in the database and then replacing it agin when displaying it will be the only thing working so far
    thanks for all your help though

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Just me

    The complication is mixing Like and wildcard characters - literal searches are fine. Have a look at my suggestion - either it works or I misunderstood you. Check out attached.

    HTH
    Attached Files Attached Files
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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