Access 2010. I am finding it difficult to find records using
FindFirst.

In the code below I open the same file in two recordsets. The field
CreateDate is a date field in the Now() format. The SenderAddy field
is text.

If I run the routine below using #1 or #4 all records are missing. If
I run #2 or #3, it finds them all. If I run #5 then 1594 are found/
996 are missing. If I run #6 then 2516 are found/90 are missing.

I then created a new field in the table called NewDate. I ran an
update query with the following criteria and updated the field NewDate
with:
DateSerial(Year([CreateDate]),Month([CreateDate]),Day([CreateDate]))
+TimeSerial(Hour([CreateDate]),Minute([CreateDate]),Second([CreateDate]))

I then ran a comparison between NewDate and CreateDate in a query.
If the two were the same I displayed the word "Same", if different
"Diff". They were all "Diff" even tho the two appeared to look the
same in the screen!

If I modifed the routine to look at NewDate all FindFirst's but #6
found all of the records. I'm not sure why converting a date still
produced a mismatch.

Public Sub FindTheRec()
Set D = CurrentDb
Set rst = D.OpenRecordset("Duplicates", dbOpenSnapshot)
Set r = D.OpenRecordset("Duplicates", dbOpenSnapshot)

Do While Not rst.EOF
intAll = intAll + 1

strF = "Cdbl(CreateDate) = " & CDbl(rst!CreateDate) & " And
SenderAddy = """ & rst!SenderAddy & """"

'I removed the comment character in my respective FindFirst
tests
'r.FindFirst "CreateDate = #" & rst!CreateDate & "#" '#1
'r.FindFirst "CreateDate >= #" & rst!CreateDate & "#" '#2
'r.FindFirst "SenderAddy = """ & rst!SenderAddy & """" '#3
'r.FindFirst "CreateDate = #" & rst!CreateDate & "# And
SenderAddy = """ & rst!SenderAddy & """" '#4
'r.FindFirst "CreateDate >= #" & rst!CreateDate & "# And
SenderAddy = """ & rst!SenderAddy & """" '#5
'r.FindFirst strF '#6

If Not r.NoMatch Then
intFound = intFound + 1
Else
intMissing = intMissing + 1
End If

rst.MoveNext
Loop
MsgBox "All " & intAll & vbNewLine & "Found " & intFound1 &
vbNewLine & "Missing " & intMissing, , "Info"
End Sub

My data came from opening up Outlook and reading emails. For some
reason I can't filter Outlook mail based on [date] [hhmmss] (Now()
format). It appears Outlook doesn't like seconds when filtering.
Since emails can arrive in the same minute but different seconds, I
subtracted a minute from the "From" date range just in case this
occurred so I wouldn't miss an email in my date filter range. Then as
I read the emails I used the following code to update the Access
table.
datDat = olRow("CreationTime")
rst!CreateDate = datDat

It is odd that the date that is stored from Outlook can't be used in
my above routine. Does anybody have a suggestion on 1) filtering
Outlook emails in a date range using seconds in the filter and 2) why
the Access table can't find its own record?

What is your Short Date format set to in Regional Settings? Realistically,
Access does not respect regional date setting in SQL statements (which is
what Filter statement actually is), so if it's set to dd/mm/yyyy, that's
your problem.

#6 will work because you're converting the date to its numeric value (where
the integral part represents the date as a number of days relative to 30
Dec, 1899, and the decimal part represents the time as a fraction of a day)

Change your first 5 filters to

'r.FindFirst "CreateDate = " & Format(rst!CreateDate,
"\#yyyy\-mm\-dd hh\:nn\:ss\#") '#1
'r.FindFirst "CreateDate >= " & Format(rst!CreateDate,
"\#yyyy\-mm\-dd hh\:nn\:ss\#") '#2
'r.FindFirst "SenderAddy = """ & rst!SenderAddy & """" '#3
'r.FindFirst "CreateDate = " & Format(rst!CreateDate,
"\#yyyy\-mm\-dd hh\:nn\:ss\#") & " And
SenderAddy = """ & rst!SenderAddy & """" '#4
'r.FindFirst "CreateDate >= " & Format(rst!CreateDate,
"\#yyyy\-mm\-dd hh\:nn\:ss\#") & " And
SenderAddy = """ & rst!SenderAddy & """" '#5



"Patrick Finucane" wrote in message
news:34ba1142-8d59-4fa8-a237-6b7cfd87b5e1 (AT) f36g2000vbm (DOT) googlegroups.com...

Access 2010. I am finding it difficult to find records using
FindFirst.

In the code below I open the same file in two recordsets. The field
CreateDate is a date field in the Now() format. The SenderAddy field
is text.

