[m_wylie85]

Hi i have a data base where i am selecting rows between two dates, but what i need to do is also get another another row if it has the same ID so:

If i put the date as: 01/02/2011 - 01/02/2011

and data in the database looks like so:

ID Name Date
123 John
123 John 01/02/2011

124 Peter
124 Peter 10/08/2012

i would like to be able to get both these rows

123 John
123 John 01/02/2011

But as you can see one of the row does not have a date in the row, so at the moment i am not getting that row just:

123 John 01/02/2011

so what i am asking is how could i get both:

how i am filtering the dates at the moment:

    Public Function mediumdate(ByVal Str As String) As String
        Dim aDay As Integer
        Dim aMonth As String
        Dim aYear As Integer
        Dim x As String = Format(Str, "dd/MM/yyyy")
        aDay = DateAndTime.Day(CDate(Str))
        aMonth = MonthName(DateAndTime.Month(CDate(Str)), True)
        aYear = DateAndTime.Year(CDate(Str))

        Return CStr(aDay & "-" & aMonth & "-" & aYear)

    End Function

    Function ShowTableBetweenSetDates() As DataSet
        Dim ds As New DataSet()
        Try
            Dim ActionCode As String = "ActionCode"

            If DBCon.State = ConnectionState.Closed Then
                DBCon.Open()
            End If
            Dim rs As New ADODB.Recordset()
            rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
            rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic

            'get the date i want from two date time pickers 'DTP1 and DPT2 

            rs.Open("SELECT * FROM " & working_Table & " WHERE DateActioned >= #" & _
            mediumdate(CStr(frmUpdatedMainMenu.DTP1.Value)) & "# AND DateActioned <= #" & _
            mediumdate(CStr(frmUpdatedMainMenu.DTP2.Value)) & "#", conString)
            
            ' close the connection
            DBCon.Close()
            Dim da As New System.Data.OleDb.OleDbDataAdapter()

            da.Fill(ds, rs, working_Table)

        Catch ex As Exception

        End Try
        Return ds
    End Function

[Macjohn]

From what I understood here is logical solution for you!

x--y--s
1--0--1
0--1--1
1--1--1
0--0--0


is this what you want? Is in the form of a logic table but I think is easy to understand!

[m_wylie85]

I Have no idea what your post is trying to teach me. Please explain what the above is meant to mean

[CharlieMay]

OK, I'm not very strong with these but something like:

SELECT test.[myID], test.[IDName], test.[FieldDate]
FROM test 
INNER JOIN (SELECT tst.MyID, tst.IDNAME, tst.FieldDate FROM Test as tst 
WHERE tst.FieldDate BETWEEN #01/01/12# AND #02/01/12# ) AS sub 
ON test.myID = sub.MyID

Of course you would need to change to your fields and tables.

Also, this is based on the same information being in one table. If it's two separate tables, then this query will not work.

This post has been edited by CharlieMay: 11 April 2012 - 06:50 AM

[Macjohn]

Also called Logic gates (for more on the subject...LINK HERE) is a way to see your problem.

In your query what you say is that you want to SELECT ALL the data THAT corresponds to your "requirements" with includes a date.

and in your "requirements" you say that you want all that have the date X, and is normal that if ¦123 John ---¦ doesn't have a date he(query) is not going to select it!

So my solution is.

First

WHERE xxxx OR DATA='' AND ID=ok

Did I made it clear enought?
(I don't think so but, today I'm having a hard time to explain(write)myself!)


[edit]As for query 2 different tables you may one read THIS.. it might help![/edit]

This post has been edited by Macjohn: 11 April 2012 - 06:58 AM

[m_wylie85]

I think i get what you mean Macjohn I wil try out both your suggestion and come back if i need help

Thanks

Michael