Last 24 Hours

[samuelmoneill]

Hi,

I have written an application in VB6 to take in SMS messages from a SIM card and store them as Alarms in a MYSQL database. Each Alarm is stored in the database with the date and time of the alarm.

I need to add a function to the application to only display the alarms that came in in the Last 24 Hours. Does anyone know how I might achieve this?

I was trying something along the lines of using the VB function Time() and subtractung the time of the alarm from it?

Any suggestions would be grately appreciated.

Sam

[vb5prgrmr]

Use the DateAdd function...

?now
7/10/2009 9:26:48 AM 
?dateadd("d",-1,now)
7/9/2009 9:27:13 AM

Good Luck

[samuelmoneill]

Thanks vb5prgrmr ,

but that will only get me the date and time of 24 hours. How do I then use that result to only display records/alarms from a mysql database that have occurred in the last 24 hours?
Ideally i need to end up with something of the form:
<code>
Select * from live_alarms Where Date_stamp <= 24hours
</code>

Here is what I have tried:

<code>
Private Sub ddFilter_defined_Click()
Dim cur_date_date As String
Dim cur_date As String
Dim cur_time As String
Dim cur_hour As Integer
Dim cur_min As Integer
Dim db_hour As Integer
Dim db_min As Integer
Dim day_diff As Integer
Dim cur_day As Integer
Dim prev_day As Integer
Dim prev_date As String
Dim prev_hour As String
Dim prev_date_date As String

If ddFilter_defined.Text = "Last 24 Hours" Then
'MySQLQuery ("Select * from live_alarms Order By Time_stamp")
' If Not rs.EOF Then
' rs.MoveLast
' End If
' Do Until rs.BOF
' filter_id = rs.Fields("ID").Value
' filter_client = rs.Fields("Site").Value
' filter_alarm = rs.Fields("Alarm").Value
' filer_date = rs.Fields("Date_stamp").Value
' filter_time = rs.Fields("Time_stamp").Value
' filter_time = Mid$(filter_time, InStr(1, filter_time, " "), 6)
'
' Loop
cur_date_date = DateValue(Now)
cur_time = Time()
cur_hour = Mid$(cur_time, 1, 2)
cur_date = Now()
cur_day = Mid$(cur_date_date, 1, 2)
prev_date = DateAdd("h", -24, Now)
Debug.Print "Previous Date: "; prev_date
prev_date_date = Mid$(prev_date, 1, 10)
Debug.Print "Previous Date 1: "; prev_date_date
prev_day = Mid$(prev_date, 1, 2)
Debug.Print "Previous Day: "; prev_day
cur_date_date = DateValue(Now)
Debug.Print "Now Date: "; cur_date_date
ste.Clear

If cur_day - prev_day = 1 Then
prev_date_date = Format$(prev_date, "YYYY/MM/DD")
Debug.Print "Prev_date Formatted: "; prev_date
MySQLQuery ("Select * from live_alarms Where Date_stamp = '" & prev_date_date & "'")
If Not rs.EOF Then
rs.MoveLast
End If
Do Until rs.BOF
prev_hour = Mid(DateAdd("h", -24, Now), 12, 2)
db_hour = Mid$(rs.Fields("Time_stamp").Value, 1, 2)
If db_hour = "00" Then
db_hour = "24"
If db_hour >= prev_hour And db_hour < 24 Then
filter_id = rs.Fields("ID").Value
filter_client = rs.Fields("Site").Value
filter_alarm = rs.Fields("Alarm").Value
filter_date = rs.Fields("Date_stamp").Value
filter_time = rs.Fields("Time_stamp").Value
filter_time = Mid$(filter_time, InStr(1, filter_time, " "), 6)
short_message = filter_id & " " & filter_time & " " & filter_date & " " & filter_client & " " & filter_alarm
ste_item_index = rs.Fields("ID").Value
ste.AddItem short_message
ste.ItemData(ste.NewIndex) = ste_item_index
rs.MovePrevious
End If
End If
Loop
rs.Close
cur_date_date = DateValue(Now)
cur_date_date = Format$(cur_date_date, "YYYY/MM/DD")
MySQLQuery ("Select * from live_alarms Where Date_stamp = '" & cur_date_date & "'")
If Not rs.EOF Then
rs.MoveLast
End If
Do Until rs.BOF
filter_id = rs.Fields("ID").Value
filter_client = rs.Fields("Site").Value
filter_alarm = rs.Fields("Alarm").Value
filter_date = rs.Fields("Date_stamp").Value
filter_time = rs.Fields("Time_stamp").Value
filter_time = Mid$(filter_time, InStr(1, filter_time, " "), 6)
short_message = filter_id & " " & filter_time & " " & filter_date & " " & filter_client & " " & filter_alarm
ste_item_index = rs.Fields("ID").Value
ste.AddItem short_message
ste.ItemData(ste.NewIndex) = ste_item_index
rs.MovePrevious
Loop
rs.Close
End If

cur_hour = Mid$(cur_time, 1, 2)
Debug.Print "Current Time: "; cur_time
Debug.Print "Current Day: "; cur_day
Debug.Print "Last 24 Hours: "; prev_date
End If
End Sub

Appologies for the messy code
</code>

[vb5prgrmr]

strSQL = "SELECT * FROM tblName WHERE dDateField >= " & DateAdd("d", -1, Now)

as an example.

Good Luck