Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 21st, 2005, 08:33 AM
Authorized User
 
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default Messages within a certain timeframe

Hi Everyone.. I was wondering if you could help me with something.

In a database storing messages of chat conversations I would like to retrieve messages that were sent in the last hour, 10mins, and month. These could be seperate queries. Im not sure how to go about this. By looking into it a bit I thought that as the conversations are stored offline I would need to look at the last message sent and look at the time it was sent.. I do that using a query :

Select User, Message, Time From [Table] Where [Msg ID] in (Select Max([Msg ID]) as m_id From [Table]);

This will select the last record - so the last message sent. What I want to do is retrieve a list of messages sent in the last 10mins, last month and last hour - so I can have three separate queries to do this..

Would it be possible to use the DateDiff function in Access.. ive not used it before and not sure how it works .. if I get the Time from the first query above and say retrieve messages < month.. (i dont know the correct syntax and formats)

The format of the Time field is as follows : 12/02/2004 08:48:45

OR

Would it be best to use a function like below :

Public Sub timeDistribution(inLastM As Boolean, inLastH As Boolean, inLast10M As Boolean, testTime As Date)
' THIS METHOD TAKES FOUR PARAMETERES
' inLast10M IF MSG TIME IS IN LAST 10 MINUTES IT WILL SET THIS TRUE AND QUIT
' inLastH IF MSG TIME IS IN LAST HOUR IT WILL SET THIS TRUE AND QUIT
' inLastM IF MSG TIME IS IN LAST MONTH IT WILL SET THIS TRUE AND QUIT
    Dim minutes_now As Integer
    Dim minutes_msg As Integer
    minutes_now = ((Hour(Now) * 60) + Minute(Now))
    minutes_msg = ((Hour(testTime) * 60) + Minute(testTime))
    If (minutes_now - minutes_msg) <= 10 Then
        inLast10M = True
    ElseIf (minutes_now - minutes_msg) <= 60 Then
        inLastH = True
    ElseIf Month(testTime) = Month(Now) Then
        inLastM = True
    End If
End Sub

and put that as module and somehow pass the Time from the first query and then display the results in another query?

Please help.. your assistance will be greatly appreciated
 
Old March 21st, 2005, 08:44 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

You dont need to use a function for something like this. You CAN use the datediff function as an expression in the queries. e.g. DateDiff("n",fldTime,Now()). the First argument specifies the return period, in this case "n", represents minutes. The second argument is the time value you want to evaluate (your field name), and the last argument is the value to comapre against (we use Now() to get the current time value as per your system clock).

To do this, add a column to your query and type fldTimeMins:DateDiff("n",Time,Now())

Now run your query, You should end up with something like this. (Depending on the time you run the query of course!)

Time fldTimeMins
---------------------
10:40 120
11:40 60
12:00 40

You can then set the criteria to only get values where the fldTimeMins is <= the duration you specify.

On a sperate note, You should rename the field 'time' in your table aswell. Its not good to use reserved words as field names.

Jon
 
Old March 21st, 2005, 08:50 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Forgot to mention,

You can also use this for the remaining queries, Just set the Datediff period argument to the desired value ("h" = hours, "m" = Months)

Jon
 
Old March 21st, 2005, 01:17 PM
Authorized User
 
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Jon,

Thanks for that.. but I didnt understand how to 'add a column to your query and type fldTimeMins:DateDiff("n",Time,Now())' ?

also I dont want to calculate the time from the current system clock I basically want it to be one month, 10mins or an hour before the last time a message was sent.

so using the query :

SELECT User, Message, Time AS last_time
FROM [Table]
WHERE [Msg ID] in (Select Max([Msg ID]) as m_id From [Table]);

I get the last message sent and therefore the latest time.. from that I want to have a query saying that retrieve messages from Table where the time of the message is 10mins less than 'last_time'

is this possible?
 
Old March 23rd, 2005, 04:10 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

First Save the query that gets the latest message (qry_LastMessage). Build a new query in design view, Add the messages table and the query you just saved. Add fields from the message table (Message_ID,User,Message and MsgTime). In an empty column, add the expression TimeDiff: DateDiff("n",[Last_Time],[MsgTime])

Your SQL statement should look something like this -

SELECT tbl_Messages.Message_ID, tbl_Messages.Message, tbl_Messages.User, tbl_Messages.msgTime, DateDiff("n",[Last_Time],[MsgTime]) AS TimeDiff
FROM tbl_Messages, qry_LastMessage;


If you run the query, the TimeDiff field should contain a set of negative numbers. This represents the difference between the last_time (qry LastMessage) and MsgTime (the actual message time in tbl_Messages). Now you can add the criteria to your query. You should end up with something like this -

SELECT tbl_Messages.Message_ID, tbl_Messages.Message, tbl_Messages.User, tbl_Messages.msgTime, DateDiff("n",[Last_Time],[MsgTime]) AS TimeDiff
FROM tbl_Messages, qry_LastMessage
WHERE (((DateDiff("n",[Last_Time],[MsgTime]))>=-10));

This particular statement will return all messages from tbl_Messages where the difference in minutes between the message time and the Last_Time field in qry_LastMessage is Less than 10 minutes old. Save this query and copy it to handle your other cases, Just change the DateDiff Parameters to match your case. E.g. to Do the same for Months, change the Datadiff Period form "n" to "m". Adjust the crieria to >=-1. the SQL should lokk like this

SELECT tbl_Messages.Message_ID, tbl_Messages.Message, tbl_Messages.User, tbl_Messages.msgTime, DateDiff("m",[Last_Time],[MsgTime]) AS TimeDiff
FROM tbl_Messages, qry_LastMessage
WHERE (((DateDiff("m",[Last_Time],[MsgTime]))>=-1));

This will then return all messages less than one month old as per the time in the qry_LastMessage field.

I hope this clear it up for you. Let us know how you get on.

PS. Where your time field is called 'Time' mine is called msgTime.

Jon





Similar Threads
Thread Thread Starter Forum Replies Last Post
Private Messages ViagraFalls BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 10 October 22nd, 2007 03:48 PM
sends two messages instead of one?! shazza Biztalk 0 September 10th, 2007 03:19 PM
Messages RobCarter VB Databases Basics 1 September 13th, 2006 03:03 PM
error messages timbal25 Classic ASP Databases 0 August 1st, 2006 02:56 PM
Messages with ASP AmitG Classic ASP Basics 3 March 24th, 2004 12:54 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.