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

    Trying to Select Dates by Month and Year

    Hi,



    I am new to access so this may be a simple question but I appreciate your help.

    I am running a query to select tasks that have to be completed in a given month during a specific year. I want the users to be able to use a form I created to select the month and year and then have the query use the months and year from the form. I can get the query to select all the tasks based on the month or select all of the tasks based on the year, but it yields no results when I use an And statement to combine the two expressions. Here is what I have so far:

    A query called [qryEventDates] that has all the tasks and their corresponding due dates. The due date field is called [EventDate] and is in date and time format.
    A form called [Report by POC] that lets the user select the month and year. The form has a combo box for month called [ComboMonth_RBP] and a text box called [YearBox]

    If I enter this expression in my query...
    (DatePart("m",[qryEventDates]![EventDate])=[Forms]![Report by POC]![ComboMonth_RBP])

    it selects all the tasks due by the month on the form. This makes sense.

    If I enter this expression...
    (DatePart("yyyy",[qryEventDates]![EventDate])=[Forms]![Report by POC]![YearBox])

    it selects all the tasks due during the year specified on the form. This also makes sense.

    However, if I enter this expression...

    (DatePart("m",[qryEventDates]![EventDate])=[Forms]![Report by POC]![ComboMonth_RBP]) And (DatePart("yyyy",[qryEventDates]![EventDate])=[Forms]![Report by POC]![YearBox])

    It selects nothing, even when there are tasks due in that given month and year.

    Does anybody know what could be causing this? Should I be using a different approach? I appreciate any advice you can offer. Thanks for your time.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure it makes a difference if it is working individually, but you should use a . for the table/field separator rather than a !

    [qryEventDates].[EventDate]

    also try

    Format([qryEventDates].[EventDate],"yyyymm")=[Forms]![Report by POC]![YearBox] & [Forms]![Report by POC]![ComboMonth_RBP]

  3. #3
    matechik4 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    6
    It worked! Thank you. I'm just beginning to get into to Access but I like it so far. I appreciate all of you who have contributed to forums and made instructional videos. It is really helping me out.

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

Similar Threads

  1. Replies: 4
    Last Post: 07-13-2015, 02:06 PM
  2. Replies: 2
    Last Post: 04-15-2014, 08:43 AM
  3. Select Month from previous selected year
    By k0enf0rNL in forum Access
    Replies: 1
    Last Post: 01-15-2014, 12:14 PM
  4. Replies: 4
    Last Post: 05-26-2013, 03:28 PM
  5. Replies: 2
    Last Post: 02-11-2013, 12:05 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