Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 21st, 2005, 08:33 AM
Authorized User
 
Join Date: Feb 2005
Location: , , .
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
Reply With Quote
  #2 (permalink)  
Old March 21st, 2005, 08:44 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: , , United Kingdom.
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
Reply With Quote
  #3 (permalink)  
Old March 21st, 2005, 08:50 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: , , United Kingdom.
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
Reply With Quote
  #4 (permalink)  
Old March 21st, 2005, 01:17 PM
Authorized User
 
Join Date: Feb 2005
Location: , , .
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?
Reply With Quote
  #5 (permalink)  
Old March 23rd, 2005, 04:10 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: , , United Kingdom.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:14 AM.


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