find records with same date in 2 columns

[piper3d]

I have dates in 2 columns.

I would like to have the user input the date they are searching for. like [Enter Date]. Then using the date that was entered, show all of the records that matched the date they entered from both the first date column and the second date column. Also, I would not like to show duplicates. If the same record has the same date in both date columns, only show the record one time.

Thank You In Advance,
Brian

 

[cmmrfrds]

select * from yourtable
where
column1date = enterdate OR
(column2date = enterdate and column1date <> enterdate)

 

[JeremyNYC]

Actually, I think that solution will show you cases where either one of the fields match, but not both, and I don't think this is what you're after.

This will do what you want:
SELECT tblTest.OneID, tblTest.dateField1, tblTest.DateField2
FROM tblTest
WHERE (((tblTest.dateField1)=[enter the date]) AND ((tblTest.DateField2)=[tbltest].[datefield1]));

You don't have to worry about duplicate rows. It's checking two fields in the same row, so it will only show the row one time.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[piper3d]

I want either the first field or the second field to match what the user enters, there may be a chance that both fields do match, but that doesn't matter if it looks at each field independantly, right?

I don't know much about sql. I did do view sql mode to show you what I have now.

Where will your code fit into this?

SELECT DISTINCTROW SCHEDULE.friprom, SCHEDULE.CO_NUMBER, SCHEDULE.CUST_NAME, SCHEDULE.SHIP_TO_NM, SCHEDULE.CUST_ITEM, SCHEDULE.SumOfcount, SCHEDULE.SumOfweight, SCHEDULE.SumOfschfac, SCHEDULE.PROM_DLVRY, SCHEDULE.LastOfshops, SCHEDULE.LastOfshopsout, SCHEDULE.LastOfready, SCHEDULE.LastOfreleased, GetNextDayDate(6,[LastOfreqd]) AS REQ_FRI, SCHEDULE.LastOfreqd, SCHEDULE.SumOfFRCO, SCHEDULE.NOTE, SCHEDULE.SUPPLYDATE, SCHEDULE.LastOfLN_STA, SCHEDULE.LastOfITEM_REF2, ACKSHEAD.SALES_CAT2, SCHEDULE.LastOfpor, PLineClass.Description, SCHEDULE.LastOfUNIT_PRICE, SCHEDULE.TEXT_LINE1, ACKSHEAD.CO_AMOUNT, TRACKLN.COMP_WC, TRACKLN.QUANTITY
FROM TRACKLN RIGHT JOIN ((SCHEDULE LEFT JOIN ACKSHEAD ON SCHEDULE.CO_NUMBER = ACKSHEAD.CO_NUMBER) LEFT JOIN PLineClass ON SCHEDULE.LastOfITEM_REF2 = PLineClass.ProdLine) ON TRACKLN.CO_NUMBER = SCHEDULE.CO_NUMBER
WHERE (((TRACKLN.COMP_WC)=&quot;ORDER CONFIG&quot AND ((TRACKLN.QUANTITY)<>0));

Thank You In Advance,
Brian