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

Use the DateAdd function...


Good Luck

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>

as an example.


Good Luck