Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2002
    Posts
    97

    Unanswered: Parsing a string value

    Okay,

    This is one of those that I think I should be able to get but it's eluding me.

    I'm getting a string value input from the user in a text box on the form. The values are then passed to an SQL statement that generates a report.

    In the text box the user is asked to specify a state or states they'd like to search for the report. The problem is I can't put the and/or statement into the string.

    So if a user enters "AK" it's fine. However, if they enter anything additional like AK, AL, ME then the query sees it as one big string and returns no records. Initially, I thought I could use the instr() function and substitute an " OR" in place of each comma but haven't gotten that to work yet.

    Any help or suggestions would be appreciated.

    Thanks.
    Chris

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Let's have a look at the current SQL query string.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Dec 2002
    Posts
    97

    SQL and such.

    The SQL is fine.

    It's the incoming string from the text box that's a problem.

    Right now, the sqlST variable is in "non-working" mode. Meaning, it's me just working on ideas/options.

    Thanks for the help.
    Chris





    strTerm = frm.txtTerm
    strCouns = frm.txtCouns
    strST = frm.txtST
    strCEEB = frm.txtCEEB



    If Len(strTerm) > 0 Then

    sqlTerm = " AND ([APP_TERM]= " & q & strTerm & q & ") "

    Else

    sqlTerm = ""

    End If


    If Len(strST) > 0 Then

    Lposition = InStr(strST, ",")

    'Debug.Print Lposition

    If Lposition > 0 Then

    sqlST = " AND ([ST] = " & q & IIf(Len(InStr(strST, ",")) > 0, Left(strST, Lposition - 1) & q & " OR ", strST) & q


    Else

    sqlST = ""

    End If
    End If

    If Len(strCEEB) > 0 Then

    sqlCEEB = " AND ([HS_CEEB] = " & strCEEB & ") "

    Else

    sqlCEEB = ""

    End If




    sqlAll = sqlTerm + sqlCouns + sqlST + sqlCEEB


    Debug.Print sqlAll



    qsel = "SELECT Master.CAMPUS_ID, Master.[SOC#], Master.GENDER, Master.F_NAME, Master.L_NAME, Master.CITY, " & _
    "Master.ST, Master.ZIP, Master.CNTY, Master.PHONE, IIf([Email]='NO E-MAIL ADDRESS AVAILABLE',' ',[Email]) AS EMAILMod, " & _
    "Master.EMAIL, Master.HS_CEEB, Master.HS_NAME, Master.GPA, Master.SATV, Master.SATM, Master.TSAT, Master.ACT, " & _
    "Master.[HS_RANK_%TILE] AS RankPercentile, Master.RANK, Master.SCHOL_AMT, Master.SCHOL_CODE, Master.MAJOR_CD, " & _
    "Master.TUIT_DEP, Master.APPLIED_COLLEGE, Master.APPLIED_MAJOR, Master.COLLEGE, Master.MAJOR, Master.ACCEPTANCE_STATUS, " & _
    "Master.ACCEPTANCE_DATE, Master.SPEC_AC_PROG, Master.SPEC_PROG, Master.COLLEGE_CD, Master.APP_TERM, Master.REQ_RULE, Master.ADMISS_REP " & _
    "FROM Master WHERE (((Master.REQ_RULE) = 'F' Or (Master.REQ_RULE) = 'FI')) " & sqlAll & " ORDER BY Master.MAJOR_CD;"

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You could use soemthing like split() to break the component parts, and then build the sql based on the array of retruned elements.

    I'd suggets however that you consider a combo / list box with multi select. populate the combo / list as requried from your preferred data source (table or filed list (up to you)). You may need to add additional elements to your list box such as no specified state.

    Iterate through the items selected collection of the combo / list box. The advantage - you don't have to worry about crap data ie soemone putting in a 3 digit code, or wrong code....

  5. #5
    Join Date
    Dec 2002
    Posts
    97

    ComboBox

    I thought of using a combobox or something similar...but thought this would be a quicker interim fix. Now, I'm beginning to think otherwise!

    -C

  6. #6
    Join Date
    Dec 2002
    Posts
    97

    Combo

    Actually, I just realized I'd have a similar or even the same problem if I used a combobox.

    By using a combo/list box I'd still have to separate the values put them in quotes and separate them with an and/or statement. I guess it would be a bit easier since with some sort of list I'd just have to step through putting the " OR" after each value.

    or something like that..... (I'm typing outloud)

    -C

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    There is no way round it you are going to have to buiild the sql (or at least the parameters) if you are using a docmd

    effectively your where clause is going to be soemthing like
    where state in (<element1>,<element2>...<elementn> if there are more than one states

    where state = <element1> for one state

    you could use the user input direvt but you are creating a source of problems for yourself where they put in dodgy data.

  8. #8
    Join Date
    Dec 2002
    Posts
    97

    Talking Solution

    I'm gonna disagree that there is no solution....here it is....

    (It came to me driving to work this morning.)



    If Len(strST) > 0 Then


    strHold = frm!txtST

    Do While InStr(strHold, ",") > 0


    strRight = Right(strHold, Len(strHold) - InStr(1, strHold, ",") - 1)
    strLeft = Left(strHold, InStr(1, strHold, ",") - 1)

    strHold = strLeft & q & " OR " & q & strRight

    Loop

    sqlST = " AND ([ST] = " & q & strHold & q


    Else

    sqlST = ""

    End If


    Thanks all to those who submitted ideas and input. As always, it's greatly appreciated.

    -C

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Mr_Yabbo
    Actually, I just realized I'd have a similar or even the same problem if I used a combobox.

    By using a combo/list box I'd still have to separate the values put them in quotes and separate them with an and/or statement. I guess it would be a bit easier since with some sort of list I'd just have to step through putting the " OR" after each value.

    or something like that..... (I'm typing outloud)

    -C
    Depending on your users, another argument for using the combo/list box is security. What happens if I put * in the criteria box?
    oh yeah... documentation... I have heard of that.

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

  10. #10
    Join Date
    Dec 2002
    Posts
    97

    *

    Teddy,

    In this situation, the user has the option to return all possible results. There is no instance for this db where any one user has restrictions of the information they are allowed to view/print.

    -C

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Fair enough, just something to keep in mind.
    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
  •