Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query help (A2k)

    I've got a listbox with the following SQL:

    SELECT tblLetters.ID, tblLetters.FileName, tblLetters.LetterDescription, tblLetters.LetterGroup, tblLetters.LetterDate, tblLetters.LetterLocation
    FROM tblLetters
    WHERE (((tblLetters.LetterGroup) Like [ClientStatus]))
    ORDER BY tblLetters.LetterDate;


    The possible LetterGroup fields are:
    Accountant
    Shareholder
    Insured
    Prospect
    Prospect-Shareholder

    ClientStatus is a combo box that holds the same values as LetterGroup, only for specific companies.

    When a user is selected on a form, the listbox populates with possible letters from a table that need to be created/sent for that company. This table holds all letters, not specific letters for each type of client. The table is filtered by the LetterGroup field.

    My problem is that when a ClientStatus is Prospect-Shareholder, I want to return all letters whose LetterGroup is Prospect *and* Shareholder, vice copying each letter for Prospects and Shareholders twice so that they can be included when the ClientStatus is Prospect-Shareholder.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query help (A2k)

    Instead of:

    WHERE (((tblLetters.LetterGroup) Like [ClientStatus]))

    Try this:

    WHERE instr([ClientStatus],tblLetters.LetterGroup)>0
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help (A2k)

    Mark,

    That doesn't work for me, but maybe I'm doing something wrong. What exactly is your WHERE clause supposed to do?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query help (A2k)

    Put it in place of your existing WHERE clause!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help (A2k)

    <hr>What exactly is your WHERE clause supposed to do?<hr>

    Mark,
    I know where to put it, but I'm trying to decipher how your clause is different than mine. I'm trying to use one value returned as criteria to return two values worth of results.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help (A2k)

    I'm trying with an IIF statement, please correct my syntax if I'm wrong:

    IIf([ClientStatus] = "Insured - Shareholder","Insured" Or "Shareholder",[ClientStatus])
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help (A2k)

    I've tried the following variants, but all have only returned one set of letters:

    Variant 1:
    IIf([ClientStatus].[text]="Insured",([tblLetters].[LetterGroup])="Insured" Or ([tblLetters].[LetterGroup])="Shareholder",[ClientStatus])

    Variant 2:
    IIf([ClientStatus].[text]="Insured",([tblLetters].[LetterGroup])="Insured",[ClientStatus]) Or IIf([ClientStatus].[text]="Insured",([tblLetters].[LetterGroup])="Shareholder",[ClientStatus])
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query help (A2k)

    You use the IIF statement to return 1 of 2 values, depending on the situation. You aren't trying to do that here! You are trying to set a criteria to accept only certain records. Use the InStr clause I gave you.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query help (A2k)

    The InStr() function has this format: instr(string1, string2)

    It looks for the existence of the string2 within string1, then returns the starting position number (or 0 if not found).

    So in your situation, you merely check to see if the LetterGroup for a record is within ClientStatus you've specified. If a 0 is returned, then it wasn't it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help (A2k)

    <img src=/S/nope.gif border=0 alt=nope width=15 height=15>

    I don't understand how it's going to help me.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help (A2k)

    Mark,

    I want the query to check and see if ClientStatus = Insured. If it does, I want the query to return both sets of records, the ones whose LetterGroup = Insured, and the ones whose LetterGrou = Shareholder. I'm not at all familiar with InStr, if you could INSTRuct me on how to use it, it would be greatly appreciated <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Thank you for looking in and helping with this.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Query help (A2k)

    It would help if you provided a consistent description of your problem. Originally, you had Prospect-Shareholder as a special value, later it became Insured - Shareholder.
    Also, ClientStatus is variously described as a combo box and as a list box. Whichever it is, it is a control on a form, so you cannot just refer to ClientStatus in your query.
    Try the following, substituting the correct form name:
    <code>
    WHERE [Forms]![FormName]![ClientStatus] Like "*" & [LetterGroup] & "*"
    </code>
    or
    <code>
    WHERE InStr([Forms]![FormName]![ClientStatus],[LetterGroup]) > 0</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
  •