Results 1 to 3 of 3
  1. #1
    Amras is offline Novice
    Windows 2K Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Sioux Falls, SD
    Posts
    29

    Is Format(Now,"hh:nn AM/PM") < rsTime!WedStop


    Hello, all, and thanks for taking a moment to read my post. I've got a table listing each State and a Start and Stop time during which my people can place calls. The State is Text and the Start and Stop times are Date/Time formatted as Medium Time. I've got a recordset that loops thru the table, comparing first the state on my form against the State in the recordset, then if checks whether the current time is > the Start time, then whether the current time is < the Stop time (I tried writing it on a single line and had even worse problems!). There's no problem with my code checking for the State and the Start time, but it always errors on the Stop time (it skips to my Else statement when it should execute the next line of code). The following is the code:
    Code:
        Do Until rsCallTime.EOF
            Select Case strWeekDay
                Case 2, 3, 4, 5, 6 'Mon - Fri
                    If txtState = rsCallTime!State Then
                        If Format(Now, "hh:nn AM/PM") >= rsCallTime!WedStart Then
                            If Format(Now, "hh:nn AM/PM") < rsCallTime!WedStop Then
                                    EnableSameDayCalls
                                Else
                                    DisableSameDayCalls
                                Exit Sub
                            End If
                        End If
                    End If
                Case 7  'Sat
            End Select
            rsCallTime.MoveNext
        Loop
    I'm throwing myself on your mercy! What am I doing wrong?!? I hope I've explained everything sufficiently for you. 'preciate any help you can provide.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    dates are stored as decimal number. The value before the decimal point is the date and after the time express as the number of seconds so far today divided by 86400 (the number of seconds in a day). So now is 42963.9818402778

    using the format function - Format(Now, "hh:nn AM/PM") - converts this decimal number to a string. So assuming WedStop is a date/time function you are trying to compare a string with a number

    if WedStop contains a time only value suggest use the Time function instead

    If Time()< rsCallTime!WedStop Then

    and if it contains a date/time value, the now function

    If now()< rsCallTime!WedStop Then

    you can simplify your code to just open the recordset for the state required or use dcount

    Code:
    if dcount("*","tblStates","[State]='" & txtState & "' AND Now() BETWEEN [WedStart] and [WedStop]")> 0 then EnableSameDayCalls ELSE DisableSameDayCalls

  3. #3
    Amras is offline Novice
    Windows 2K Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Sioux Falls, SD
    Posts
    29
    Worked Perfectly, Ajax!! Thank you very much for pointing-out my mistake!

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

Similar Threads

  1. Replies: 2
    Last Post: 02-22-2017, 12:02 PM
  2. Replies: 4
    Last Post: 09-23-2015, 09:13 AM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 5
    Last Post: 05-28-2015, 03:24 PM
  5. Replies: 2
    Last Post: 04-19-2014, 02:42 AM

Tags for this Thread

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