If I run the routine below using #1 or #4 all records are missing. If
I run #2 or #3, it finds them all. If I run #5 then 1594 are found/
996 are missing. If I run #6 then 2516 are found/90 are missing.

I then created a new field in the table called NewDate. I ran an
update query with the following criteria and updated the field NewDate
with:
DateSerial(Year([CreateDate]),Month([CreateDate]),Day([CreateDate]))
+TimeSerial(Hour([CreateDate]),Minute([CreateDate]),Second([CreateDate]))

I then ran a comparison between NewDate and CreateDate in a query.
If the two were the same I displayed the word "Same", if different
"Diff". They were all "Diff" even tho the two appeared to look the
same in the screen!

If I modifed the routine to look at NewDate all FindFirst's but #6
found all of the records. I'm not sure why converting a date still
produced a mismatch.

Public Sub FindTheRec()
Set D = CurrentDb
Set rst = D.OpenRecordset("Duplicates", dbOpenSnapshot)
Set r = D.OpenRecordset("Duplicates", dbOpenSnapshot)

Do While Not rst.EOF
intAll = intAll + 1

strF = "Cdbl(CreateDate) = " & CDbl(rst!CreateDate) & " And
SenderAddy = """ & rst!SenderAddy & """"

'I removed the comment character in my respective FindFirst
tests
'r.FindFirst "CreateDate = #" & rst!CreateDate & "#" '#1
'r.FindFirst "CreateDate >= #" & rst!CreateDate & "#" '#2
'r.FindFirst "SenderAddy = """ & rst!SenderAddy & """" '#3
'r.FindFirst "CreateDate = #" & rst!CreateDate & "# And
SenderAddy = """ & rst!SenderAddy & """" '#4
'r.FindFirst "CreateDate >= #" & rst!CreateDate & "# And
SenderAddy = """ & rst!SenderAddy & """" '#5
'r.FindFirst strF '#6

If Not r.NoMatch Then
intFound = intFound + 1
Else
intMissing = intMissing + 1
End If

rst.MoveNext
Loop
MsgBox "All " & intAll & vbNewLine & "Found " & intFound1 &
vbNewLine & "Missing " & intMissing, , "Info"
End Sub

My data came from opening up Outlook and reading emails. For some
reason I can't filter Outlook mail based on [date] [hhmmss] (Now()
format). It appears Outlook doesn't like seconds when filtering.
Since emails can arrive in the same minute but different seconds, I
subtracted a minute from the "From" date range just in case this
occurred so I wouldn't miss an email in my date filter range. Then as
I read the emails I used the following code to update the Access
table.
datDat = olRow("CreationTime")
rst!CreateDate = datDat

It is odd that the date that is stored from Outlook can't be used in
my above routine. Does anybody have a suggestion on 1) filtering
Outlook emails in a date range using seconds in the filter and 2) why
the Access table can't find its own record?

On 31/10/2011 00:41:11, "Douglas J Steele" wrote:
Dates always are a pain in the arse in Access
I have been playing around with this, and it seems to provide the correct
answers in the UK

Function DDiff(Interval As String, FirstDate As String, SecondDate As String)
' More relianle than DateDiff, seems to interpret dates correctly
Dim Diff As Long

Diff = DateDiff(Interval, DateSerial(DatePart("yyyy", SecondDate), _
DatePart("m", SecondDate), DatePart("d", SecondDate)), _
DateSerial(DatePart("yyyy", FirstDate), _
DatePart("m", FirstDate), DatePart("d", FirstDate)))

DDiff = Diff

End Function

Note that the 2 dates to be compared are strings, not dates, so you need a
CStr() of the 2 dates first

Phil

Check what Allen Browne has at http://www.allenbrowne.com/ser-36.html to
take the mystery out of what's happening.

"Phil" wrote in message news:j8ljvd$3r9$1 (AT) speranza (DOT) aioe.org...

On 31/10/2011 00:41:11, "Douglas J Steele" wrote:
Dates always are a pain in the arse in Access
I have been playing around with this, and it seems to provide the correct
answers in the UK

Function DDiff(Interval As String, FirstDate As String, SecondDate As
String)
' More relianle than DateDiff, seems to interpret dates correctly
Dim Diff As Long

Diff = DateDiff(Interval, DateSerial(DatePart("yyyy", SecondDate), _
DatePart("m", SecondDate), DatePart("d", SecondDate)), _
DateSerial(DatePart("yyyy", FirstDate), _
DatePart("m", FirstDate), DatePart("d", FirstDate)))

DDiff = Diff

End Function

Note that the 2 dates to be compared are strings, not dates, so you need a
CStr() of the 2 dates first

Phil