Results 1 to 10 of 10
  1. #1
    Join Date
    May 2014
    Posts
    5

    Unanswered: Access query from form parameter will not return null values

    I am entering my query parameters from a form that may contain null values. If null is entered I would like to return all records. I am using:

    IIf(IsNull([Forms]![Form Name]![Field Name]),[Table Name]![Field Name],[Forms]![Form Name]![Field Name])

    This works fine if I enter a parameter on the form but if I don't it will only return the records with a value that is not null.

    I did try:

    IIf(IsNull([Forms]![Form Name]![Field Name]),[Table Name]![Field Name] Or [Table Name]![Field Name] Is Null,[Forms]![Form Name]![Field Name])

    But I then receive a message that says: This expression is typed incorrectly or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Build 2 queries...

    Code:
    if isnull( txtBox) then
      docmd.openquery "qsNullVersion"
    else
      docmd.openquery "qsRegVersion"
    endif

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dont use parameters use a form to allow the user to select the elements they ant, then
    supply those paremters as part of the forfm/reports arguments in the openform/openreport macros
    or
    write the query to the querydefs collection
    or pull the parameters not the form/report as required

    why?
    effectively you want
    where userid = 'Gern' or isnull(userid)
    in parameters thats
    where userid = '[enter user id]' or isnull([enter user id])

    is too easy to screw something up
    putting it on a form also means you can validate the suer inpuit, and of offer users a choice from a list or combo box
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2014
    Posts
    5
    Quote Originally Posted by ranman256 View Post
    Build 2 queries...

    Code:
    if isnull( txtBox) then
      docmd.openquery "qsNullVersion"
    else
      docmd.openquery "qsRegVersion"
    endif
    That would be a great idea if there was only one parameter on the form. I will have several and the possible combinations would be mind numbing.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    In the query, replace the "=" operator with the "Like" operator:
    Code:
    WHERE TableName.ColumnName LIKE IIf(IsNull([Forms]![Form Name]![Field Name]),'*',[Forms]![Form Name]![Field Name])
    Have a nice day!

  6. #6
    Join Date
    May 2014
    Posts
    5
    Quote Originally Posted by healdem View Post
    dont use parameters use a form to allow the user to select the elements they ant, then
    supply those paremters as part of the forfm/reports arguments in the openform/openreport macros
    or
    write the query to the querydefs collection
    or pull the parameters not the form/report as required

    why?
    effectively you want
    where userid = 'Gern' or isnull(userid)
    in parameters thats
    where userid = '[enter user id]' or isnull([enter user id])

    is too easy to screw something up
    putting it on a form also means you can validate the suer inpuit, and of offer users a choice from a list or combo box
    No, if the user enters a parameter I only want to return that value not null values as well. If a user does not enter a value for a parameter I want to return the entire recordset including null.

  7. #7
    Join Date
    May 2014
    Posts
    5
    Quote Originally Posted by Sinndho View Post
    In the query, replace the "=" operator with the "Like" operator:
    Code:
    WHERE TableName.ColumnName LIKE IIf(IsNull([Forms]![Form Name]![Field Name]),'*',[Forms]![Form Name]![Field Name])
    That still did not return my null value.

  8. #8
    Join Date
    May 2014
    Posts
    5
    Quote Originally Posted by healdem View Post
    dont use parameters use a form to allow the user to select the elements they ant, then
    supply those paremters as part of the forfm/reports arguments in the openform/openreport macros
    or
    write the query to the querydefs collection
    or pull the parameters not the form/report as required

    why?
    effectively you want
    where userid = 'Gern' or isnull(userid)
    in parameters thats
    where userid = '[enter user id]' or isnull([enter user id])

    is too easy to screw something up
    putting it on a form also means you can validate the suer inpuit, and of offer users a choice from a list or combo box
    No, If a user enters a parameter I only want to return those records not null as well. If the parameter is null I want to return all records including null.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    doesn't alter the resolution
    for this sort of thing you have to add processing loigic
    either as a query (write to the querydefs collection)
    or pass values as required as parto fot he form/reports docmd macro
    or pull the values into the form/report as it opens.

    in my experience users are very flaky when entering parameters. SO personbally I don't use 'em. I always use two forms that handle the user inmjput


    one form acts as the holder of the current parameters
    another implements logic checking, validation, making certain the user selects sane values, which are passed back to the holding form
    from the holding form the user can the select whatever forms/reports they need with a consistent set of parameters

    I don't allow users to enter parameters on the fly, especailly in reporting suites as its way to easy to feck something up between diffferent reports as users input what should be the same parameter
    the more opportunity you allow your users to screw something up, the more they will. forcing 'em to use a form, espcailly one that forcesd 'em to use sane values AND validating those selections saves a lot of heartache, fingerpointing and blamestorming. its no different to the approach of using list of compbo boxes. the users are constrained to select know good values
    Last edited by healdem; 05-29-14 at 11:07.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You can always buid a dynamic query.
    Have a nice day!

Posting Permissions

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