Checking date difference

[Gordon]

I am working on a customer information database (Access 2007). In the
before update event of the customer data entry form, I have some code
which checks if the custmer already exists (searching on surname and
postcode).

strSQL = "SELECT DISTINCTROW tblCompTicketIssues.fldSurname,
tblCompTicketIssues.fldPostCode, tblCompTicketIssues.fldContactID,
tblCompTicketIssues.fldInitials, tblCompTicketIssues.fldateAdded "

strSQL = strSQL & " FROM tblCompTicketIssues WHERE
(((tblCompTicketIssues.fldSurname) In (SELECT [fldSurname] FROM
[tblCompTicketIssues] "

strSQL = strSQL & " As Tmp GROUP BY [fldSurname],[fldPostCode] HAVING
Count(*)>1 And [fldPostCode] = [tblCompTicketIssues].
[fldPostCode])))"

strSQL = strSQL & "ORDER BY tblCompTicketIssues.fldSurname,
tblCompTicketIssues.fldPostCode;"

set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
With rst
' Loop through the records, creating a string of names and ID numbers
Do While Not .EOF
sOut = sOut & " " & !fldSurname & " " & !fldPostCode & " - Issue
ID # " & !fldContactID & vbCrLf
..MoveNext
lngDupes = lngDupes + 1
If lngDupes > conMaxDupes And Not .EOF Then
sOut = sOut & " and others." & vbCrLf
etc etc

This code works fine and throws up a warning message about possible
duplicates but I only want the warning to pop up if, as well as the
duplication of surname and postcode, the customer was added anytime
less than 2 years before today. The field name for that is
fldDateAdded. I can work this out by putting a text box on the form
(hidden or otherwise) witha control property of [txtTimescale] =
DateDiff("d",[fldDateAdded],Now()) and testing if[txtTimescale] <730,
but can someone suggest how I can integrate that logic with the code
above for checking if the customer is a duplicate?

Thanks

Gordon

 

[Ken Snell MVP]

Add the condition to the query:

strSQL = strSQL & " FROM tblCompTicketIssues WHERE
DateDiff("d",[fldDateAdded],Now()) < 730 AND
(((tblCompTicketIssues.fldSurname) In (SELECT [fldSurname] FROM
[tblCompTicketIssues] "

 

[Gordon]

Add the condition to the query:

strSQL = strSQL & " FROM tblCompTicketIssues WHERE
DateDiff("d",[fldDateAdded],Now()) < 730 AND
(((tblCompTicketIssues.fldSurname) In (SELECT [fldSurname] FROM
[tblCompTicketIssues] "

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

I am working on a customer information database (Access 2007).  In the
before update event of the customer data entry form, I have some code
which checks if the custmer already exists (searching on surname and
postcode).

strSQL = "SELECT DISTINCTROW tblCompTicketIssues.fldSurname,
tblCompTicketIssues.fldPostCode, tblCompTicketIssues.fldContactID,
tblCompTicketIssues.fldInitials, tblCompTicketIssues.fldateAdded "

strSQL = strSQL & " FROM tblCompTicketIssues WHERE
(((tblCompTicketIssues.fldSurname) In (SELECT [fldSurname] FROM
[tblCompTicketIssues] "

strSQL = strSQL & " As Tmp GROUP BY [fldSurname],[fldPostCode] HAVING
Count(*)>1  And [fldPostCode] = [tblCompTicketIssues].
[fldPostCode])))"

strSQL = strSQL & "ORDER BY tblCompTicketIssues.fldSurname,
tblCompTicketIssues.fldPostCode;"

set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
With rst
' Loop through the records, creating a string of names and ID numbers
 Do While Not .EOF
sOut = sOut & "    " & !fldSurname & " " & !fldPostCode & "  - Issue
ID # " & !fldContactID & vbCrLf
.MoveNext
lngDupes = lngDupes + 1
If lngDupes > conMaxDupes And Not .EOF Then
sOut = sOut & "    and others." & vbCrLf
etc  etc

This code works fine and throws up a warning message about possible
duplicates but I only want the warning to pop up if, as well as the
duplication of surname and postcode, the customer was added anytime
less than 2 years before today. The field name for that is
fldDateAdded.  I can work this out by putting a text box on the form
(hidden or otherwise) witha control property of [txtTimescale] =
DateDiff("d",[fldDateAdded],Now()) and testing if[txtTimescale] <730,
but can someone suggest how I can integrate that logic with the code
above for checking if the customer is a duplicate?

Thanks

Gordon- Hide quoted text -

- Show quoted text -

Thanks Ken, that worked fine after I enclosed the "d" in double
quotes.

Gordon

 

[Ken Snell MVP]

Thanks Ken, that worked fine after I enclosed the "d" in double
quotes.

Yes, sorry....typo from copy/paste on my part.