Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2004
    Posts
    48

    Unanswered: Conditional statement

    One of the fields in an Access database tracks Thursday night visitors to a place of business. The user clicks on a button that will fill in the day of the week and a date in their respective fields. When that day of the week is Thursday at 5 pm or later, a prompt will appear telling the user that this is a Thursday night visitor and asking the user if they want the Thursday night visitor checkbox to be checked off. If the user clicks "Yes", then the Thursday Night Visitor checkbox is automatically checked. The code that makes the above happen is as follows:

    Private Sub cmdCalendar_Click()

    Dim MyTime
    Dim IsThurs As Integer

    Date.Value = Now()
    MyTime = #5:00:00 PM#
    If [Time] > MyTime And Weekday([txtDay]) = 4 Then
    IsThurs = MsgBox("This is a Thursday Night Visit" & Chr(13) & "Would you like to tag this as an evening visit?", vbYesNo, "Front Desk System")
    If IsThurs = vbYes Then
    ThursdayVisitor.Value = -1
    End If
    'Else
    End If

    End Sub


    The users of the database now want to track Monday night visitors as well. I've tried to add code to include Monday nights to the above but can't get the syntax right. Can anyone out there help please?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If the same checkbox is to be checked, I'd add to the If clause here (If A Or B Then). If it's a separate checkbox, I'd simply add another block of code. If you have a failing effort, why don't you post it and we'll fix it. Be sure to specify the answer to the above.
    Paul

  3. #3
    Join Date
    Aug 2004
    Posts
    48

    Conditional Statement

    Hi Paul,
    Thanks for the input.

    There are 2 checkboxes; one for Thursday and another for Wednesday (I switched it from Monday for testing purposes).

    The code has been simplified and works perfectly:

    Dim MyTime
    MyTime = #11:00:00 AM#
    If [Time] >= MyTime And Weekday(Now) = vbThursday Then
    ThursdayVisitor.Value = -1
    End If

    Date.Value = Now()
    End Sub


    When I add:
    If [Time] >= MyTime And Weekday(Now) = vbWednesday Then
    WednesdayVisitor.Value = -1
    below the code for ThursdayVisitor.Value = -1 I get a "Variable not defined" message.

    Is this just a syntax issue? I'm new to vb so I can use all the help I can get!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What does it highlight?

    By the way, if it's not too late, "Date" and "Time" are not good as names of fields, as they can conflict with the Date() and Time() functions. I'd change them.
    Paul

  5. #5
    Join Date
    Aug 2004
    Posts
    48

    Conditional Statement

    WednesdayVisitor.Value = -1 is highlighted.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Then my guess is that's not a field or control on the form.
    Paul

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Maybe WednesdayVisitor is still named MondayVisitor on the form?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Aug 2004
    Posts
    48

    Conditional Statement

    The WednesdayVisitor field is in the table and on the form as WednesdayVisitor.

    Even though I'm getting the "Variable not defined" message, does the code look like it should work? Is the syntax correct?

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If the first works, there's no reason the second shouldn't. You can try starting with "Me." so you get a list of the available options.
    Paul

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Sorry, but I don't see how that that code could ever have worked, as presented in your original post. [Time] is the "variable" that Access can't find. By placing the square brackets around Time, which is a function, you are, in essence, telling Access that it's actually an object, and Access can't ind it!

    When you change

    If [Time] >= MyTime

    to

    If Time >= MyTime

    your code works as expected, for Wednesday and Thursday.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Check the spelling carefully. I've often looked at named controls and thought "spelled exactly the same!", only to find 20 minutes later that they were were not quite "spelled excatly the same".
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Aug 2004
    Posts
    48

    Conditional Statement

    Thanks to all for your valuable suggestions and comments.

    I've simplified the code even further and have come up with something that works. I had to manipulate the day of the week and the time to test it out but so far so good. I won't know for sure until next Monday and Thursday.
    (To Missinglinq: "Time" in this database is a field name - I know, pretty crappy!)

    Private Sub cmdCalendar_Click()

    If [Time] >= #5:00:00 PM# And Weekday(Now) = vbThursday Then
    ThursdayVisitor.Value = -1
    End If
    If [Time] >= #5:00:00 PM# And Weekday(Now) = vbMonday Then
    MondayVisitor.Value = -1

    End If

    Date.Value = Now()

    End Sub

  13. #13
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad you got it working, but I cannot stress enough how important Paul's advice to you in Post #4. Time and Date are Reserved Words in Access, and should not be used as the name for fields or objects. Sooner or later, staying with these names is going to jump up and bite you on an unpleasant part of your body!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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