Dear All,

I am presently working on a program where a database table needs to be accessed from VB. The table (tblMaintenance) is as below:

ID Site Date1 Date2 Date3 Date4
1 Forest 05/04/2012 23/05/2012 15/06/2012 15/07/2012
2 Orleans 06/03/2012 05/04/2012 18/05/2012 13/06/2012
3 Washington 04/04/2012 18/05/2012 21/06/2012 02/07/2012
4 California 03/04/2012 19/05/2012 11/06/2012 27/07/2012

I would like a textbox to return all sites where a particular date is found. As example, a search for '05/04/2012' should return "Forest" and "Orleans" since they are found in fields 'Date1' and 'Date2'.

I know how to search for a specific record in one field. For this my SQL string is,

Code:
sql = "SELECT Site FROM tblMaintenance WHERE (Format([Date1], 'Short Date')) = '05/04/2012'"
and then to retrieve the Site name is OK.

Now, if I want to look for this date in fields 'Date1' and 'Date2', my SQL string is,

Code:
sql = "SELECT Site FROM tblMaintenance WHERE (Format([Date1], 'Short Date')) = '05/04/2012' OR (Format([Date2], 'Short Date')) = '05/04/2012'"
which works perfectly. But if I need to search in all fields i.e Date1,Date2,Date3 etc, I can continue adding 'OR' which you will agree does not make the code very neat.

I am sure there must be a simpler way to do this but can't figure it out.

Please help.