Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2014
    Posts
    27

    Unanswered: Help creating a search using drop downs

    Need help making a form that uses drop downs as options for criteria and i would llike it to return a report of records that match all of the criteria can anyone help with this?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you provide the data structure of the table(s) involved or is it just a general project?
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    id place various controls as required (whether thats list or combo boxes, date pickers...), plus another, a command button which applies or removes the 'filter', possibly another one to reset the controls to blank or known 'sane' default values

    you can apply the parameters as either a filter or opneing arguments, or as a query. that query can be saved int he querydefs collection if rerquired (but only do so if you are usign a split db, with seaprate front end (user interface) and back end (data), and each user has there one copy of the front end, otherwise there is a risk that you will overwrite queries.

    how you build the filters is up to you... effectively a filter is the same as a where clause, without using the word WHERE. there was a nice sampel of code sulied recently which avoids writing the filter / where cluase if you are suing the filter in the current form, but I cant find it.

    ...an approach is to build the clause on the fly using something like

    dim strWhereClause
    strWhereClause = "WHERE 1=1 "
    'if this is going to be a filter or passed as openign arguments on a form or report open macro then omit the WHERE
    ' the 1= 1 is an old SQL dodge that menas you will alwasy have a valid where clause
    if mynumericcombobox.value <> "" then 'checkl to see if the combo box has a value, ie has been selected
    strwhereclause = strwhereclause & " and mynumericcolumn = " & mynumericcombobox.value
    endif
    if mystringcombo .text <>"" then
    strwhereclause = strwhereclause & " and mystringcolumn = '" & mystringcombo.text & "'"
    endif
    if mydatelistbox.text <>"" then
    strwhereclause = strwhereclause & " and mydatecolumn = #" & format(mydatelistbox.text,"yyyy/mm/dd") & "#"
    endif


    if you want all values for that particular element you have two option
    either leave the combo/list/whatever blank OR provide an 'all' option. that does mean that you will have to populate the list/combo boxes yourself
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2014
    Posts
    27
    Thanks so much, I will try this

  5. #5
    Join Date
    Jun 2014
    Posts
    27

    Search problems

    I am making a form that searches through records using criteria and I figured out most of it but I have a section on credit rating and the system doesnt intuitively know the order of the ratings and which one is better, what can I do to make the system know what the ratings are from highest to lowest? Any help would be greatly appreciated.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Have a ratings table and in that define your sort sequence
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2014
    Posts
    27

    Reply

    I have one but how do I link that in with the search on the form that I created

  8. #8
    Join Date
    Jun 2014
    Posts
    27

    Having trouble with check boxes

    I have a bunch of locations and i have a list of companies and I have each location that the company is in checked off, I tried making check boxes for each location on the search form but, my form won't return anything when i check the boxes and search even though I wrote [forms]![Search Page]![Checkbox1] in the criteria in my search query. Does anyone know what I am doing wrong?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Not really....
    its hard to diagnose with code shown, its near 'kin impissible based on vague description.

    Its orobablr best to purge the db if sensitive data, compact and repair it then zip yhe file and pist it here as an attachment
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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