Searching database in Access from VB2008.

[salim100569]

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,

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,

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.

[Niya]

Well I can think of ways to enumerate the fields in the table but its not worth the effort when you could simply type in the fields in your SQL string, inelegant as that may seem. The cost to benefit ratio in this particular case doesn't make enumeration a viable option. It does if you have something like 20 date fields, but I'm assuming you have like 3 to 5 date fields so I'd suggest you just type them out.

[kareninstructor]

The following shows one idea where we work off an array of field names. I used Now.ToShortDate but of course you would replace that with your date to search

Private Sub Button1_Click( _ 
    ByVal sender As System.Object, _ 
    ByVal e As System.EventArgs) Handles Button1.Click

    Dim Dates As String() = {"Date1", "Date2", "Date3"}
    Dim Result As String = "SELECT Site FROM tblMaintenance WHERE " & _ 
        CreateCondition(Dates, Now.ToShortDateString)
    Console.WriteLine(Result)

    Dates = {"Date1", "Date2"}
    Result = "SELECT Site FROM tblMaintenance WHERE " & _ 
        CreateCondition(Dates, Now.ToShortDateString)
    Console.WriteLine(Result)

    Dates = {"Date4"}
    Result = "SELECT Site FROM tblMaintenance WHERE " & _ 
        CreateCondition(Dates, Now.ToShortDateString)
    Console.WriteLine(Result)

End Sub
Public Function CreateCondition(ByVal Fields As String(), ByVal TheDate As String) As String
    Dim Items(Fields.Count - 1) As String
    Dim Pattern As String = "Format([{0}], 'Short Date') = '{1}'"

    For x As Integer = 0 To Items.Count - 1
        Items(x) = String.Format(Pattern, Fields(x), TheDate)
    Next
    Return String.Join(" or ", Items)
End Function

[Baselx]

Thank you