Results 1 to 4 of 4
  1. #1
    enilc is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    14

    Null parameter as wildcard

    Hello

    I have a fairly standard table that includes peoples' dates-of-birth. I'm creating a query that will allow people to enter a month and day-of-month to get a list of people with that birthday.

    People are in the table as a couple, so I'm actually looking for a list of if either person in the relationship has a birthday on a specified day.

    This works so far:

    PARAMETERS [Month Number] Short, [Day Number] Short;
    SELECT NEWrelationships.RelationshipName, NEWrelationships.FName1, NEWrelationships.LName1, NEWrelationships.DOB1, NEWrelationships.FName2, NEWrelationships.LName2, NEWrelationships.DOB2
    FROM NEWrelationships
    WHERE (((Month([DOB1]))=([Month Number])) AND ((Day([DOB1]))=([Day Number]))) OR (((Month([DOB2]))=([Month Number])) AND ((Day([DOB2]))=([Day Number])));


    I would like to be able to get the list of birthdays for the entire month by using the same query. To that end, I thought user could leave the [Day Number] parameter blank and I could use IIF, IS Null, and a wildcard to make it work like this:

    PARAMETERS [Month Number] Short, [Day Number] Short;SELECT NEWrelationships.RelationshipName, NEWrelationships.FName1, NEWrelationships.LName1, NEWrelationships.DOB1, NEWrelationships.FName2, NEWrelationships.LName2, NEWrelationships.DOB2
    FROM NEWrelationships
    WHERE (((Month([DOB1]))=([Month Number])) AND ((Day([DOB1]))=IIf(IsNull([Day Number]),'Like *',[Day Number]))) OR (((Month([DOB2]))=([Month Number])) AND ((Day([DOB2]))=IIf(IsNull([Day Number]),'Like *',[Day Number])));

    If I enter a [Day Number] it gives me the correct list. If I leave [Day Number] blank, it returns an empty set (no error). I've tried using 'Like *' (as shown) as well as just *. I've also tried with and without single/double quotes. Can't seem to get it to work.



    I appreciate any assistance with this.

    TIA
    Steve

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to use is null and you can't build a criteria like that

    try

    WHERE (Month([DOB1])=[Month Number] AND (Day([DOB1])=[Day Number] OR [Day Number] is null)) OR (Month([DOB2])=[Month Number] AND (Day([DOB2])=[Day Number] OR [Day Number] is null))

    I've removed the extraneous brackets, if you are using the query grid, copy the bit in red to the same criteria line as [Month Number]

    Not sure what the Short datatype is - sounds like it might be text

    Recommend don't use spaces in field names (or parameters in this case)

    And sounds like your table is not properly constructed. I would expect a table for people and another for relationships. What happens if the couple splits or change partners?




  3. #3
    enilc is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    14
    Perfect Ajax. That does exactly what I needed.

    Your observations are spot-on. I don't normally use the spaces, but was in a hurry this morning. This was originally going to be a one-off query, but now that you've helped me increase it's functionality, I'll make corrections.

    'Short' is a text datatype, for use when field is <255 char. Those were originally setup as int. Not sure how it changed in the SQL.

    With regard to the table setup. I cringed and hesitated A LOT before posting this, as I knew I was airing some 'dirty laundry' in that regard. I originally had this setup as you describe, with individuals in one table and linked via a relationship key. However, people above me who sign my paycheck (who took an Access class in college 20 years ago) told me he wanted it this way. I fought the fight and spent some time demonstrating why the 'normal' way is better, but he insisted and I relented. This particular db is particularly cringeworthy if you saw how some of the Forms and Tables are setup. It's a bit of a house of cards and it's definitely one of those dbs that give Access a bad name. But I'm making it work for now.

    I really appreciate your help on this.

    Steve

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    good luck - if the app is still under development, expect further problems along the way

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 12-05-2013, 11:48 AM
  2. Replies: 2
    Last Post: 04-04-2013, 03:13 PM
  3. Replies: 6
    Last Post: 03-18-2013, 11:35 AM
  4. bypassing passing parameter when null
    By cowboy in forum Queries
    Replies: 11
    Last Post: 04-14-2010, 09:59 PM
  5. Parameter Query & Wildcard
    By Rick West in forum Queries
    Replies: 8
    Last Post: 12-29-2009, 10:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums