Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Problems using Strings?

    I have written the following code but am getting a 'compile syntax error' message.

    Can anyone please tell me what I am doing wrong and how to fix it?


    Private Sub PlacementOutcomeCodeAdd_Click()
    On Error GoTo PlacementOutcomeCodeAdd_Click

    'PlacementOutcomeCodeAdd is a combo box located on an unbound form
    'which allows the user to select criteria to be used in a query
    'which is linked to the unbound form

    If Me.[PlacementOutcomeCodeAdd].Value = "List All Job Seekers" Then
    Me.[PlacementOutcomeCode].Value = "*"
    ElseIf Me.[PlacementOutcomeCodeAdd].Value = "All But Not Including 'Ceased'" Then
    Me.[PlacementOutcomeCode].Value = "<>"Ceased""
    ElseIf Me.[PlacementOutcomeCodeAdd].Value = "All But Not Including 'Did Not Start'" Then
    Me.[PlacementOutcomeCode].Value = "<>"Did Not Start""
    ElseIf Me.[PlacementOutcomeCodeAdd].Value = "All But Not Including 'Ceased' and 'Did Not Start'" Then
    Me.[PlacementOutcomeCode].Value = "<>"Ceased" And <>"Did Not Start""
    End If

    Exit_PlacementOutcomeCodeAdd_Click:
    Exit Sub

    Err_PlacementOutcomeCodeAdd_Click:
    MsgBox Error$
    Resume Exit_PlacementOutcomeCodeAdd_Click

    End Sub


    Thanks in advance

    Karen Day

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What is it exactly you want put in? The quotes are off here and would cause an error:

    Me.[PlacementOutcomeCode].Value = "<>"Ceased""

    Perhaps you want

    Me.[PlacementOutcomeCode].Value = "<>'Ceased'"
    Paul

  3. #3
    Join Date
    May 2002
    Posts
    157

    Error in my thinking

    I have just realised there is an error in my thinking!!!!!!!

    The idea around this code is to allow the user to select from an unbound combo box which is on an form which is bound to a select query (the query refers back to the form for the info for the basis (the criteria) on which to supply the information).

    I have just realised that the following part of the code works because the * is acting as a wild card in the select query criterion
    Like [Forms]![CASE_LOADED_WITH_SelectF]![PlacementOutcomeCode]


    But the rest of the code is not working because the above is actually searching my PlacementOutcomeCode field data for text that reads eg :

    <>"Ceased" And <>"Did Not Start"
    or
    <>"Ceased"
    or
    <>"Did Not Start"

    What I am wanting to do is to get the like statement above to use what is selected to retrieve the appropriate records.

    I hope I have made myself clearer here?!!

  4. #4
    Join Date
    May 2002
    Posts
    157

    Problems using Strings

    I am attaching some screen dumps to try and show what I am doing.

    I am sure you will have a better way of doing this.

    This is simply the way my mind worked until I realised I was incorrect in my thinking.

    Please advise how you might suggest you would approach what I am trying to do.

    Regards

    Karen Day
    Attached Files Attached Files

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Dear Karen,

    You can work with the Filter property of the form, like this:
    Code:
    Private Sub PlacementOutcomeCodeAdd_AfterUpdate()
    
        Dim strFilter As String
        
        If Me.[PlacementOutcomeCodeAdd].Value = "List All Job Seekers" Then
            strFilter = ""
        ElseIf Me.[PlacementOutcomeCodeAdd].Value = "All But Not Including 'Ceased'" Then
            strFilter = "PlacementOutcomeCode <> 'Ceased' Or IsNull(PlacementOutcomeCode)"
        ElseIf Me.[PlacementOutcomeCodeAdd].Value = "All But Not Including 'Did Not Start'" Then
            strFilter = "PlacementOutcomeCode <> 'Did Not Start' Or IsNull(PlacementOutcomeCode)"
        ElseIf Me.[PlacementOutcomeCodeAdd].Value = "All But Not Including 'Ceased' and 'Did Not Start'" Then
            strFilter = "PlacementOutcomeCode Not In ( 'Ceased', 'Did Not Start' ) Or IsNull(PlacementOutcomeCode)"
        End If
        Me.Filter = strFilter
        Me.FilterOn = True
    
    End Sub
    Have a nice day!

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by marissa jasmine View Post
    Edit: If your Strings are too big to be comfortably used in source code ...
    consider doing a quick find-and-replace on the string before using it

    Thanks for share that
    Why didn't I think of that? If the text you're attempting to display is giving you problems use different text! Have some cake while you're at it.




    Anyway...

    Looking at the overall vibe you're going for here, I think a more elegant solution would be to store the statuses you're using as search criteria in their own table and then bind your selection controls to this table. You will have a much easier time using the selected values to build query criteria. You'll also avoid the need to hard code any updates, additions or deletions to available criteria in your forms, reports and queries.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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