Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116

    Unanswered: User prompt query

    I have a query that has a dialog box that opens for the user to enter the data criteria. It is not fancy or complicated just:
    [Enter CMS number]

    However now I need to be able if they want multiple choices. For example they want more than one number they would enter
    850 or 851 in the dialog box. This doesn't work the way I have it now. How do I allow for this?

    Thanks .... texasalynn

  2. #2
    Join Date
    May 2003
    Posts
    47

    what about a range ?

    You could do a range ?
    Between [Enter Lower Range] And [Enter Upper Range]

  3. #3
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    That won't work because it won't always be a range and it could be 2 number or even 6 numbers.

    Thanks. . . texasalynn

  4. #4
    Join Date
    Mar 2003
    Posts
    46
    For a 'quick fix':

    In the criteria field write: [Enter CMS Number 1] OR [Enter CMS Number 2]

    This is obviously not a very neat solution as there will now be two prompts for criteria, not nice from the user-side

    Slightly less quick fix

    Create a form with say 5 Unbound textboxes and a button that runs your query. Then set the query crteria field to [Forms]![Form1]![Text1] OR [Forms]![Form1]![Text2] OR [Forms]![Form1]![Text3] OR [Forms]![Form1]![Text4] OR [Forms]![Form1]![Text5]

    Hope this helps

    CCC

  5. #5
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    Ugh! I was really looking for a way to have it work as a criteria line where you type in
    "850" or "851" or "733" etc.

    Is that not possible?

    texasalynn

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    You can’t

    try from a form and string the criteria

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by axsprog
    You can’t

    try from a form and string the criteria
    thats not quite true but the other way is messy, long winded and totaly evil, it involves using vb to generate the query from the results in a input box of some kind

    unless of course a filtered recordset will do as you can take the entire filter from a user input if you wanted
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    correct but within the parameter query itself
    [enter id] and not calling up a function you cannot

    I agree - the other way can be evil

  9. #9
    Join Date
    May 2003
    Posts
    47

    maybe explain a little more ?

    Maybe if you explain a little more detail of what you are doing , there is an alternative way to get the input and results you want ?

  10. #10
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    I was looking for a way for the user to input much like in the criteria of a query:

    "820" or "825" or "733" or "735"

    I didn't know it would be an massive under taking.

    Thanks . . . texasalynn

  11. #11
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    Originally posted by texasalynn
    I was looking for a way for the user to input much like in the criteria of a query:

    "820" or "825" or "733" or "735"

    I didn't know it would be an massive under taking.

    Thanks . . . texasalynn
    dont think we can do easily in a saved query. some simple vb codes may work and give users a bit more ease by allowing them to enter 820,825,733,735... or 820-825-733... or even 820 825 733 ...

    the idea is to make vb codes to loop thru the string, get all numeric strings out and use that for the SQL statement

    if you are not familiar with vb, but want that thing done this way, just let us know. someone can do it for you.

    hope this helps
    qha_vn

  12. #12
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    what is th maximum number of these type of criteria would you want to search for in one one search?

    ie "835" or "675" or "543" is 3 items

  13. #13
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by axsprog
    what is th maximum number of these type of criteria would you want to search for in one one search?

    ie "835" or "675" or "543" is 3 items
    i was thinking more
    Option Compare Database
    Option Explicit

    Sub q1()
    Dim tmp As String
    ReDim wheredata(0) As String
    Dim tmp2 As Integer
    tmp = InputBox("enter conditions")
    tmp2 = InStrRev(tmp, ",")
    While tmp2 <> 0
    wheredata(UBound(wheredata)) = " [field] = " & Trim(Right(tmp, Len(tmp) - tmp2))
    ReDim Preserve wheredata(UBound(wheredata) + 1)
    tmp = Left(tmp, tmp2 - 1)
    tmp2 = InStrRev(tmp, ",")
    Wend
    ReDim Preserve wheredata(UBound(wheredata) + 1)
    wheredata(UBound(wheredata)) = "[field] = " & tmp
    tmp = ""
    For tmp2 = 0 To UBound(wheredata)
    If tmp2 > 0 Then tmp = tmp & " or"
    tmp = tmp & wheredata(tmp2)
    Next
    MsgBox tmp
    CurrentDb.QueryDefs.Delete "query1"
    CurrentDb.CreateQueryDef "query1", "select * from table1 where" & tmp

    End Sub

    still bug but i don't have time to finish it atm
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  14. #14
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    create a table called tblCrit
    create a field with the same datatype as the field that has the data in the main table
    call this field mycrit

    put 3 buttons on your form called
    cmdClearCrit 'this clears the criteria
    cmdGetCrit 'this will add criteria
    cmdGetData 'this will return the results

    i am assuming you are using 97 or 2000

    if 2000 and you are not using ado (I do not think you are)
    got to the vb window
    tools/references
    remove the check for ado
    scroll down to Microsoft DAO 3.5 or 3.6 and check it
    close the reference dialog box and close the vb window

    If 97 this is already the default

    place the following code behind the cmdclearCrit

    docmd.setwarnings false
    docmd.runsql "delete * from tblcrit"
    docmd.setwarnings true

    place the following code behind cmdgetcrit

    dim db as database
    dim rst as recordset
    set db = currentdb
    set rst = db.openrecordset ("tblcrit")
    dim x as variant
    x = inputbox("Enter Criteria")
    rst.addnew
    rst!mycrit = x
    rst.update
    rst.close
    set rst = nothing


    you can run this as many times as needed to create all the criteria

    create query called qryMycrit
    add the tblcrit and your other table to the query
    join mycrit from tblcrit to the field in your table
    bring down the fields that you want displayed for this data

    Place the following code behind cmdGetData
    docmd.openquery "qryMycrit"

    To see how this works

    click cmdClearCrit to start a new query

    click cmdGetCrit

    mY CAVEAT TO THE USER ***This is quick hack code and anything after 20 criteria items may get cumbersome***

    then click cmdGetData

    all of the dat you need should be displayed

    hth -I tested and it works

  15. #15
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    right i've had time to complete the debuging he's the finished version of my code

    Sub q1()
    Dim tmp As String
    ReDim wheredata(0) As String
    Dim tmp2 As Integer
    tmp = InputBox("enter conditions")
    tmp2 = InStrRev(tmp, ",")
    While tmp2 <> 0
    wheredata(UBound(wheredata)) = " [field] = " & Trim(Right(tmp, Len(tmp) - tmp2))
    ReDim Preserve wheredata(UBound(wheredata) + 1)
    tmp = Left(tmp, tmp2 - 1)
    tmp2 = InStrRev(tmp, ",")
    Wend
    wheredata(UBound(wheredata)) = " [field] = " & tmp
    tmp = ""
    For tmp2 = 0 To UBound(wheredata)
    If tmp2 > 0 Then tmp = tmp & " or"
    tmp = tmp & wheredata(tmp2)
    Next
    CurrentDb.QueryDefs.Delete "query1"
    CurrentDb.CreateQueryDef "query1", "select * from table1 where" & tmp & ";"

    End Sub


    the query it's self can be what ever you want i suggest you go to the query builder make the query then copy and past the sql the builder makes minus the user defined critiria and this will work with near infinate values they just need to be comma delimited you then base the form on the query this makes
    Last edited by m.timoney; 06-13-03 at 09:59.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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