Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    83

    Unanswered: converting string to number

    hi,

    The part of a query in one of my code is:
    lstMedicines is the listbox, from which the user selects the medicines

    'create the IN string by looping thru the listbox
    For i = 0 To lstMedicines.ListCount - 1
    If lstMedicines.Selected(i) Then
    If lstMedicines.Column(0, i) = "All" Then
    flgAll = True
    End If
    strIN = strIN & "'" & lstMedicines.Column(0, i) & "',"
    End If
    Next i



    strSQL = strSQL & "SELECT distinct A.Severity, A.PatientLastName, A.PatientFirstName,A.Date, B.MedicineID,B.Medicines "
    strSQL = strSQL & " FROM [Asthma Action] A, Medicines B"
    strSQL = strSQL & " WHERE A.Medicine1 = B.MedicineID"
    strSQL = strSQL & " AND B.MedicineID in (" & Left(strIN, Len(strIN) - 1) & ")"

    the query in the run time is shown as(in msgbox):
    SELECT distinct A.Severity, A.PatientLastName, A.PatientFirstName,A.Date, B.MedicineID,B.Medicines
    FROM [Asthma Action] A, Medicines B
    WHERE A.Medicine1 = B.MedicineID
    AND A.MedicineID = C.MedicineID"
    AND B.MedicineID in ('1','19')

    The MedicineID column in my table is number datatype, whereas in the
    the result ('1','19'). It is grabbing the right data, but it is in the string format. how can you convert the string(as '1','19') to number (as 1,19).

    Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    Posts
    83

    Re: converting string to number

    Originally posted by skodidhi
    hi,

    The part of a query in one of my code is:
    lstMedicines is the listbox, from which the user selects the medicines

    'create the IN string by looping thru the listbox
    For i = 0 To lstMedicines.ListCount - 1
    If lstMedicines.Selected(i) Then
    If lstMedicines.Column(0, i) = "All" Then
    flgAll = True
    End If
    strIN = strIN & "'" & lstMedicines.Column(0, i) & "',"
    End If
    Next i



    strSQL = strSQL & "SELECT distinct A.Severity, A.PatientLastName, A.PatientFirstName,A.Date, B.MedicineID,B.Medicines "
    strSQL = strSQL & " FROM [Asthma Action] A, Medicines B"
    strSQL = strSQL & " WHERE A.Medicine1 = B.MedicineID"
    strSQL = strSQL & " AND B.MedicineID in (" & Left(strIN, Len(strIN) - 1) & ")"

    the query in the run time is shown as(in msgbox):
    SELECT distinct A.Severity, A.PatientLastName, A.PatientFirstName,A.Date, B.MedicineID,B.Medicines
    FROM [Asthma Action] A, Medicines B
    WHERE A.Medicine1 = B.MedicineID
    AND A.MedicineID = C.MedicineID"
    AND B.MedicineID in ('1','19')

    The MedicineID column in my table is number datatype, whereas in the
    the result ('1','19'). It is grabbing the right data, but it is in the string format. how can you convert the string(as '1','19') to number (as 1,19).

    Thanks in advance.
    In Short, how can I convert text to number.
    I tried adding Clng(strIN) after Next i line in the loop. It didnot worked.
    thanks for any clue or idea

  3. #3
    Join Date
    Oct 2003
    Posts
    66
    The reaosn the converison funciton is not working is because there are non-numeric characters in the value...meaning the ' and , characters.

    I think the best way to change a string variable that is = '1','19' will be to use string maiplulation functions like Left$, Right$, Instr to strip out the ' and , and store the numeric characters.

  4. #4
    Join Date
    Sep 2003
    Posts
    83
    Originally posted by adrkoehler
    The reaosn the converison funciton is not working is because there are non-numeric characters in the value...meaning the ' and , characters.

    I think the best way to change a string variable that is = '1','19' will be to use string maiplulation functions like Left$, Right$, Instr to strip out the ' and , and store the numeric characters.
    can we add these string manipulation functions in the loop itself, like after the line that starts with
    strIN = strIN & "'" & lstMedicines.Column(0, i) & "',"
    which function will be best, can you please explain with an example,

    Thanks.

  5. #5
    Join Date
    Oct 2003
    Posts
    66
    okay the code below will stip out the 's and , then store the
    numbers.

    Dim LookFor As String
    Dim LookIn As String
    Dim Result1 As Long
    Dim Result2 As Long


    LookIn = "'1','19'"
    LookFor = "'"


    Result1 = CLng(Mid$(LookIn, 2, InStr(2, LookIn, LookFor) - 2))

    Result2 = CLng(Mid$(LookIn, InStr(2, LookIn, LookFor) + 3, InStr(InStr(2, LookIn, LookFor) + 3, LookIn, LookFor) - (InStr(2, LookIn, LookFor) + 3)))

    MsgBox (Result1)
    MsgBox (Result2)

    I know it looks confusing but run it and look up the Mid$ and InStr function in the help. It is the way i would do this....which mean it oculd be the most difficult way since i am not super experienced. Best i can do.

Posting Permissions

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