Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54

    Question Unanswered: User form- select criteria for query?

    Ok... I am writing a form for people who don't know how to use access which will enable them to select what information they would like to see for a mailing list. Basically, it is a gentle way of having them create a query without them having to create or modify a query. Make sense?

    Here is where I am stuck....

    I know how to make the criteria in the query as it relates to what is selected in the form (e.g., if 90078 is the selected zip code on a drop down on the form control, I can state in the criteria that the parameter comes from the value of that control on the form) but what i am totally baffled on, is how it can return all records (e.g., no parameter is selected) if the control is null.

    Am I missing something elementary??? Argh!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you're building the query in VBA, you can just include that in your logic. Otherwise:

    http://www.mvps.org/access/queries/qry0001.htm
    Paul

  3. #3
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54

    Yay

    Thank you so much... that is JUST what i needed!

    Rachel

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    I use a similar method in my databases to allow the users to query without SQL knowledge.

    One problem arose of people entering data in a field than "backspacing" the data out, leaving a zero length string. Since most users don't know the difference between null and zero-length, let alone how one is caused, this caused search problems.

    To solve this, on the event that triggers the search, I do something like
    PHP Code:
    if me.Txtbox "" then me.Txtbox dlookup("[FldInTable]","TableName",some criteria to never work
    This is the only way i can think of to assign a null value to a field using vb, just make sure the criteria will never find any results (probably not the best way to assign a null value, but it works).

    If someone has a better way of assigning null, plz let me know.

    HTH
    Last edited by nckdryr; 01-05-07 at 14:52.
    Me.Geek = True

  5. #5
    Join Date
    Jul 2003
    Posts
    16
    I prefer using IIf on these dynamic queries, especially if you have multiple options for the user. So, in the criteria of the query, I'd put the following...

    IIf(IsNull([Forms]![dlg-Dialog]![ZipCode]), [ZipCode], [Forms]![dlg-ZipCode]![ZipCode])

    So the its the condition (if the ZipCode is left null on the form), then the true part (display all Zip Codes), then false (display only the specified Zip Code).

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by nckdryr
    If someone has a better way of assigning null, plz let me know.
    How about:

    Me.TextBoxName = Null
    Paul

  7. #7
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54

    Exclamation sigh... stuck again

    thank you all for your wonderful advice, but now, alas, I am stuck.... *^)*^)*^)^ AGAIN.


    I took care of the "either query by what the value is in the text box or return all records" part but now i am having problem with a forced selection:

    The user has option to select the # of local of employees (or another field with the Amount of Gross Sales) and there are categories to chose from:
    1) >= 5 ees
    2) 6-10 ees
    3) etc. etc. etc.

    For some reason, I can't get the "Between 6 and 10" to move from the form to the query... ARGH... please see my code:

    Private Sub Command15_Click()
    On Error GoTo Err_Command15_Click
    Dim stDocName As String
    Dim strParameter As String


    If Me.Combo13 = "" Then
    strParameter = "*"
    Else
    strParameter = Me.Combo13
    End If

    stDocName = "qryMailingList"
    DoCmd.OpenQuery stDocName, acViewNormal, acReadOnly

    Exit_Command15_Click:
    Exit Sub

    Err_Command15_Click:
    MsgBox Err.Description
    Resume Exit_Command15_Click

    End Sub


    Why why WHY won't this work!!!

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by pbaldy
    How about:

    Me.TextBoxName = Null
    For some reason I thought you couldn't do it, but just tried it and it works, so...don't I feel sheepish.

    To get to your question Rachel, how is your query set up? Because the code shown doesn't look like it's passing any values to the query.
    Me.Geek = True

  9. #9
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54
    OK, so basically there are serveral fields that I am trying to allow the vaguely "retarded" (I use that in a loving way) user to be able to query a table to create a mailing list based on several criteria...

    Criteria One: NAICS Code (this is working thanks to my previous question!)
    Criteria Two: Zip Code (same for this one!)
    Criteria Three: # of Employees (^%&*%&*^%*&)
    Criteria Four: Total Local Sales (%^&%&^&*%)

    The last 2 aren't working. I have raw data that is all over the place, so i created "categories".

    Category 3 categories:
    >5 ees
    6-10 ees
    11-20 ees
    21-50 ees
    51-100 ees
    100+ ees

    and for Criteria 4:
    Less than 100K
    100K-500K
    Over 500K

    Now... my problem is passing the value of the combo box for the last two to the query

    Oh yes, and I should mention, that any comination of these criteria could be null and should return all values if that is the case.

    Rachel

    PS.. THANK YOU!!!!!

  10. #10
    Join Date
    Jul 2003
    Posts
    16
    Try using a SELECT CASE function

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Copied from http://www.dbforums.com/showthread.p...57#post6251457

    personaly i'd use a generated SQL statement

    Code:
    dim SQLstatement as string
    SQLstatement = "Select "
    If Me.Combo13 = "" Then
        SQLstatement = SQLstatement & "* "
    Else
        SQLstatement = SQLstatement & Me.Combo13 & " "
    End If
    SQLstatement = "From your table"
    
    if selected<>null then 
    SQLstatement = "Where "...
    endif
    
    SQLstatement = SQLstatement & ";"
    currentdb.CreateQueryDef("qryGenerated",SQLstatement)
    i wrote this on the fly so it's totally untested
    Definition of a Beginner, Someone who doesn't know the rules.

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

  12. #12
    Join Date
    May 2005
    Posts
    1,191
    Try using microsoft help on the CreateQueryDef action.

    Else, if you want a little more versitility, you could have two text boxes that the user types in the max and min value, then do something similar to above. Just make sure on the event that triggers the search, that you set textboxes to your max and min values if left empty
    PHP Code:
    me.minbox 0
    me
    .maxbox dmax("[FldName]","TableName"
    or something like that. Then just run a Between on these two boxes from your query, similar to what you did above. This just allows the user to define their min and max value instead of being forced to pick from a list. But if the list is what you need, then I would do something like m.timoney's post.
    Last edited by nckdryr; 01-10-07 at 09:53.
    Me.Geek = True

  13. #13
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54
    ok ding dong alert.... i am terrible at sql statements...

    Can you help explain the
    *Here*
    SQLstatement = "From your table"
    if selected<>null then
    SQLstatement = "Where "...
    endif
    *to Here*

    Part? Thanks so much!

  14. #14
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    okay SQL is easy you just need to get your head round it
    All select statements look like this

    Select Field1, Field2, etc
    From Table1
    Where Field1 = 2

    so the "From your table", will be for a table called supplier "From Supplier"
    as for the Where clause for that i suggest you build the query in Query builder, then view the SQL for that query and copy and paste it Access doesn't write good SQL but it will normally do the job.

    PS the "if selected<>null then" is a clause i added in case you wanted to show all records without a "Where" clause
    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
  •