Pull two fields in one criteria

[mauricionava]

I have a query in which I enter a criteria that pulls it from a table with dealers, one of the dealers changed its name.

How can I set it up so when I enter either of those two name in the criteria it gives me all accounts from both names?

I just want to enter one name in the criteria, not both names everytime I want to run the query

thanks

 

[Golom]

Not enough information. Let's see the query and some notion of the fields in the table(s) that you are accessing.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[lespaul]

Is the name of the dealership the Primary Key of the table?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[mauricionava]

There is no primary key in the table.

dealer names are Suntrust and Southtrust
I want to pull all accounts from Suntrust and Southtrust if in the criteria I either enter one of the two dealer name.

 

[Golom]

A statement like

WHERE TheName LIKE '*trust*'         <-- DAO
or
WHERE TheName LIKE '%trust%'         <-- ADO

will catch both of them but we would need to see how you are supplying the name to search for to be more helpful.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[mauricionava]

ok, this is the query

SELECT LOCALLOTS.DEALER, TRHIST.LOT, TRDATA.STATUS, TRHIST.ACCT, TRHIST.RECEIPT, TRHIST.DATEPAID, TRHIST.CURDUE, TRHIST.AMTPAID, TRHIST.LATEFEE, TRHIST.PRINPAID, TRHIST.INTPAID, TRHIST.BALANCE, TRHIST.PAYTYPE, TRHIST.METHOD, TRHIST.MISCPAID, TRDATA.GP_AMT, TRDATA.GP_COST, TRDATA.TERM, TRDATA.PRINCIPAL, TRDATA.DOCFEES, TRDATA.RES_PCT, TRDATA.INTEREST, ([PRINCIPAL]-[GP_AMT]-[DOCFEES])/[PRINCIPAL] AS HBR, ([RES_PCT]/100)*[HBR]*[PRINPAID] AS Hpay, (1-[HBR])*[PRINPAID] AS GDoc
FROM ((TRHIST INNER JOIN TRDATA ON TRHIST.ACCT = TRDATA.ACCT) INNER JOIN LOCALLOTS ON TRHIST.LOT = LOCALLOTS.LOT) LEFT JOIN paytypelookup ON TRHIST.PAYTYPE = paytypelookup.Paytype
WHERE (((LOCALLOTS.DEALER) Like [Enter the dealer]) AND ((TRHIST.DATEPAID)>=[Enter the start date] And (TRHIST.DATEPAID)<=[Enter the end date]) AND ((paytypelookup.payHoldback)=True Or (paytypelookup.payHoldback) Is Null))
ORDER BY TRHIST.RECEIPT;

Should I change something in the table where it's getting the dealer name from so that I wont have to change all of my queries?

Thanks

 

[Golom]

Depends on what is being typed for the [Enter the dealer] prompt. You do not have any wild card characters around it so here are the results that you will get
[tt]
[Enter the dealer] Will Find
ABC - Dealers with the name ABC
*ABC - Dealers whose name ends in ABC
ABC* - Dealers whose name starts with ABC
*ABC* - Dealers whose name contains ABC
[/tt]
in your example, you could enter *trust* or *trust and both "SunTrust" and "SouthTrust" would be returned along with any others that either contained (first entry) or ended with (second entry) the word "trust".

NOTE: If you are using ADO instead of DAO then change "*" to "%" in the above examples and comments.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[mauricionava]

thank you, that helped but I would like to get the information using another method that it could be easier for my staff that's already used to do it the other way.

Is there a way to change something in the table that it links the 2 fields or something like that?

 

[Golom]

Sure. Build another table
[tt]
tblAlias
Dealer (PK)
Alias (PK)
[/tt]
Then

INSERT INTO tblAlias (Dealer, Alias)
Select DISTINCT Dealer A, Dealer B From LOCALLOTS

then

INSERT INTO tblAlias (Dealer, Alias)
VALUES ('SunTrust', 'SouthTrust')

and finally

SELECT L.DEALER, T.LOT, D.STATUS, T.ACCT, T.RECEIPT, T.DATEPAID, 
       T.CURDUE, T.AMTPAID, T.LATEFEE, T.PRINPAID, T.INTPAID, T.BALANCE, 
       T.PAYTYPE, T.METHOD, T.MISCPAID, D.GP_AMT, D.GP_COST, D.TERM, 
       D.PRINCIPAL, D.DOCFEES, D.RES_PCT, D.INTEREST, 
       ([PRINCIPAL]-[GP_AMT]-[DOCFEES])/[PRINCIPAL] AS HBR, 
       ([RES_PCT]/100)*[HBR]*[PRINPAID] AS Hpay, 
       (1-[HBR])*[PRINPAID] AS GDoc

FROM (((TRHIST T 
     INNER JOIN TRDATA       D ON T.ACCT = D.ACCT) 
     INNER JOIN LOCALLOTS    L ON T.LOT = L.LOT) 
     INNER JOIN tblAlias     A ON A.Dealer = L.Dealer)
     LEFT JOIN paytypelookup P ON T.PAYTYPE = P.Paytype

WHERE (((A.Alias) Like [Enter the dealer]) 
  AND ((T.DATEPAID) BETWEEN [Enter the start date] AND [Enter the end date]) 
  AND ((P.payHoldback)=True Or (P.payHoldback) Is Null))

ORDER BY T.RECEIPT;

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[mauricionava]

Awesome!

Thank you very much.