FindFirst Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to loop through a discharge table and search in an appointment
table for appointments occurring within 7 days of a discharge. Both tables
are linked to another Access 2000 database.
I have opened a DAO recordset from each table then I am looping through the
discharge recordset and using findfirst, as below, looking in the appointment
recordset for records with the same PTID and the appointment match
I have opened both recordsets as dbOpenDynaset and I have confirmed that I
am using the correct version Jet as per Knowledge base yet the nomatch
property is always false even if no appointment for discharge is in the
appointment recordset

strFindCriteria = "[PTID] = '" & rsDisc!PTID & "' AND ([Date] BETWEEN " &
MakeUSDate(rsDisc!DischargeDate) & " AND " & MakeUSDate(DateAdd("d", 7,
rsDisc!dischargeDate)) & ") "
rsAppt.FindFirst (strFindCriteria)

If anyone can help I would be very grateful
 
Try delimiting your date values:

strFindCriteria = "[PTID] = '" & rsDisc!PTID & "' AND ([Date] BETWEEN #" &
MakeUSDate(rsDisc!DischargeDate) & "# AND #" & MakeUSDate(DateAdd("d", 7,
rsDisc!dischargeDate)) & "#) "

Not really why your version always finds a match, but then I also don't know
exactly what MakeUSDate() does.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
MakeusDate converts my british format date to us format.

I don't know what you mean by delimiting date values

Roger Carlson said:
Try delimiting your date values:

strFindCriteria = "[PTID] = '" & rsDisc!PTID & "' AND ([Date] BETWEEN #" &
MakeUSDate(rsDisc!DischargeDate) & "# AND #" & MakeUSDate(DateAdd("d", 7,
rsDisc!dischargeDate)) & "#) "

Not really why your version always finds a match, but then I also don't know
exactly what MakeUSDate() does.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Tatt said:
I am trying to loop through a discharge table and search in an appointment
table for appointments occurring within 7 days of a discharge. Both tables
are linked to another Access 2000 database.
I have opened a DAO recordset from each table then I am looping through the
discharge recordset and using findfirst, as below, looking in the appointment
recordset for records with the same PTID and the appointment match
I have opened both recordsets as dbOpenDynaset and I have confirmed that I
am using the correct version Jet as per Knowledge base yet the nomatch
property is always false even if no appointment for discharge is in the
appointment recordset

strFindCriteria = "[PTID] = '" & rsDisc!PTID & "' AND ([Date] BETWEEN " &
MakeUSDate(rsDisc!DischargeDate) & " AND " & MakeUSDate(DateAdd("d", 7,
rsDisc!dischargeDate)) & ") "
rsAppt.FindFirst (strFindCriteria)

If anyone can help I would be very grateful
Click to expand...
Click to expand...
 
MakeusDate converts my british format dates to US format.
My version doesn't always find a match the no match property is always false.

I don't know what you mean by delimiting the date values

Roger Carlson said:
Try delimiting your date values:

strFindCriteria = "[PTID] = '" & rsDisc!PTID & "' AND ([Date] BETWEEN #" &
MakeUSDate(rsDisc!DischargeDate) & "# AND #" & MakeUSDate(DateAdd("d", 7,
rsDisc!dischargeDate)) & "#) "

Not really why your version always finds a match, but then I also don't know
exactly what MakeUSDate() does.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Tatt said:
I am trying to loop through a discharge table and search in an appointment
table for appointments occurring within 7 days of a discharge. Both tables
are linked to another Access 2000 database.
I have opened a DAO recordset from each table then I am looping through the
discharge recordset and using findfirst, as below, looking in the appointment
recordset for records with the same PTID and the appointment match
I have opened both recordsets as dbOpenDynaset and I have confirmed that I
am using the correct version Jet as per Knowledge base yet the nomatch
property is always false even if no appointment for discharge is in the
appointment recordset

strFindCriteria = "[PTID] = '" & rsDisc!PTID & "' AND ([Date] BETWEEN " &
MakeUSDate(rsDisc!DischargeDate) & " AND " & MakeUSDate(DateAdd("d", 7,
rsDisc!dischargeDate)) & ") "
rsAppt.FindFirst (strFindCriteria)

If anyone can help I would be very grateful
Click to expand...
Click to expand...
 
I am sorry the Makeusdate function delimits the dates

Roger Carlson said:
Try delimiting your date values:

strFindCriteria = "[PTID] = '" & rsDisc!PTID & "' AND ([Date] BETWEEN #" &
MakeUSDate(rsDisc!DischargeDate) & "# AND #" & MakeUSDate(DateAdd("d", 7,
rsDisc!dischargeDate)) & "#) "

Not really why your version always finds a match, but then I also don't know
exactly what MakeUSDate() does.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Tatt said:
I am trying to loop through a discharge table and search in an appointment
table for appointments occurring within 7 days of a discharge. Both tables
are linked to another Access 2000 database.
I have opened a DAO recordset from each table then I am looping through the
discharge recordset and using findfirst, as below, looking in the appointment
recordset for records with the same PTID and the appointment match
I have opened both recordsets as dbOpenDynaset and I have confirmed that I
am using the correct version Jet as per Knowledge base yet the nomatch
property is always false even if no appointment for discharge is in the
appointment recordset

strFindCriteria = "[PTID] = '" & rsDisc!PTID & "' AND ([Date] BETWEEN " &
MakeUSDate(rsDisc!DischargeDate) & " AND " & MakeUSDate(DateAdd("d", 7,
rsDisc!dischargeDate)) & ") "
rsAppt.FindFirst (strFindCriteria)

If anyone can help I would be very grateful
Click to expand...
Click to expand...
 
Back
Top