Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jul 2005
    Location
    Lancaster, Delaware, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Criteria (Access 2000/ Windows XP)

    I have a question regarding query criteria. I have a parameterized query which gets its parameters from a form's combo boxes. The code in the query criteria is as follows:

    Like [Forms]![frmName]![cboName] & "*" OR Like [Forms]![frmName]![cboName] Is Null (Where Name is the name of the form and the combo box).

    Now this works great except I have a field that stores numeric values and when I pass a parameter of say "10" I also recieve "100","1000" etc. So I changed the query criteria to the following:

    Like Forms!frmName!cboName (omtting the & "*").

    And I no longer get the other undesirable values but when I select a parameter for a separate field in the query the query returns nothing. Now when I return the criteria in the query to the previous line of code it works great with the exception that I still get "100","1000" when I only asked for "10" Any help will be greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Criteria (Access 2000/ Windows XP)

    Try the following in the criteria line:

    [Forms]![frmName]![cboName] OR [Forms]![frmName]![cboName] Is Null

    (Like isn't used at all)

  3. #3
    Lounger
    Join Date
    Jul 2005
    Location
    Lancaster, Delaware, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (Access 2000/ Windows XP)

    Mr. Hans,
    I appreciate the prompt reply. When I try your suggestion it causes the query to output nothing, but when I put LIKE back in front of the criteria expression(s) it works great ( except of course I still get 100, 1000 when all I want is 10). Again an suggestions are greatly appreciated. Also I have to put LIKE in front of all the criteria for each combo box. If I say have all the fields that accept a parameter from my form and ALL of these fields DO NOT have the LIKE operator the query will not perform. As soon as I put the LIKE operator back into the expression it works as well as before.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Criteria (Access 2000/ Windows XP)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Lounger
    Join Date
    Jul 2005
    Location
    Lancaster, Delaware, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (Access 2000/ Windows XP)

    Hans,
    I am having a hell of a time. I have eliminated all excess data(tables,queries,modules,macros,etc) but the resultant file is still too big. Is there somewhere else I can possibly send this file to. Thankx Hans.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Criteria (Access 2000/ Windows XP)

    Create a blank new database, and import only the tables, queries and form that are relevant to the problem. Then compact it, and zip it.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (Access 2000/ Windows XP)

    Try this in your query criteria:

    IIf(IsNull([forms]![frmName]![cboName]),[FIELDNAME],[forms]![frmName]![cboName])
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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