Results 1 to 3 of 3
  1. #1
    Cavman is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    9

    Using date variable in a SELECT statement to filter recordset

    Hi, I wonder if anyone can help. I'm trying to open a recordset using the code below, but I think there's something wrong with the syntax of the last part of the WHERE clause. Everything works fine until I add the 'BookingDate = dtDateHolder' at the end, then it brings back no results. It works perfectly as a query, so I'm guessing either my syntax must be wrong, or there is a problem with the data type in the dtDateHolder field. Please help!



    Public dtDateHolder As Date
    Public intRoomIDHolder As Integer
    intRoomIDHolder = Me.cmbRoomSelect ' insert value from combo box on form
    dtDateHolder = Me.DateSelect ' insert value from date selector on form

    Set rsRecordsToBook = CurrentDb.OpenRecordset("SELECT tbl_BookingsbyRoom.RoomName, tbl_BookingsbyRoom.BookingDate, tbl_BookingsbyRoom.BookedBy, tbl_BookingsbyRoom.Selected, tbl_BookingsbyRoom.Lock FROM tbl_BookingsbyRoom WHERE (tbl_BookingsbyRoom.Selected=True) AND (tbl_BookingsbyRoom.RoomName = " & [intRoomIDHolder] & ") AND (tbl_BookingsbyRoom.BookingDate = #" & [dtDateHolder] & "#);")

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    In order for it to work in Access SQL, the [dtDateholder] format must be mm/dd/yy. If your system date format is not mm/dd/yy, try using format([dtDateholder],"mm/dd/yy") to force the date to the correct format.

    John

  3. #3
    Cavman is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    9
    That did the trick! Thank you

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form data in SELECT statement recordset
    By nvrwrkn in forum Programming
    Replies: 15
    Last Post: 10-16-2012, 03:57 PM
  2. How to Select second Recordset from first recordset
    By FrustratedAlso in forum Programming
    Replies: 28
    Last Post: 05-10-2012, 05:45 PM
  3. open recordset with variable SQL
    By rivereridanus in forum Queries
    Replies: 4
    Last Post: 07-27-2011, 12:58 PM
  4. select statement with variable table name
    By dv89k in forum Queries
    Replies: 1
    Last Post: 05-26-2011, 10:54 PM
  5. Replies: 3
    Last Post: 02-16-2010, 10:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums