Query help (A2k)

Topic

New Reply

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.

Reply | Quote

Replies

Instead of:

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

Try this:

WHERE instr([ClientStatus],tblLetters.LetterGroup)>0

Reply | Quote

Mark,

That doesn’t work for me, but maybe I’m doing something wrong. What exactly is your WHERE clause supposed to do?

Reply | Quote

Put it in place of your existing WHERE clause!

Reply | Quote

[indent]

---

What exactly is your WHERE clause supposed to do?

---

[/indent]

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.

Reply | Quote

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

IIf([ClientStatus] = “Insured – Shareholder”,”Insured” Or “Shareholder”,[ClientStatus])

Reply | Quote

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])

Reply | Quote

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.

Reply | Quote

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 Thank you for looking in and helping with this.

Reply | Quote

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.

Reply | Quote

I don’t understand how it’s going to help me.

Reply | Quote

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:

WHERE [Forms]![FormName]![ClientStatus] Like "*" & [LetterGroup] & "*"

or

WHERE InStr([Forms]![FormName]![ClientStatus],[LetterGroup]) > 0

Reply | Quote