Results 1 to 9 of 9
  1. #1
    joflow21 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    13

    Enter criteria in a qry between the date entered in the text box of a frm & 12 mos b4

    Hi,



    I am trying to enter a formula for the criteria in a query that would be between the date entered in the text box of a form & 12 months prior to that date (returning the 1st day of the month). For example, I currently have Between #1/1/2013# And #12/31/2013#, but I need it to use the text box of my form. The text box will have the end date, but I need the criteria in the query to look for records that are between 12 months prior to the date in the text box (returning the 1st day of the month) and the date in the text box.

    I am hoping that I have asked the question correctly, but can someone please help?

    Thanks,
    Joflow21

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Along the lines of

    Between DateAdd("m", -12, Forms!FormName.TextboxName) And Forms!FormName.TextboxName
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    If you want the first day of the 12-months-ago month, this might work:
    Code:
    WHERE [MyTable].[MyDate] 
    BETWEEN DateSerial((Year(Forms!MyForm!MyTextDate) - 1, Month(Forms!MyForm!MyTextDate), 01)) 
    AND Forms!MyForm!MyTextDate
    You will, of course, have to update table, form, and field names.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I missed the first day of the month part.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    joflow21 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    13
    Thank you pbaldy. I simply changed the "m" to "d" and -12 to -364, and I got exactly what I needed. Thank you so much for your help!

  6. #6
    joflow21 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    13
    Thank you Dal, for your reply. I tried this and got a message saying "the expression I entered has a function containing the wrong number of arguments. I copied and pasted it and just changed the names of the table, form and text box, but I can't see where the issue is.

    I was able to get what I needed, so if you don't want to look at it further, that is okay. Thank you.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help, though that method won't go back to the first of the month.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    joflow21 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    13
    Not a problem. I realized that using 364 days was more accurate, for what I needed, than 12 months and the 1st day of the month. Thanks.

  9. #9
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Good enough. Marking thread Solved.

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

Similar Threads

  1. Replies: 9
    Last Post: 05-22-2012, 01:28 PM
  2. Replies: 1
    Last Post: 01-27-2012, 02:03 PM
  3. Replies: 1
    Last Post: 12-12-2011, 06:32 AM
  4. Replies: 5
    Last Post: 02-06-2011, 04:32 PM
  5. Using value entered as query criteria
    By morganba in forum Access
    Replies: 3
    Last Post: 08-11-2010, 05:52 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