X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Look Up Record In A Recordset, Any Version    
post Jul 10 2019, 05:46 AM

Posts: 1,090
Joined: 16-June 05

I have the table "tblAppointments"
tblAppointments has the following fields:
AppointmentsId - autonumber
AppointmentDate as Date and contains both date and time. e.g 05/07/2019 9:00:00 AM, 05/07/2019 10:00:00 AM.....

Using DAO, I have created a recordset on the table "tblAppointments" returning only records for a given date
I now need to loop through this recordset (or do a lookup to find a given time
How do I loop through a recordset?

Go to the top of the page
post Jul 10 2019, 06:12 AM

Posts: 1,758
Joined: 5-February 06
From: Ohio, USA

Assuming the following...
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String

Set db = CodeDb

SQL = "SELECT AppointmentsId, AppointmentDate WHERE AppointmentDate Between #5/7/2019# And #5/7/2019 11:59 PM#"

Set rs = db.OpenRecordset(SQL)

That 'opens' the recordset, using a SQL statement. You could either change the SQL statement to look for the given time...
SQL = "SELECT AppointmentsId, AppointmentDate WHERE AppointmentDate = #5/7/2019 11:59 PM#"

or loop through the recordset, like... (for demo purposes, we're 'printing' the ID and date in the 'Immediate Window')…
(Oh, and add this to the above, starting the next line...)
If Not rs.EOF and Not rs.BOF Then
  Do Until rs.EOF
    Debug.Print rs!AppointmentsID, rs!AppointmentDate
End If

There are other ways to loop as well... but that should get you started.
This post has been edited by nuclear_nick: Jul 10 2019, 06:12 AM

"Nuclear" Nick
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
post Jul 10 2019, 07:19 AM

Access Wiki and Forums Moderator
Posts: 75,699
Joined: 19-June 07
From: SunnySandyEggo

Hi. You can also search for records by using the FindFirst method of the recordset object.

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Jul 10 2019, 08:23 AM

Posts: 66
Joined: 28-March 18
From: Virginia

can you give us a better example of what you are searching for?

Are you looking to determine whether an appointment already exists that overlaps the time you are searching for?

Or are you looking for a period of a certain length where no other record overlaps it?


Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    16th July 2019 - 05:24 AM