Multiple field search based on a qry "Rookie"

[pfunk]

I have two fields in the same table one called home phone and the other called poe phone. I want to be able to search both fields simultaneously meaning if not in home phone then look in poe. any help here here is what i currently have [enter home phone]

 

[BigRed1212]

What are all the relevant fields in the table and what do you want the query to return?

Given a phone, get back what?

 

[pfunk]

SELECT T_Collection_Records.Collector, T_Collection_Records.[Account #], T_Collection_Records.Client_ID, T_Collection_Records.Client, T_Collection_Records.[First Name], T_Collection_Records.Middle, T_Collection_Records.[Last Name], T_Collection_Records.[Address 1], T_Collection_Records.[Address 2], T_Collection_Records.City, T_Collection_Records.State, T_Collection_Records.Zip, T_Collection_Records.[Home Phone], T_Collection_Records.[POE Phone], T_Collection_Records.[SS#], T_Collection_Records.DOB, T_Collection_Records.Dbt2_LName, T_Collection_Records.Dbt2_FName, T_Collection_Records.Bal_Principal, T_Collection_Records.Last_Pmt_Amt, T_Collection_Records.Last_Pmt_Dt, T_Collection_Records.Orig_Loan_Amt, T_Collection_Records.Orig_Loan_Dt, T_Collection_Records.Last_Charge_Dt, T_Collection_Records.Occurance_Dt, T_Collection_Records.Debt_Type, T_Collection_Records.Service_Provided, T_Collection_Records.[CO Date], T_Collection_Records.Notes, T_Collection_Records.[Demand Letter], T_Collection_Records.[Demand_Ltr_ Date], T_Collection_Records.[Sif Letter], T_Collection_Records.Sif_Ltr_Date, T_Collection_Records.Settlement_Amount, T_Collection_Records.Adjusted_Bal_Principal, T_Collection_Records.Payment_Due_Date, T_Collection_Records.[Pif Letter], T_Collection_Records.[Status Codes], T_Collection_Records.Credit_Report_Request, T_Collection_Records.Credit_Report_Date, T_Collection_Records.Credit_Report_Data, T_Collection_Records.results, T_Collection_Records.match_code, T_Collection_Records.Gender, T_Collection_Records.Spouse, T_Collection_Records.Date_Reported, T_Collection_Records.Promise_Date, T_Collection_Records.Promise_Amount, T_Collection_Records.Payment_Due_Date, T_Collection_Records.[Letter Returned], T_Collection_Records.[Returned Date], T_Collection_Records.[Demand Letter2], T_Collection_Records.[Demand_Ltr_ Date2], T_Collection_Records.Adjusted_Bal_Principal2, T_Collection_Records.Monthly_Payment, T_Collection_Records.Due_Each_Mo, T_Collection_Records.Pif_Letter_Date, T_Collection_Records.Property_Owner, T_Collection_Records.Property_Reffered, T_Collection_Records.Property_Address, T_Collection_Records.Property_City, T_Collection_Records.Property_State, T_Collection_Records.Property_Zip, T_Collection_Records.Assesed_Value, T_Collection_Records.WellsFargo_Report_Dt, T_Collection_Records.Nxt_Work_Dt, T_Collection_Records.L_Work_Dt, T_Collection_Records.SOL, T_Collection_Records.Int_Rate, T_Collection_Records.County, T_Collection_Records.Region, T_Collection_Records.[Alt#], T_Collection_Records.[Alt_Rel_#1], T_Collection_Records.[Alt_Rel_#2], T_Collection_Records.[Alt_Rel_#3], T_Collection_Records.[Alt_Rel_#4], T_Collection_Records.portfolio_id, T_Collection_Records.[Placement level], T_Collection_Records.[Current Outsource Agency], T_Collection_Records.[WCS Agency1], T_Collection_Records.[WCS Agency2], T_Collection_Records.[Current Placement Date], T_Collection_Records.[Current Recall Date], T_Collection_Records.[WCS Agency1 P Date], T_Collection_Records.[WCS Agency1 R Date], T_Collection_Records.[WCS Agency2 P Date], T_Collection_Records.[WCS Agency2 R Date], T_Collection_Records.Nrby, T_Collection_Records.Rel, T_Collection_Records.LL, T_Collection_Records.[Rev Add], T_Collection_Records.[3 Add], T_Collection_Records.[Dbt2SS#], T_Collection_Records.Credit_Report_Request, T_Collection_Records.LL_Info, T_Collection_Records.POE_Info, T_Collection_Records.Attorney_Info
FROM T_Collection_Records
WHERE (((T_Collection_Records.[Home Phone])=[Enter Home Number]))
ORDER BY T_Collection_Records.Nxt_Work_Dt DESC , T_Collection_Records.L_Work_Dt;

 

[BigRed1212]

Ok. Perhaps something like

Select *
from t_collection_records
where [enter home phone] = [home phone] or [enter home phone] = [poe phone]
order by Nxt_Work_Dt DESC, L_Work_Dt;

Note this could possibly return multiple records if we both had the same poe phone (both worked at the same place for example). poe is "place of employment" I am guessing.

Where is "enter home phone" coming from?

 

[pfunk]

Big Red,

worked great but any way to incorporate a wild card search meaning i have database users who put garble notes next to the phone numbers and diffrent formats for phone numbers example 888-888-8888 or (888)-888-8888 or 8888888888 good number. before i have to clean the data for a couple hours for screwed up chariacters and text and have to limit the field sise to 10 digits and treat these users like morons. yeah know what i mean. a wild card seach would be ideal. like *888*888*8888*

 

[BigRed1212]

Where is "enter home phone" coming from? Direct user entry on a Web page?

What format are the numbers in the database in currently? Are they all in the same format?

 

[pfunk]

the answer to you first question is its a Direct user entry. and not not all the same formats if i search in a filer on the form like *888*888*8888 it works but when the user [enter home phone] they get the ones without the characters formated like 8888888888. which is the fomat i really want but came on this project after the damage was done .just trying to stay away from cleaning the data and formating the field to be ten digits.

 

[BigRed1212]

I think you are saying that 1) your users are entering things directly, implying that this is some sort of a real time phone lookup; and 2) that the phone numbers in the database are not all in the same format and you are trying to avoid having to go in and clean them. I'm not sure I followed you on the rest of it.

I hope others will weigh in. There are probably some cool matching search patterns or neat tricks I don't know that will make the next stuff I say irrelevant, but failing that; I would think you need to do a few things.

First, if you are not already doing it nothing goes into the database from now on that isn't in the format you want. Set up to screen, validate, and check the format of any data being added 6 ways to Sunday.

Second, clean the data that is already there. Export it to something and bring it back in if you must, but I think a database with (888)555-1212 and 888-555-1212 and 888.555.1212 in it all at the same time is a problem that needs fixing. If you have good data, you can exact match and you won't need to rely on pattern matching.

Third (this part is more opinion and taste) I think people really do handle phone numbers better in the 3/3/4 break up rather than as a 10 digit number. When I have to enter a phone number on a Web form I always like the forms that have 3 seperate boxes. 3 numbers in the area code box, automatically move to 3 in the next box, and then to 4 in the last. No confusion over whether to add parenthesis or hyphens or anything. Those can even be on the screen already. The boxes accept nothing but numbers and get manipulated and put together or whatever back side.

I don't think I fixed your problem or answered your question, but I'm not sure what you want or where you are going.