Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    String for case select (xp/2003)

    I am trying to create in code from a table a lsit for a case select. when I debug.print what the code creates and then hard code that into the module it works. But as variable it does not. Any ideas? This is the code:
    The important part is to create str1

    Set rst = db.OpenRecordset("tblcode")




    rst.MoveFirst
    Do While Not rst.EOF
    With rst
    strquote = Chr$(34)
    Select Case .Fields(2).Value

    Case 1 'minutes in Reshet
    str1 = str1 & strquote & .Fields(1).Value & strquote & ","
    Case 2 'payment no matter what
    str2 = str2 & .Fields(1).Value & ","
    On Error Resume Next

    Case Else

    End Select
    .MoveNext
    End With

    Loop
    str1 = Left(str1, Len(str1) - 1)
    'str1 = Right(str1, Len(str1) - 1)
    str2 = Left(str2, Len(str2) - 1)

    rst.Close
    Set rst = Nothing

    Set rst = db.OpenRecordset("reports_excel")
    Dim x As Variant
    rst.MoveFirst
    With rst
    Do While Not rst.EOF
    Debug.Print str1
    .Edit
    Select Case .Fields(2).Value

    Case str1
    .Fields(7).Value = 1
    Case str2
    .Fields(7).Value = 2

    Case Else ' all regular calls - 500
    If .Fields(2).Value Like "(972) 1800*" Then
    .Fields(7).Value = 2
    ElseIf .Fields(2).Value Like "(972) 144*" Then
    .Fields(7).Value = 2
    Else
    x = (.Fields(2).Value Like "*(972)*")
    If x = False Then
    .Fields(7).Value = 2
    Else
    .Fields(7).Value = 3
    End If
    End If
    End Select
    .Update
    intCount = intCount + 1
    'msgbox intcount mod 100
    .MoveNext



    Loop
    End With
    rst.Close
    Set rst = Nothing

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

    Re: String for case select (xp/2003)

    It's hard for us to know what goes on when the code is actually run. Try single-stepping through the code to see what happens.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: String for case select (xp/2003)

    to test it I did a debug.print on what the first statement creates, then pasted that into the code as hard statement instead of Str1. Whn it is a variable it does not work in the select, but as a hardcode it does. Does that clarify the problem a bit? If not I can I post a stripped down version?
    Thanks as usual

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

    Re: String for case select (xp/2003)

    I'd still try to single step through the version using variables, but if that doesn't help, yes, please post a stripped down copy of the database.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: String for case select (xp/2003)

    I had tried single stepping and that did not enlighten me. The goal of the code in module1 function Addcode is to check the phone number in the third field of table excel_report and see if it matches any of the numbers in tblcode that are coded as 1, and put a 1 in the last field in the table excel_report. The database is attached
    Thanks

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

    Re: String for case select (xp/2003)

    Ah, now I understand what you are trying to do. I'm sorry, but it won't work this way. When using

    Case variable

    variable must represent a single value, not a list of values.

    You don't need code for this, you can use an update query. This is easier, and will execute much faster than looping through a recordset. I have attached the database with the query (I tried to post the SQL, but although my browser can display Ivrit characters, they get mangled by the Lounge software.)

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: String for case select (xp/2003)

    I had originally thought of an update, but for other reasons since there are other variables that need to be checked for that are in Ivrit and therefore do not work so well in criteria (Right to left and left to Right issues) for a query, I decided to step through it. Now I see I will use both approaches. First I will run the update query and them step through the recordset with an opening if clause to check that intcode is null, and only then update it.
    Thanks for the help
    Zave

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: String for case select (xp/2003)

    As a point of understanding- I have come across this a few times.
    Why is there a difference between an actual string and a variable representing in some situations in VBA and not others? and how do you know when, rather than go nuts for an entire afternoon trying to figure out why when the string variable is only one possibility it works but when it is an exact replicate of the format for a few options in the case statement it does not?
    Or is this a decree from on high which one may not investigate?
    Thanks

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

    Re: String for case select (xp/2003)

    What you wanted to construct was something like

    Case "a", "b", "c", "d"

    In this line, "a" is a string, as are "b" and "c" and "d". Each of these can be replaced by a variable, or by an expression:

    Case strVar1, "b", strVar2 & "c", Left(strVar3, 4)

    But "a", "b", "c", "d" as a whole is *not* a string - it is a list of strings. You cannot replace it with a string.

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: String for case select (xp/2003)

    If I understand correctly the comma is not part of the string, it is part of the VBA code so it cannot be in a string variable. Is that correct?

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

    Re: String for case select (xp/2003)

    Yes, that is the essential part.

Posting Permissions

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