Results 1 to 7 of 7
  1. #1
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    22

    How to tell a query if check box = true, then...


    Hi,

    I have a query that runs when I fill out the relevant criteria which is entered into a form.

    I want the query to filter results by only displaying those after today's date if a check box has been ticked on the form.

    I was trying 'IF Forms![SearchF]![Filter by Deadline?] = True THEN Date()' but I get the error that the expression I entered contains invalid syntax. '[Filter by Deadline?]' is the name of the check box in the form.

    How should I fix this?

    Many thanks!

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Which field holds the date you wish to compare to today’s date

  3. #3
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    22
    That is located at [tblDeadlines]![Deadline]

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Maybe change the onclick event of the chkbox?

    If me.filterbydeadline = -1 then
    Me.filter = “[deadline]>date()”
    Me.filteron=true
    Me.requery
    Else
    Me.filteron = false
    Me.requery
    End if

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Without seeing the SQL of the query, you might try the immediate if function "IIF()". You cannot use the function IF() in a query.


    BTW, [Filter by Deadline?] is a horrible field name. It has spaces and punctuation (the question mark).
    "[FilterByDeadline]" is a better name.

    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not use Look up FIELDS, multi-value fields or calculated fields in tables.
    Do not begin object names with a number.

  6. #6
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    22
    The SQL of the Query is:

    SELECT tblCompanies.Company, tblServicePrice.Service, tblServicePrice.Price, tblServicePrice.Currency, tblServicePrice.[Number of Issues], tblServicePrice.Comments, tblDeadlines.Deadline
    FROM (tblCompanies INNER JOIN tblServicePrice ON tblCompanies.ID = tblServicePrice.Company) INNER JOIN tblDeadlines ON tblCompanies.ID = tblDeadlines.Company
    WHERE (((tblServicePrice.Service)=Forms!SearchF!Service) And ((tblDeadlines.Deadline)>Date()) And ((tblServicePrice.Company)=Forms!SearchF!Company)) Or (((tblDeadlines.Deadline)>Date()) And ((Forms!SearchF!Service) Is Null) AND ((Forms!SearchF!Company) Is Null)) Or (((tblServicePrice.Service)=Forms!SearchF!Service) And ((tblDeadlines.Deadline)>Date()) And ((Forms!SearchF!Company) Is Null)) Or (((tblServicePrice.Company)=Forms!SearchF!Company) And ((tblDeadlines.Deadline)>Date()) And ((Forms!SearchF!Service) Is Null));

    Thanks for your advice, I have changed the name to '[DeadlineFilter]'. Would you be able to suggest how I might write the IIF function so that it will return results after today's date in the [tblDeadlines.Deadline] field if the check box is true?

    Thanks both!

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Disclaimer:
    I don't know what you are trying to do and don't know what your table structures are. I made up tables based on the query you provided.

    This may not be your table structure, but this is what the query suggests.
    If you have 1 record in "tblCompanies", 3 records in "tblServicePrice" and 3 records in "tblDeadlines" and create a query with no criteria (no WHERE clause), you will have 9 records, not 3. (This is a Cartesian join)

    I think you need to step back and revisit your tables structures and relationships.
    Click image for larger version. 

Name:	Stru1.png 
Views:	6 
Size:	26.8 KB 
ID:	31454



    So based on your query, this is the query I came up with. Not sure what you want if [Forms]![SearchF]![DeadlineFilter] is not checked.
    Code:
    SELECT tblCompanies.Company, tblServicePrice.Service, tblServicePrice.Price, tblServicePrice.CurrencyType, tblServicePrice.NumberOfIssues, tblServicePrice.Comments, tblDeadlines.Deadline, IIf([Forms]![SearchF]![DeadlineFilter]=True,Date(),#1/1/1900#) AS Expr1
    FROM (tblCompanies INNER JOIN tblDeadlines ON tblCompanies.ID = tblDeadlines.Company_FK) INNER JOIN tblServicePrice ON tblCompanies.ID = tblServicePrice.Company_FK
    WHERE (((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblDeadlines.Deadline)>IIf([Forms]![SearchF]![DeadlineFilter]=True,Date(),#1/1/1900#)) AND ((tblServicePrice.Company_FK)=[Forms]![SearchF]![Company])) OR (((tblDeadlines.Deadline)>IIf([Forms]![SearchF]![DeadlineFilter]=True,Date(),#1/1/1900#)) AND (([Forms]![SearchF]![Service]) Is Null) AND (([Forms]![SearchF]![Company]) Is Null)) OR (((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblDeadlines.Deadline)>IIf([Forms]![SearchF]![DeadlineFilter]=True,Date(),#1/1/1900#)) AND (([Forms]![SearchF]![Company]) Is Null)) OR (((tblDeadlines.Deadline)>IIf([Forms]![SearchF]![DeadlineFilter]=True,Date(),#1/1/1900#)) AND ((tblServicePrice.Company_FK)=[Forms]![SearchF]![Company]) AND (([Forms]![SearchF]![Service]) Is Null));

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

Similar Threads

  1. Replies: 41
    Last Post: 12-06-2016, 08:40 AM
  2. Replies: 7
    Last Post: 08-02-2016, 01:19 PM
  3. True/False Check Box Usage in Forms
    By avicknair in forum Access
    Replies: 3
    Last Post: 11-12-2015, 12:54 PM
  4. Replies: 8
    Last Post: 10-06-2014, 03:02 PM
  5. Replies: 4
    Last Post: 02-03-2014, 12:16 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