Results 1 to 3 of 3
  1. #1
    matechik4 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    6

    Combining Like, IIf, and DatePart as query criteria

    Hi All,



    I am working on a database of work orders and recurring tasks. My boss has requested a from with combo boxes for Division, Point of Contact (POC), Month and Year that are then used as inputs for a query. The idea is he can quickly generate custom reports for whatever Division, POC and date that he wants. There is one caveat that is complicating my query. I also included a choice called "<NA>" in each combo box. That way, my boss can leave that box blank to see all the records in that category (i.e. selecting "<NA>" for the month combo box would mean that the query would select records in any month).

    All of the tasks and work orders are stored in a query called [qryEventDates]. I am trying to select the tasks based on their due date, called [EventDate] in the query.

    The combo boxes are in a form called [Custom Report] and are named:
    [ComboDiv]
    [ComboPOC]
    [ComboMonth]
    [ComboYear]

    I realized that I could use Like and IIf together to write query criteria that would select the records based on what was selected in the combo box and return all records for that field if "<NA>" is selected. The following expression worked for the Division Field.
    Like IIf([Forms]![Custom Report]![ComboDiv]="<NA>",[qryEventDates]![Division],[Forms]![Custom Report]![ComboDiv])
    Next, I thought I would apply a similar criterion for the month and year combo boxes with the addition of a DatePart function. My thought was to include the field [qryEventDates]![EventDate] in the query twice and use one to select based on the month and the other to select based on year. (I supposes I could combine them into one field and use an AND statment). I came up with this expression below for the year combo box, but I am having trouble with it. Can anybody help me out? Should I try a different approach? If I can get this one, the criteria for month should be very similar.

    Like IIf([Forms]![Custom Report]![ComboYear]="<NA>",[qryEventDates]![EventDate],DatePart("yyyy",[qryEventDates]![EventDate])=[Forms]![Custom Report]![ComboYear])
    Thanks for your time.

  2. #2
    matechik4 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    6
    In case anybody was wondering, I was able to get around this problem by taking a different approach. I bet there is a way to code it like I originally asked but it was too complicated for me. Instead, I replaced the month and year combo boxes with two date boxes and let the user pick a range of dates. It is one extra step for the user but it also makes the report generator more flexible. The idea came from this forum. http://stackoverflow.com/questions/2...in-the-followi

    Here is the SQL code I used.
    SELECT qryEventDates.Task, qryEventDates.Canceled, qryEventDates.POC, qryEventDates.Division, [qryEventDates]![SAID] AS [Individual Action ID], qryEventDates.EventDate
    FROM qryEventDates
    WHERE (((qryEventDates.EventDate) Between [Forms]![Custom Report]![StartDate] And [Forms]![Custom Report]![EndDate])) OR ((([Forms]![Custom Report]![StartDate]) Is Null)) OR ((([Forms]![Custom Report]![EndDate]) Is Null))
    GROUP BY qryEventDates.Task, qryEventDates.Canceled, qryEventDates.POC, qryEventDates.Division, [qryEventDates]![SAID], qryEventDates.EventDate;

    Interestingly enough, Access couldn't handle it when this expression was used with my Like IIf statements in my original post. To fix that I just used two queries. The first selected based on the date using my SQL code and then the second used those results and selected based Division and POC using on my Like IIf statements.

    Anyway, I'm glad I found a solution. Hopefully this will be useful to somebody at some point.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Thanks for posting your solution. It may help someone else...

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

Similar Threads

  1. Me.filter with datepart from 2 combo boxes?
    By wackywoo105 in forum Programming
    Replies: 3
    Last Post: 03-15-2014, 06:36 PM
  2. Replies: 10
    Last Post: 01-07-2014, 04:03 PM
  3. datepart in sql string
    By Dannasoft in forum Queries
    Replies: 1
    Last Post: 12-19-2012, 04:52 PM
  4. Replies: 5
    Last Post: 09-20-2012, 03:27 PM
  5. iff(datepart..
    By pranvera in forum Access
    Replies: 4
    Last Post: 11-19-2010, 11:38 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