Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Search field (xp)

    I have two fields Address and PostCode

    I want to see if the data in PostCode is in the Address field. I want Field Result1 to show me in a query if it is in or not

    <pre>Address | PostCode | Result1
    123 Acacia Avenue SW1 6TY | SW1 6TY | Yes
    24 Petunia Drive SW1 5GH | SW2 5TH | No

    </pre>


    I just can't think this afternoon how to do this I have tried *[PostCode]* as a parameter but my result differ from
    when I did this in Excel with the search function. Can you help please?
    Jerry

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search field (xp)

    Are the postal codes always 6 chars with a space (7 chars)???? If they are you could try:

    IIF(RIGHT([Address],7) = [Postal Code], "YES", "NO")... This is untested though!
    Regards,
    Rudi

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search field (xp)

    Its always so obvious when you spell it out Hans! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search field (xp)

    Thank you Hans, it's a been a long day. I did the top one as it provides me with a nice "clean" set I can work on, but that is my preference for this bit of work <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    PS You may need to add a closing parenthesis to it though, just to tidy it up
    Jerry

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

    Re: Search field (xp)

    Thanks, will do that.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search field (xp)

    Not to be pedantical but,

    shouldn't it be:
    Result1: InStr([Address],[PostCode])>0

    rather than:
    Result1: InStr([Address],[PostCode]>0)
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search field (xp)

    Gary

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Yes Option 1 worked for me
    Jerry

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

    Re: Search field (xp)

    Aaargh! Thanks!

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

    Re: Search field (xp)

    Edited by HansV to add missing closing parenthesis, as pointed out by Jerry, and again to put parenthesis in the correct place, as pointed out by Gary. Thanks, guys.

    You can use
    <code>
    Result1: InStr([Address],[PostCode])>0
    </code>
    or
    <code>
    Result1: [Address] Like "*" & [PostCode] & "*"</code>

Posting Permissions

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