Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85

    Question Unanswered: Criteria selection option in parameter query

    Dear All

    On a parameter query I have put three Text box references from a form on three different fields (CUSTOMER, LOT NO, MODEL). I want to have a search facility working like the following sentence

    CUSTOMER and/or LOT NO and/or MODEL

    What I have done is that I have put all the parameters in the same row of criteria of query. Now if on the form

    I put a CUSTOMER in and leave the other two blank, it would return with nothing
    I put CUSTOMER in and put asterisk (*) in other two, still brings nothing

    It only works when all the fields are filled in.

    I want to provide search facility with an option among the three parameters. Can somebody suggest something?

  2. #2
    Join Date
    May 2002
    Location
    London
    Posts
    87
    You should just need to put them on different rows. where you put several criteria on the same row of the design grid, Access treats these as ANDs. Move each one to its own row and it will be fine, However if you have and non-parameterised criteria that you want to apply to all searches (e.g Active=True or whatever) , these will need to be repeated in every row.

  3. #3
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85
    Thank you very much for your quick reply.

    I wil try it now. I didn't earlier thinking that if I put in different rows, it would treat as ORs only and I would not be able to search as AND.

    Thanks for your advice.

  4. #4
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Smile Prob solution

    in the code set the statement

    Dim SQL1 AS string

    SQL1 = "SELECT Customer,LOTNO, Model from TABLENAME" & _
    "WHERE "

    if txtbCUSTOMER > "" then
    SQL1 =SQL1 & " CUSTOMER LIKE " & txtbCUSTOMER.text & " AND "
    end if

    if txtbLOTNO > "" then
    SQL1 =SQL1 & " LOTNO = " & txtbLOTNO.text & " AND "
    end if

    if txtbMODEL > "" then
    SQL1 =SQL1 & " MODEL LIKE " & txtbCUSTOMER.text & " AND "
    end if

    SQL1 =mid(SQL1,1,len(sql1-5))

  5. #5
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85
    I have tried what Risky suggested earlier. Unfortunately it is not working the way I want it to do.

    If I have CUSTOMER field in the first row and others in row2 and row3. Now if I put a name of a CUSTOMER on form and run the report based on the same query,it bring back some results. Now even if I put something in row2 or row3 Text boxes, it does not make any effect on the result. It only works if I use row2 and row3 independent of row1 (CUSTOMER).

    So we are still there. I will try to use Marvels method.

    Thanks

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    another possible approach.
    make three additional text boxes (visible = no)

    your GO button does the following
    if isnull(visibleBox1.value) then
    hiddenBox1.value = "*"
    else
    hiddenBox1.value = "*" & visibleBox1.value & "*"
    endif
    etc for 2, 3

    and your query criteria (all on the same row!) read
    Like [forms]![formName]![hiddenBox1]
    etc for 2, 3

    now you have not only your original complete-field search, but also sub-string search in each field ("uBs" finds "SubString"). if it's a humungous database, this could take some time..

    ...variations include ltrim$(rtrim$(visiblebox1.value)) depending on how you feel about spaces.


    izy

  7. #7
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85
    By GO button, do you mean on onclick event of the command button on form that opens the report?

    Thanks

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yes but!

    the code was intended as a search function and it might not be appropriate as the basis for a report because of the subString capability.

    consider showing the results of your query on a form first... if there were multiple hits, the user gets to select the record he really wants to use for the report.

    izy

    another possible variation on
    hiddenBox1.value = "*" & visibleBox1.value & "*"
    is
    hiddenBox1.value = visibleBox1.value & "*"
    now "sIT" finds "sit", "sitting", but not "positive"

Posting Permissions

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