Results 1 to 5 of 5
  1. #1
    sspreyer is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Nov 2013
    Posts
    36

    How to filter if date is null by using a check box

    Hi all


    I have query which is filter by form only have 2 fields to filter field one is client which I filter by combo2 on my form have add this in the expression builder in my query works fine problem that I have got now is I have another field I need to filter called date start but I would like it so if checkbox "filter null" on my form is ticked to only show records that have no date in the field "date start" but if un ticked only shows records that have a date in the date start field

    Thanks in advance

    Shane

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    Other people may have a more elegant solution than this but in situations like this I have a column in the query driving my query results that's something like:

    Show: iif([forms]![FORMNAME]![CHECKBOXNAME] = -1 AND isnull(DATEFIELD), 1, iif([forms]![FORMNAME]![CHECKBOXNAME] = 0 and not isnull(DATEFIELD), 1, 0))

    and have a criteria on this field of 1

  3. #3
    sspreyer is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Nov 2013
    Posts
    36
    Thanks Rpeare
    still having problems getting to work do I need to put this code

    Show: iif([forms]![FORMNAME]![CHECKBOXNAME] = -1 AND isnull(DATEFIELD), 1, iif([forms]![FORMNAME]![CHECKBOXNAME] = 0 and not isnull(DATEFIELD), 1, 0))

    in criteria of the date field

    sorry if I'm being a bit slow

    thanks a lot for your time

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    No, you're creating a calculated field

    The result of that formula should correctly identify the records you want to show.

    You simply put 1 in the criteria after you verify it's giving you the results you want

  5. #5
    sspreyer is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Nov 2013
    Posts
    36
    Thanks Rpeare work great thank you ever so much for time

    Shane

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

Similar Threads

  1. Replies: 5
    Last Post: 11-02-2013, 04:27 PM
  2. Finding the Max Date and Null Values if Null
    By SpdRacerX in forum Queries
    Replies: 1
    Last Post: 02-03-2012, 06:29 AM
  3. Access VBA statement to check for Null entries
    By tim_tims33 in forum Access
    Replies: 4
    Last Post: 01-24-2012, 12:19 PM
  4. How to Check If Two Fields are Null?
    By alpinegroove in forum Programming
    Replies: 5
    Last Post: 01-04-2012, 01:41 PM
  5. Null Check
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 06-04-2010, 12:59 PM

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 - Senior Forums