UtterAccess.com
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    
 
   
TimTDP
post Jul 10 2019, 05:46 AM
Post#1



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?

Thanks
Go to the top of the page
 
nuclear_nick
post Jul 10 2019, 06:12 AM
Post#2



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


Assuming the following...
CODE
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...
CODE
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...)
CODE
If Not rs.EOF and Not rs.BOF Then
  rs.MoveFirst
  Do Until rs.EOF
    Debug.Print rs!AppointmentsID, rs!AppointmentDate
  Loop
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
 
theDBguy
post Jul 10 2019, 07:19 AM
Post#3


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
 
dale.fye
post Jul 10 2019, 08:23 AM
Post#4



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

--------------------
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