Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Checking dates (XP)

    I have 3 textboxes on a form :txtRoomID,txtStart and txtEnd. I am trying to check for availability of rooms on between given dates and I am using code I modified from a search result. However, I am getting a type mismatch error. Any help would be appreciated.


    Private Sub Command6_Click()

    Dim RoomCheck As Integer
    RoomCheck = DCount("*", tblBooking, "RoomID=" & Me!txtRoomID & " AND " & _
    tblBooking("Start", dbDate, "<=" & Me!txtEnd) & "AND" & _
    tblBooking("End", dbDate, ">=" & Me!txtStart))
    If RoomCheck > 0 Then
    'Warn User
    MsgBox "Room unavailable on these dates"
    End If

    End Sub
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking dates (XP)

    There seems to be some confusion here. You use tblBooking as if it were a function (the BuildCriteria function to be more precise.) Try this instead:

    RoomCheck = DCount("*", tblBooking, "RoomID=" & Me!txtRoomID & " And " & _
    "Start<=#" & Format(Me!txtEnd, "mm/dd/yyyy") & "# And " & _
    "End>=#" & Format(Me!txtStart, "mm/dd/yyyy") & "#")

    If your browser does strange things to the "less than or equal" and "greater than or equal" symbols, the above expression should contain < = and > = without spaces in between < and = and between > and =

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking dates (XP)

    Thanks for your help, Hans. I enetered exactly as you posted but now I get a different error message:

    You entered can invalid argument in a domain aggregate function.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking dates (XP)

    Oops, the table name should be in quotes: DCount("*", "tblBooking", ...)

    BTW, do you have Option Explicit at the top of your modules, or put otherwise, have you ticked "Require variable declaration" in the Tools | Options dialog in the Visual Basic Editor?

  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking dates (XP)

    Works a treat! With your help, I think I'm getting there with the DCount function. (Famous last words).
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking dates (XP)

    I'm attempting a variation of the DCount function where values from 4 textboxes on frmCheck are used as the criteria to count matching criteria in the table tblBooking. However, the event triggers a syntax error (missing operator). Would someone check the following code and identify the source of the error?

    Option Explicit


    Private Sub Command9_Click()
    Dim CheckDateTime As Integer
    CheckDateTime = DCount("*", "tblBooking", "EmployeeID=" & Me!txtEmployee & " And " & _
    "Start<=" & Me!txtEnd & " And " & _
    "End>=" & Me!txtStart & " And " & _
    "Date=#" & Format(Me!txtDate, "mm/dd/yyyy") & "#")

    If CheckDateTime > 0 Then
    'Warn User
    MsgBox "Already booked"
    End If

    MsgBox "Available"
    End Sub
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking dates (XP)

    Do txtStart and txtEnd contain dates? If so, why did you change the expression for them back from what I posted earlier?

    And what is the role of Date? If it is the current date, you should write Date() but if it is a field, you should write [Date] to avoid confusion with the Date function (this is a good reason, by the way, not to name a field Date)

  8. #8
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking dates (XP)

    txtStart and txtEnd contain short time values, and txtDate contains a short date value. Date is a field in the table tblBooking. Start and End are fields in tblBooking. I am attempting to check that a given employee, on a given date is or is not booked at a given time. This scenario is different from the original hence I tried to develop the code accordingly. Thanks for your help, Hans.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking dates (XP)

    Try this then:

    CheckDateTime = DCount("*", "tblBooking", "EmployeeID=" & Me!txtEmployee & " And " & _
    "[Start]<=#" & Format(Me!txtEnd, "hh:mm AM//PM" & "# And " & _
    "[End]>=#" & Format(Me!txtStart, "hh:mm AM/PM" & "# And " & _
    "[Date]=#" & Format(Me!txtDate, "mm/dd/yyyy") & "#")

    Duck Logo!

  10. #10
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking dates (XP)

    Syntax error again! The four lines stay "redded out"
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking dates (XP)

    Hmm, some closing parentheses magically disappeared... sorry about that. Let's try again:

    CheckDateTime = DCount("*", "tblBooking", "EmployeeID=" & Me!txtEmployee & " And " & _
    "[Start]<=#" & Format(Me!txtEnd, "hh:mm AM//PM") & "# And " & _
    "[End]>=#" & Format(Me!txtStart, "hh:mm AM/PM") & "# And " & _
    "[Date]=#" & Format(Me!txtDate, "mm/dd/yyyy") & "#")

  12. #12
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking dates (XP)

    No Joy! Does the attachment help?
    Attached Images Attached Images
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking dates (XP)

    Rob, there is some kind of devil about - it seems as if the Lounge software is modifying the code I post.

    But if you look closely at the code as it appears in my previous reply, you should see the error: the first Format statement contains two slashes instead of one in AM//PM. Remove one of them and try again.

  14. #14
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking dates (XP)

    "The Devil's in the detail".
    It now works. Thanks for your time and patience, Hans.

    Real Mining.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Checking dates (XP)

    I liked the previous version of Alfred E Neuman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •