Results 1 to 11 of 11
  1. #1
    rocknmoll is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    16

    Query to return current month in report

    I have a very simple query that is giving me real trouble. I have a table, "Invoicing" which records all data about individual invoices. Within this table, I have a field for date of issue.



    I want to run a query that shows all invoices in the current month, so when I run the associated report at any time, it will show me what has been invoiced for the current month.

    I have tried the Month(Now()) and Year(Now()) functions but I cannot get it to work. Some help with this simple question would be appreciated. I have searched all over and cannot find something else here that answers my exact question.

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a form, with 2 text boxes, txtStartDate, txtEndDAte
    in the query, it looks at the form for the range:

    select * from tInvoices where [invoiceDate] between forms!myForm!txtStartDate and forms!myForm!txtEndDate

  3. #3
    rocknmoll is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    16
    Thank you, but that seems a roundabout way of doing it. Surely it's just the syntax I am using in my query? Even if I need to add columns to help specify it down. I don't want to have to enter manual dates either, I want it to just pick up dates in current month.

    Quote Originally Posted by ranman256 View Post
    make a form, with 2 text boxes, txtStartDate, txtEndDAte
    in the query, it looks at the form for the range:

    select * from tInvoices where [invoiceDate] between forms!myForm!txtStartDate and forms!myForm!txtEndDate

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    this is versatile, the form allow for ANY dates,
    1 day, 1 month, 1 qtr, 1 year, etc


    a form could also make it simple point & click to choose a date, be it monthly, daily, etc..
    Click image for larger version. 

Name:	rpts ytd.jpg 
Views:	7 
Size:	45.5 KB 
ID:	36180

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Surely it's just the syntax I am using in my query?
    Maybe. We need to actually see the query to evaluate it.

  6. #6
    rocknmoll is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    16
    Here is my query which works fine and returns what I want it to, but I don't want to have to go in and change the dates each month.

    I have blacked out proprietary company info.

    Click image for larger version. 

Name:	query.PNG 
Views:	5 
Size:	37.8 KB 
ID:	36183

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Try something like this. It will request the two dates from user.
    Click image for larger version. 

Name:	tween.JPG 
Views:	6 
Size:	46.1 KB 
ID:	36184
    Last edited by davegri; 11-13-2018 at 09:12 AM. Reason: clarif

  8. #8
    rocknmoll is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    16
    Yes, this would work, but still requires the dates to be manually entered. That is what I am trying to avoid.

    Quote Originally Posted by davegri View Post
    Try something like this. It will request the two dates from user.
    Click image for larger version. 

Name:	tween.JPG 
Views:	6 
Size:	46.1 KB 
ID:	36184

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    That is what I am trying to avoid.
    OK, use this for the criteria.
    Code:
    Between (DateSerial(Year(Date()),Month(Date()),1)) And (DateSerial(Year(Date()),Month(Date())+1,0))

  10. #10
    rocknmoll is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    16
    I did this, it seems to work. Thanks for your help.

    Click image for larger version. 

Name:	querysol.PNG 
Views:	5 
Size:	38.5 KB 
ID:	36185

    Quote Originally Posted by davegri View Post
    OK, use this for the criteria.
    Code:
    Between (DateSerial(Year(Date()),Month(Date()),1)) And (DateSerial(Year(Date()),Month(Date())+1,0))

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Good thinking, pretty slick and straightforward. Keep on truckin'.

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

Similar Threads

  1. Replies: 8
    Last Post: 09-29-2015, 10:37 AM
  2. Replies: 6
    Last Post: 05-05-2014, 10:51 PM
  3. Access Query: Return Month Name and Year
    By PhatRam32 in forum Queries
    Replies: 7
    Last Post: 05-13-2013, 05:28 PM
  4. Replies: 5
    Last Post: 04-22-2013, 07:50 AM
  5. Replies: 0
    Last Post: 08-04-2009, 08:51 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