Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    41

    Unanswered: Criteria to select one or all records

    I'm using [Item Number?] in the criteria of a query. If, at the prompt, the user simply presses Enter, what is returned? Null?
    Is there an easy way to select all records?

    For example, what I've tried is:

    Field:ItemNumber
    .
    .
    Criteria: [Item Number?] Or IIf([Item Number?] Is Null,Is Not Null,"XX")

    "XX" is disallowed via the data entry form, so it should never appear.

  2. #2
    Join Date
    Jul 2004
    Posts
    64
    If the item number doesn't vary in length, you could use:

    LIKE "*" & [Item Number?] & "*"

    The only problem with this would be if you have the following:

    123

    1234

    if you want ONLY 123, this will not work, as it will return BOTH 123 and 1234

  3. #3
    Join Date
    Mar 2004
    Posts
    41
    nope. More than one throws off the query that follows.

  4. #4
    Join Date
    Jul 2004
    Posts
    64
    So what do you want returned?

    If the item number is null, return all the records? If so, you should be able to:
    Nz([Item Number?],"*")

  5. #5
    Join Date
    Mar 2004
    Posts
    41
    No luck with that one, either.
    [Item Number?] is actually a Text field. If the user enters a string, only exact matches should be returned. If left blank, all records should be returned.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    try using nz() instead.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Mar 2004
    Posts
    41
    Forgive me - I'm dense. Isn't that what Bbender27 suggested.. or is there a different way to apply it, like using it to replace the Is Null in my original Criteria?

  8. #8
    Join Date
    Jul 2004
    Posts
    64
    Well, you need to reference it as a text field using Forms![Name of form]![Item Number?]
    What sort of error did you get when you tried using NZ?

  9. #9
    Join Date
    Jul 2004
    Posts
    64
    Actually try this instead:

    [Forms]![Form Name]![Item Number?] Or Like "*"

  10. #10
    Join Date
    Mar 2004
    Posts
    41
    ...maybe if you see what I'm seeing, this will make more sense to me.
    This is where I started.

    I replaced the Criteria with: Nz([Item Number?],"*")
    This returned 0 records, no error message
    Attached Thumbnails Attached Thumbnails query.jpg  

  11. #11
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    There are 2 methods to solve this.

    1) Select count(*) from a where name = NZ([param], name)
    2) Select count(*) from a where IIF([param] IS NULL, 1=1, name = [param])
    Attached Files Attached Files
    Last edited by r123456; 07-22-04 at 13:37.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  12. #12
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Just had a quck look at this and I think this will work assumming tha ITEM NUMBER is numeric and will always be > 0

    IIf(Not IsNull([Item Number?]),[Item Number?]) Or >IIf(IsNull([Item Number?]),0)

    I have used IIF in both cases and ignored the false option in both.

    I tried =IIf(Not IsNull([Item No?]),[Item No?],>0) but false option only work with a number but not with a > comparitor. The only way I could get it to work is to have the > outside the IIF.

    HTH

    MTB

Posting Permissions

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