Problem with SELECT Query (1 Viewer)

Ryan142

Member
Local time
Today, 09:49
Joined
May 12, 2020
Messages
52
Hi Guys,

Ran into a problem with my SELECT Query and unsure what it is

My program at large is a calendar that is searching the database for any bookings for the shown month. The specific code where the problem is occuring is the part where it searches for any existing bookings, then populates the labels of my calendar. Another question I did have is whether CurrentDb.Execute fails - for lack of a better term - queitly and if it will produce an error if there is no record found.

Anyway the code is below and I hope you can see something I can't!!

Code:
    Counter = 1
    
    For Counter = 1 To DaysOfMonth
        BookingID = 0
        Label = "lblInfo" & FirstSearch
        TempDate = Counter & "/" & CurrentMonth & "/" & CurrentYear
        SQL = "SELECT BookingID FROM tblFlightsBooked WHERE DateOfFlight = " & TempDate
        bID = CurrentDb.Execute(SQL)
        If bID <> 0 Then
            TimeStart = DLookup("TimeStart", "tblFlightsBooked", "BookingID = " & bID)
            TimeFinish = DLookup("TimeFinish", "tblFlightsBooked", "BookingID = " & bID)
            Me.Controls(Label).Caption = Chr(13) & Chr(10) & TimeStart & "-" & TimeFinish
        End If
        FirstSearch = FirstSearch + 1
    Next Counter
 

Minty

AWF VIP
Local time
Today, 09:49
Joined
Jul 26, 2013
Messages
10,355
A number of things , You would need to enclose your date with date delimiters #
SQL = "SELECT BookingID FROM tblFlightsBooked WHERE DateOfFlight = #" & TempDate & "#"
You would also need to ensure it is formatted mm/dd/yyyy or yyyy-mm-dd as access expects dates in an American format.

You can't execute a SELECT query, so you would get an error when this is run, so you have probably turned warnings off or have an error handler that is obscuring the issue.
You would have to open a recordset to do what you are trying to do, and then retrieve the booking ID from the recordset.

Edit:
if you opened the recordset you would also have direct availability of the times, avoiding the need for the DLookups as well.
 

Ryan142

Member
Local time
Today, 09:49
Joined
May 12, 2020
Messages
52
A number of things , You would need to enclose your date with date delimiters #
SQL = "SELECT BookingID FROM tblFlightsBooked WHERE DateOfFlight = #" & TempDate & "#"
You would also need to ensure it is formatted mm/dd/yyyy or yyyy-mm-dd as access expects dates in an American format.

You can't execute a SELECT query, so you would get an error when this is run, so you have probably turned warnings off or have an error handler that is obscuring the issue.
You would have to open a recordset to do what you are trying to do, and then retrieve the booking ID from the recordset.

Edit: if you opened the recordset you would also have direct availability of the times, avoiding the need for the DLookups as well.

Cool that all sounds ok except for thge recordset part as I've never actually ventured into them before!
From looking up it would have to be something like:

Code:
Dim rst As Recordset
SQL = "SELECT BookingID FROM tblFlightsBooked WHERE DateOfFlight = #" & TempDate & "#"
Set rst = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)

But what would I do from there?
Thanks!
 

Minty

AWF VIP
Local time
Today, 09:49
Joined
Jul 26, 2013
Messages
10,355
Okay - this is air code untested but something like
Code:
    Dim rst As Recordset
    
    SQL = "SELECT BookingID, TimeStart, TimeFinish FROM tblFlightsBooked WHERE DateOfFlight = #" & TempDate & "#"
    Set rst = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
    
    If Not (rst.EOF Or rst.BOF) Then  ' There are records as you aren't at the end of the recordset
         Me.Controls(Label).Caption = Chr(13) & Chr(10) & rst.Fields("Timestart") & "-" & rst.Fields("TimeFinish")
    End If
    rst.Close
This assumes you will only have one record per date, well this will only retrieve the first record.
 

Ryan142

Member
Local time
Today, 09:49
Joined
May 12, 2020
Messages
52
Okay - this is air code untested but something like
Code:
    Dim rst As Recordset
   
    SQL = "SELECT BookingID, TimeStart, TimeFinish FROM tblFlightsBooked WHERE DateOfFlight = #" & TempDate & "#"
    Set rst = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
   
    If Not (rst.EOF Or rst.BOF) Then  ' There are records as you aren't at the end of the recordset
         Me.Controls(Label).Caption = Chr(13) & Chr(10) & rst.Fields("Timestart") & "-" & rst.Fields("TimeFinish")
    End If
    rst.Close
This assumes you will only have one record per date, well this will only retrieve the first record.

Fantastic, I'll give it a shot in a second. If I stuck it in a for loop like I showed in the inital post would that allow it to work for multiple records of the same date?
 

Micron

AWF VIP
Local time
Today, 05:49
Joined
Oct 20, 2018
Messages
3,476
If Not (rst.EOF Or rst.BOF)
If your intent was to use Allen Browne's method, it's (rst.EOF AND rst.BOF) - actually, he puts BOF before EOF but I doubt that matters. I wonder if an OR situation could exist where the cursor is in a recordset at either the beginning or end of the file yet there are still records?
 

Isaac

Lifelong Learner
Local time
Today, 02:49
Joined
Mar 14, 2017
Messages
8,738
I would just test for EOF

If rs.EOF=False Then 'there are records

or

If Not(rs.EOF) if you prefer.

Also, this might be one of those times when remembering to Set the recordset object to Nothing, after closing it, might be a very good idea.
 
Last edited:

Ryan142

Member
Local time
Today, 09:49
Joined
May 12, 2020
Messages
52
I would just test for EOF

If rs.EOF=False Then 'there are records

or

If Not(rs.EOF) if you prefer.

Also, this might be one of those times when remembering to Set the recordset object to Nothing, after closing it, might be a very good idea.
I see, I've done some reading for the rest of today and vaguely begun to understand recordsets and such a bit. I understand what you've written and how it works but I have two questions.

1) What is the benefit of setting the recordset to Nothing, I've seen it mentioned before but never really understood the point if you're going to close it anyway. Probably a naiive statement but there you go :D

2) Do you have any clue about how you could optimise this kind of checking to allow for multiple records within the search criteria. I understand rst.MoveNext and rst.MovePrevious can change between the potential records, but then I run into the problem of going past the EOF or before the BOF which will obviously create an unpleasent error!

Thanks for the message anyway just wondering if you could enlighten me
Many Thanks!
 

Isaac

Lifelong Learner
Local time
Today, 02:49
Joined
Mar 14, 2017
Messages
8,738
Do until rs.eof=true
....
Loop

.. sent from phone
 

Isaac

Lifelong Learner
Local time
Today, 02:49
Joined
Mar 14, 2017
Messages
8,738
1) What is the benefit of setting the recordset to Nothing, I've seen it mentioned before but never really understood the point if you're going to close it anyway. Probably a naiive statement but there you go
Usually there is no value in setting an object to nothing once it goes out of scope, it is destroyed anyway. but in this specific case of dao record sets, if you open and close a lot of them and don't set them explicitly to nothing, it can cause a problem. I'm not in front of my computer right now or I would post a link to an article about it.
 

Cronk

Registered User.
Local time
Today, 20:49
Joined
Jul 4, 2013
Messages
2,770
Is there only one booking a day? Otherwise, the label caption will have the times for one of the bookings and without sorting in the query, not necessarily the first or last booking.
 

Users who are viewing this thread

Top Bottom