2 fields issue

[rockyjr]

Hi everyone,

I have a blank. Its been a while that I have played with queries. I created a simple query that would filter out from 2 different fields on a form. The only thing is that I can use either one or the other field. If I use both, the query doesnt work porperly. ... see code

SELECT Calls.[Opened Date], Calls.[Opened By], 
Calls.[PPD or TRAD], Calls.ID, Calls.Title
FROM Calls
WHERE (((Calls.[Opened Date]) Between [startdate] And [enddate]) 
AND ((Calls.[Opened By])=[openedby])) 
OR (((Calls.[PPD or TRAD])=[ppdtrad1]));

What I would like is the possibility to use, both fields to filter, or use them seperately.

I've been trying the "Like" command too... didnt work (ex: Like "*" & [ppdtrad1] & "*")


Thanks in advance for any advice,

Luc

 

[MSAccessRookie]

Hi everyone,

I have a blank. Its been a while that I have played with queries. I created a simple query that would filter out from 2 different fields on a form. The only thing is that I can use either one or the other field. If I use both, the query doesnt work porperly. ... see code

SELECT Calls.[Opened Date], Calls.[Opened By], 
Calls.[PPD or TRAD], Calls.ID, Calls.Title
FROM Calls
WHERE (((Calls.[Opened Date]) Between [startdate] And [enddate]) 
AND ((Calls.[Opened By])=[openedby])) 
OR (((Calls.[PPD or TRAD])=[ppdtrad1]));

What I would like is the possibility to use, both fields to filter, or use them seperately.

I've been trying the "Like" command too... didnt work (ex: Like "*" & [ppdtrad1] & "*")


Thanks in advance for any advice,

Luc

Not 100% sure what you mean but the following modification to your code allows you to select the correct item based on one of the two fields being a match. It does not handle neither one matching.

SELECT 
    Calls.[Opened Date], 
    Calls.[Opened By], 
    IIf(Calls.[PPD])=[ppdtrad1], Calls.[PPD], 
        IIf(Calls.[TRAD])=[ppdtrad1], Calls.[PPD], 
            {Something if Neither is true})) AS {YourFieldTitle},
    Calls.ID, 
    Calls.Title
FROM Calls
WHERE 
    Calls.[Opened Date] Between [startdate] And [enddate] AND
    Calls.[Opened By]=[openedby] AND
    ( ( Calls.[PPD])=[ppdtrad1] ) OR ( Calls.[PPD or TRAD]=[ppdtrad1] ) );

 

[rockyjr]

Thank you for the quick answer MSAccessRookie,

Actually, I think you got confused with my naming of my tables... ish!!! I could have done a better job on those......

[PPD or TRAD] is one field name in my table....

Here's a picture... may be better to understand:

Thanks again,

Luc

 

[rockyjr]

Anyone have any suggestions on this would be greatly appreciated.

Right now, I can have it working with one field by itself.... or when both are select (just by using "AND" instead of "OR" on my SQL). But I would like to use either.... if one field is selected... or both.

Thanks in advance,

Luc