Results 1 to 12 of 12
  1. #1
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48

    Error In Select Case Statement

    Hi



    Could someone please assist. My Select Case statement is only returning the Case Else value. Im pretty sure the error is in my syntax ??

    Private Sub SiteSurveyStatus_GotFocus()


    Select Case SiteSurveyStatus

    Case Completed

    If Not IsNull(Me.SurveyComAd) Then (Me.SiteSurveyStatus) = "Completed"

    Case DelayedProgress
    If Not IsNull DateDiff("d",(Me.SurveyStAd),Date()) And (Me.SurveyStAd)>(Me.PlanSurveyStartDate) Then (Me.SiteSurveyStatus) = "Delayed Progress"


    Case InProgress
    DateDiff("d",(Me.PODate),Date())<=0 And IsNull(Me.SurveyComAd) Then (Me.SiteSurveyStatus) = "In Progress"

    Case Delayed
    DateDiff("d",(Me.PODate),Date())>0 And IsNull(SurveyComAd) Then (Me.SiteSurveyStatus) = "Delayed"

    Case Else

    (Me.SiteSurveyStatus) = "Not Scheduled"

    End Select
    End Sub


    Thanking you In advance

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It seems you are missing some double quotes ". But it also appears you are using Select case incorrectly.
    In normal usage you would have some calculation to specify the value of SiteSurveyStatus,
    then use the Select Case to determine logic/program flow.

  3. #3
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    Hi

    I dont know how my status changed to Advanced Beginner - LOL - I certainly am NOT!!

    How would I use the Select Case in this instance.

    or would it be better to use IF statements?

    Im not sure how to go about doing this..

    Thank You

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Follow the Select Case link in post #2, review the structure and info.
    For more detail and example see this Steve Bishop video.
    If you still have questions, post back with details and show us what you tried.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I think your Case values need to be in quotes
    Case "Completed"

    Also I don't understand the If statements, you will only go into those case sections if those values are true. So for the Completed one, SiteSurveyStatus must be "Completed" to go into that section and yet you do an If calculation to set that value to "Completed" which it already is if it goes into that section. Seems others are the same way? Or am I reading that wrong?


    Select Case SiteSurveyStatus

    Case Completed

    If Not IsNull(Me.SurveyComAd) Then (Me.SiteSurveyStatus) = "Completed"

  6. #6
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    Hi

    Ok. I tried the following: Im not getting any errors but it is still just returning the Case Else Value which is "Not Scheduled" even If the SurveyComAd field is completed.



    Private Sub SiteSurveyStatus_GotFocus()



    Select Case SiteSurveyStatus

    Case Not IsNull(Me.SurveyComAd)

    SiteSurveyStatus = "Completed"

    Case Not IsNull(DateDiff("d", Me.SurveyStAd, Date) And (Me.SurveyStAd) > (Me.PlanSurveyStartDate))
    SiteSurveyStatus = "Delayed Progress"


    Case DateDiff("d", Me.PODate, Date) <= 0 And IsNull(Me.SurveyComAd)
    Me.SiteSurveyStatus = "In Progress"

    Case DateDiff("d", Me.PODate, Date) > 0 And IsNull(SurveyComAd)
    Me.SiteSurveyStatus = "Delayed"

    Case Else

    Me.SiteSurveyStatus = "Not Scheduled"

    End Select
    End Sub

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    In your Case statements, you should be comparing to the value in your "Select Case" field which is SiteSurveyStatus. Not sure you need to use Case, maybe try:

    If Not IsNull(Me.SurveyComAd) then
    Me.SiteSurveyStatus = "Completed"
    Else
    If DateDiff(("d", Me.SurveyStAd, Date) And (Me.SurveyStAd) > (Me.PlanSurveyStartDate)) Then
    Me.SiteSurveyStatus = "Delayed Progress"
    Else
    If DateDiff("d", Me.PODate, Date) <= 0 And IsNull(Me.SurveyComAd) Then
    Me.SiteSurveyStatus = "In Progress"
    Else
    If DateDiff("d", Me.PODate, Date) > 0 And IsNull(SurveyComAd) Then
    Me.SiteSurveyStatus = "Delayed"
    Else
    Me.SiteSurveyStatus = "Not Scheduled"
    End If
    End If
    End If
    End If

    Put a break point on the first If line and step through the code to see if it is doing what you want it to do. You can hover the mouse over the fields to see what values they hold.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think there is some confusion as to what you are trying to accomplish.
    If, in your processing, you have some statements that assign one of many possible values to a variable
    such as SiteSurveyStatus, then after that calculation you often use a Select case SiteSurveyStatus
    to follow the proper program logic/flow.

    It isn't clear to me if
    a) you are trying to set the value to be assigned to SiteSurveyStatus, or
    b) you are trying to use the value of SiteSurveyStatus to get to the next process/program step.

    I think you may be confusing the two. If it's calculation, then I agree with Bulzie --you probably don't need Select Case

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    Private Sub SiteSurveyStatus_GotFocus()
    Select Case TRUE
          Case Not IsNull(Me.SurveyComAd)
               SiteSurveyStatus = "Completed"
          Case Not IsNull(DateDiff("d", Me.SurveyStAd, Date) And (Me.SurveyStAd) > (Me.PlanSurveyStartDate))
               SiteSurveyStatus = "Delayed Progress"
          Case DateDiff("d", Me.PODate, Date) <= 0 And IsNull(Me.SurveyComAd) 
               Me.SiteSurveyStatus = "In Progress"
          Case DateDiff("d", Me.PODate, Date) > 0 And IsNull(SurveyComAd)
               Me.SiteSurveyStatus = "Delayed"
          Case Else
               Me.SiteSurveyStatus = "Not Scheduled"
    End Select
    End Sub
    If you want to use Select Case do the above.
    Your problem was that the 5 Case evaluations all return true or false, so that's what the top Select Case compares to.
    Last edited by davegri; 12-04-2017 at 05:23 PM. Reason: syntax

  10. #10
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    Hi Everybody

    THANK YOU for the help. Both the solutions work. I now see where my error was in the Select Case. I simply didn't understand the logic .... If I understand it correctly the case statements need a value to compare to and cant work like a Switch statement in SQL.

    I really appreciate the help and I continue to learn thanks to the awesome help from you guys. Thank You Once again.

    P.S. What would your opinion be as far as which one would be better to use in my form - or is it a case of 6 of one and half a dozen of the other

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,818
    I would go with ElseIf.
    Code:
    If Not IsNull(Me.SurveyComAd) Then
        Me.SiteSurveyStatus = "Completed"
    ElseIf DateDiff(("d", Me.SurveyStAd, Date) And (Me.SurveyStAd) > (Me.PlanSurveyStartDate)) Then
        Me.SiteSurveyStatus = "Delayed Progress"
    ElseIf DateDiff("d", Me.PODate, Date) <= 0 And IsNull(Me.SurveyComAd) Then
        Me.SiteSurveyStatus = "In Progress"
    ElseIf DateDiff("d", Me.PODate, Date) > 0 And IsNull(SurveyComAd) Then
        Me.SiteSurveyStatus = "Delayed"
    Else
        Me.SiteSurveyStatus = "Not Scheduled"
    End If

    But yes, 6 of one ...
    Last edited by June7; 12-05-2017 at 02:20 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    Thank You For The Advice

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

Similar Threads

  1. Select Case Error
    By LaughingBull in forum Access
    Replies: 13
    Last Post: 09-11-2015, 02:05 PM
  2. Replies: 1
    Last Post: 03-06-2015, 11:16 AM
  3. Should I /can I use a Select Case statement?
    By Gina Maylone in forum Access
    Replies: 1
    Last Post: 12-13-2014, 12:08 PM
  4. Replies: 4
    Last Post: 10-09-2014, 12:58 PM
  5. Help with Select Case statement
    By focosi in forum Access
    Replies: 4
    Last Post: 08-09-2011, 12:01 AM

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