Results 1 to 8 of 8
  1. #1
    nosiss is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    4

    check and return specified text

    Hi
    im new in A2010 and i wanna to create query that return for me specified value in searched column.

    i have table that in one Field (TEXT) i have something like that:

    123,44,1,,23,qwe = qwe
    qwu,234,.123,5123 = qwu


    12.,1231,21,63,323 = nothing

    and i need to create new field in query who will check every cell and if it finds a certain word it typed them. it looks like after the equal sign

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    This looks like multi-value field. Why? Is this deliberate design or something forced on you?
    Might be able to use DLookup() function. Access Help has info on this and other data aggregate functions (DSum, DCount, DMax, DMin, DAvg).
    Or a nested subquery.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    nosiss is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    4
    only how to use this function to display something like this, in column ID1 (attached)

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Oh, not searching for a specific value, you want to extract substring. Use string manipulation. Try
    ID1: Mid([ID],InStr([ID],"q"),3)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    nosiss is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    4
    and how to do that gives entire value. if it found qwe or qwr, and not starting with q
    can i do something like that:
    ID1: Mid([ID],InStr([ID],"qwe"),"qwe") - if it found qwe it returns qwe

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Please clarify "and how to do that gives entire value. if it found qwe or qwr, and not starting with q", I don't understand this statement.

    Mid function fails when InStr returns 0 because searched string is not present. This should result in #Error display for those rows but other rows should return found substring. So my suggestion did not work? Your modification should not do any better. And it would not find other substrings. I should have asked earlier, do all of the substrings begin with q and always 3 letters?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    nosiss is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    4
    I noticed that in this column ID i can find such examples:
    123,q3,4411,qwe,2312
    or
    q,123,123,634,qwe

    by your function it return for me not exactly what I wants

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Okay, so will the substring you want always be 3 letters, first 2 always qw? There has to be a pattern that can be depended on or nothing will work.

    Mid([ID],InStr([ID],"qw"),3)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-11-2011, 02:24 PM
  2. Using Caluculated fields to return text
    By InsaneJammer in forum Access
    Replies: 8
    Last Post: 04-08-2011, 07:52 AM
  3. Replies: 1
    Last Post: 11-05-2010, 04:31 PM
  4. Check box return value
    By nbryant in forum Queries
    Replies: 1
    Last Post: 10-26-2010, 12:24 PM
  5. hide text box till you un-tick check box
    By islandboy in forum Access
    Replies: 14
    Last Post: 09-06-2009, 10:08 AM

Posting Permissions

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