Results 1 to 9 of 9
  1. #1
    adjohn327 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    4

    Query criteria based on Multiple Check Boxes in a form

    I have designed a database to retain information on Customers I have contacted. I am attempting to design a form that will generate a report (based on a query) that is filtered by a date range and Multiple checkboxes. I was able to get the date range to work efficiently, however, I am having trouble with the Check boxes. There are 5 checkboxes total (defined by the "Status" of a call I.E. No Answer, Wrong number, etc.).
    In design view of the Query I have IIf([Forms]![FrmInstallsCB]![CBMovedUp]=-1,"Moved Up","") in the Criteria. When I use the form I get an error "This expression is typed incorrectly, or it is too complex to be evaluated.



    SQL
    SELECT TblInstalls.[CONT DATE], TblInstalls.[SCH DATE], TblInstalls.AREA, TblInstalls.[CUSTOMER #], TblInstalls.CITY, TblInstalls.[#], TblInstalls.STATUS, TblInstalls.COMMENTS
    FROM TblInstalls
    WHERE (((TblInstalls.[CONT DATE]) Between [Forms]![FrmInstallsCB]![TxtStrtDate] And [Forms]![FrmInstallsCB]![TxtEndDate]) AND ((TblInstalls.STATUS)=IIf([Forms]![FrmInstallsCB]![CBMovedUp]=-1,"Moved Up","0")));

    I am relatively new to Access.
    Can anyone tell me what I am doing incorrectly?

    Thank you,

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    as a test - remove the final AND from your WHERE statement as I'm not so sure about an IIF in a where statement....try just your date range

    in plain English what is the idea behind the iif ?

  3. #3
    adjohn327 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    4
    The idea behind the iif is to check if a check box is checked or not (there will be a total of 5 check boxes) and filter the data according to the check boxes. Maybe I am going about this the wrong way.
    My ultimate plan: I have a table with a field "status" that includes 5 possible entries. I want the form to enable a user to filter the table data in a report by a date range and one or many of the 5 statuses.

    The report runs fine with just the date range.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    try substituting -1 with True or Yes, which are typical values for checkboxes . I think -1, 0, 1 are for vba and not sql.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    adjohn327 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    4
    I tried both Yes and True, neither of them worked. When I put Like in front of the statement ((TblInstalls.STATUS) Like IIf([Forms]![FrmInstallsCB]![CBMovedUp]=Yes,"Moved Up",""))), the error goes away. However, it doesn't pull any data at all.

  6. #6
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    Make your query with just the date range. Call that Q1Date - and be sure it returns the correct results.

    Then you will make a second query using the Q1Date as the starting record set. Can more than 1 check box be checked? or is it an Option List where only 1 can be checked?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by adjohn327 View Post
    I tried both Yes and True, neither of them worked. When I put Like in front of the statement ((TblInstalls.STATUS) Like IIf([Forms]![FrmInstallsCB]![CBMovedUp]=Yes,"Moved Up",""))), the error goes away. However, it doesn't pull any data at all.
    you didn't use the wildcard * before or after the criteria
    Like *IIf([Forms]![FrmInstallsCB]![CBMovedUp]=Yes,'Moved Up',""))),*

    try creating the query in design view using LIKE * and it should guide you. OR use the common technique of debug.print your statement in the immediate window and coping/pasting to a new query and running it from there. It will often pinpoint the problem for you.

    In vba you might have to concatenate it.
    Like * '" & IIf([Forms]![FrmInstallsCB]![CBMovedUp]=Yes,"Moved Up",""))) & "',*....."
    Building separate queries for the individual parts sometimes reveals what is the problem with one of them when you have tried to do it in one step at the beginning.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02....

    SELECT TblInstalls.[CONT DATE], TblInstalls.[SCH DATE], TblInstalls.AREA, TblInstalls.[CUSTOMER #], TblInstalls.CITY, TblInstalls.[#], TblInstalls.STATUS,
    Object names in Access should be letters and numbers, NO spaces, punctuation or special characters (exception is the underscore).
    You have spaces in field names and have use the hash sign (which is a date delimiter) in field names.
    What really amazes me is that you have a field name that is just the hash sign ([#]). I didn't think Access would allow that to happen!


    What is the data type of the field "Status"? Text??
    I am struggling to understand the relationship between the check boxes and the field "Status".
    Can you have more than 1 check box checked? I wouldn't expect that the "Status" field would store more that one entry.
    Can you have the "Status" field = "No Answer" and have the "Moved up" check box checked?

    Couldn't you add an unbound combo box on the form "FrmInstallsCB" to select a status for use as a criteria?
    Code:
    WHERE STATUS = [Forms]![FrmInstallsCB]![cboStatus] AND [CONT DATE] Between [Forms]![FrmInstallsCB]![TxtStrtDate] And [Forms]![FrmInstallsCB]![TxtEndDate];

  9. #9
    adjohn327 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    4
    All,
    Thank you for all of your help and suggestions. I have had no success with this and am just going to follow ssanfu's idea with the combo box. My idea was to enable a user to select multiple fields, but I don't think it's worth the trouble. So they will just have to deal with a combo box.

    Thank you All!

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

Similar Threads

  1. Print specific report based on form check boxes
    By Eddy Sincere in forum Reports
    Replies: 3
    Last Post: 05-13-2015, 08:07 PM
  2. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  3. Search records based on multiple check boxes
    By maytricks in forum Access
    Replies: 4
    Last Post: 01-14-2013, 07:26 PM
  4. Filter Form with Multiple Check Boxes
    By Njliven in forum Forms
    Replies: 8
    Last Post: 01-09-2013, 01:50 PM
  5. Replies: 5
    Last Post: 11-15-2012, 03:33 PM

Tags for this Thread

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