[timotz]

(this code is in Attendess Form)
Public eventfinished As Boolean
Public currenttime As Date

Private Sub AttendBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AttendBtn.Click
currenttime = Format(Now(), "Medium Time")
            sqlCmd = String.Format("SELECT * FROM Events WHERE ETitle = '{0}' and EEndTime <= #{1}#", MTWListBx.SelectedItem.ToString(), currenttime)
            FormatQueries.CheckIfEventFinished(sqlCmd)
            If (eventfinished = True) Then
                MsgBox("Event is Finished Already. You cannot Login") 
            Else
                MsgBox("Event Not Yet Finished. You can now Login")
            End If
 End Sub


(FormatQueries Module)
Function CheckIfEventFinished(ByRef sqlCmd As String)
        con.ConnectionString = dbProvider + dbSource + dbPword
        con.Open()
        Attendees.eventfinished = False
        Try
            cmd = New OleDbCommand(sqlCmd, con)
            SQLdr = cmd.ExecuteReader()
            While (SQLdr.Read() And Attendees.eventfinished = False)
                MsgBox(SQLdr("EEndTime") + "EndTime " + Attendees.currenttime + " CurrentTime")
                If (Attendees.currenttime <= SQLdr("EEndTime")) Then
                    Attendees.eventfinished = True
                End If
            End While
        Catch ex As OleDbException
            MsgBox("Error: " & ex.ToString & vbCrLf)
        Finally
        End Try
        con.Close()
        Return 0
    End Function

so this is my code and my goal is to check whether or not a certain is event is already over when the time the user logs in or clicks the attend button. in the table i have, the fields are in date/time data type with no kind of formatting. the problem is whether the time is earlier or not the result always goes a certain result(the msgbox's) regardless of the endtime being earlier than or sooner than the system time.

example if the end time of the event is 10:00:00AM and the system time is lets say 9:27:00 AM
MsgBox("Event is Finished Already. You cannot Login") will be displayed (which is wrong)

and if i change the time in the table to something before the endtime it will still show the same msgbox
MsgBox("Event is Finished Already. You cannot Login") (which is correct but i think code-wise is wrong)


what i would love some help on is the correct time comparison syntax for my code because i think that is where the problem lies because i do not know time manipulation and comparison that well in vb. and whether or not the formatting of the fields and variables should be considered(if yes then please do tell how). i am using visual studio 2010 and ms access 2010 on win xp.

"and if i change the time in the table to something before the endtime it will still show the same msgbox
MsgBox("Event is Finished Already. You cannot Login") (which is correct but i think code-wise is wrong)"

i meant AFTER not BEFORE sry

[DimitriV]

This page has an explanation as to why you may receive unexpected results when comparing DateTime:
http://support.microsoft.com/kb/210276

[timotz]

DimitriV thanks a bunch! i will look into it now.

[timotz]

DimitriV thank you so much! i used this from the page you gave me:
Use the DateDiff() function to compare precise units such as seconds:
var1 = #2:01:00 PM#
var2 = DateAdd("n", 10, var1)
? DateDiff("s", var2, #2:11:00 PM#) = 0

i used the DateDiff() method and i just made some conditions where the certain negative and positive results will be the times that are over and not yet over respectively.

thank you!

[DimitriV]

Excellent. Ich bin sehr glücklich that it worked out for you
Ps: it means I am very happy