Compare Dates within the same table (1 Viewer)

msp · Oct 22, 2012

I have a table which I want to compare the time of two rows, and if the time is within 10 seconds return the two rows and ignore the other rows.

i.e. from the table below it would return rows 3 & 4..

Row Date Computer message
1 28/08/2012 22:11:23 Comp1 Problem resolved
2 28/08/2012 22:10:18 Comp1 Problem Started.
3 28/08/2012 21:35:46 Comp1 Problem resolved
4 28/08/2012 21:35:45 Com1 Problem Started.

I must admit it has been a few years since I played with access

plog · Oct 22, 2012

First, Date is a horrible name for a field--its a reserved word in access and can cause some issues. I suggest you use something more meaningful--possible add a prefix to 'Date'. For example, 'MessageDate', which is what I called it in the following code.

Now, for the solution. You didn't provide a name for your table, so I used 'YourTable'; you should replace all instances of that with the name of your actual table. Additionally, I used 'MessageDate' instead of 'Date'. Below is the SQL of the query you will need:

Code:

SELECT YourTable.*
FROM YourTable
WHERE (((validateMessageDate([MessageDate]))=True));

It seems simple, because the validation is so complex, I put it into a VBA function. So you need to paste this into a module:

Code:

Function validateMessageDate(MDate)
    ' determines if another message has occured within 10 seconds of the MDate
ret = False
NDate = DMax("[MessageDate]", "YourTable", "[MessageDate]<#" & [MDate] & "#")
If (IsNull(NDate) = False) Then
    ' determines if next highest MessageDate value in table is within 10 seconds
    If DateDiff("s", NDate, [MDate]) <= 10 Then ret = True
    End If
 
NDate = DMin("[MessageDate]", "YourTable", "[MessageDate]>#" & [MDate] & "#")
If (IsNull(NDate) = False) Then
    ' determines if next lowest MessageDate value in table is within 10 seconds
    If DateDiff("s", MDate, [NDate]) <= 10 Then ret = True
    End If
 
 
validateMessageDate = ret
 
End Function

Save both of those and run it and it should return the results you want.

msp · Oct 23, 2012

Hi,

Sorry still having issues with this, my table is called tbl_BaseData, when i try and run the code given i get the message, but i am getting the error,

Runtime Error @2465': Microsoft Access can't find the field "|" reffered in your exession

I admidt it is prob me doing something stupid,...

PHP:

Private Sub validateMessageDate_Click()

Dim SqL As String

SqL = "SELECT tbl_BaseData * FROM tbl_BaseData " & _
          "FROM tbl_BaseData WHERE " & _
          "(((validateMessageDate([MessageDate]))=True))"


' determines if another message has occured within 10 seconds of the MDate
ret = False
NDate = DMax("[MessageDate]", "[tbl_BaseData]", "[MessageDate]<#" & [MDate] & "#")
If (IsNull(NDate) = False) Then
    ' determines if next highest MessageDate value in table is within 10 seconds
    If DateDiff("s", NDate, [MDate]) <= 10 Then ret = True
    End If
 
NDate = DMin("[MessageDate]", "tbl_BaseData", "[MessageDate]>#" & [MDate] & "#")
If (IsNull(NDate) = False) Then
    ' determines if next lowest MessageDate value in table is within 10 seconds
    If DateDiff("s", MDate, [NDate]) <= 10 Then ret = True
    End If
 
 
 
validateMessageDate = ret


DoCmd.RunSQL SqL

End Sub

pr2-eugin · Oct 23, 2012

Your SQL statement is wrong.. there is two FROM keywords, a field called tbl_BaseData * whihc I think you meant, tbl_BaseData.*.. try replacing the SELECT statement as.,.

Code:

"SELECT tbl_BaseData.* FROM tbl_BaseData WHERE " & _
"(((validateMessageDate([MessageDate]))=True))"

RainLover · Oct 23, 2012

msp

It is a Function not a Sub.

Reread Paul's example code.

msp · Oct 23, 2012

Hi I have changed it to a function, it seems to be the # that are causing the issue..

pr2-eugin · Oct 23, 2012

msp.. the code plog gave you is different from what you have implemented.. You have literally copied the code into the same button click Sub.. the second code that plog has given you should go inside a module.. the first part of code in the button click event.. try to understand the code plog has given..

msp · Oct 23, 2012

I have changed it to a function (and updated the SQL- Thanks) still gives a error, seems to be the # that are causing the problem

pr2-eugin · Oct 23, 2012

Show us the code you have right now..

msp · Oct 23, 2012

pr2-eugin said:
Show us the code you have right now..

PHP:

[PHP]Function validateMessageDate(Mdate)
  

' determines if another message has occured within 10 seconds of the MDate
ret = False
NDate = DMax("[MessageDate]", "tbl_BaseData", "[MessageDate]>#" & [Mdate] & "#")

If (IsNull(NDate) = False) Then
    ' determines if next highest MessageDate value in table is within 10 seconds
    If DateDiff("s", NDate, [Mdate]) <= 10 Then ret = True
    End If
 
NDate = DMin("[MessageDate]", "tbl_BaseData", "[MessageDate]>#" & [Mdate] & "#")
If (IsNull(NDate) = False) Then
    ' determines if next lowest MessageDate value in table is within 10 seconds
    If DateDiff("s", Mdate, [NDate]) <= 10 Then ret = True
    End If
 
 
 
validateMessageDate = ret



 
End Function

[/PHP]

I then have a query that i call with the following SQl

SELECT *
FROM tbl_BaseData
WHERE (((validateMessageDate([MessageDate]))=True));

But getting error "Undefined Function "validateMessageDate" in expression

msp · Oct 23, 2012

Hi - Thanks all fo your help, managed to get it to work, I recreated the module and tried it again, results as expected

Compare Dates within the same table (1 Viewer)

msp

Registered User.

plog

Banishment Pending

msp

Registered User.

pr2-eugin

Super Moderator

RainLover

VIP From a land downunder

msp

Registered User.

pr2-eugin

Super Moderator

msp

Registered User.

pr2-eugin

Super Moderator

msp

Registered User.

msp

Registered User.

Similar threads

Users who are viewing this thread