Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts

    query only dates of current month

    friends,

    I am having difficulty performing a query that is simple, but I can not.

    I have a table with a date / time field, I'm trying to get only the records of the current month. For example, if the
    current month is October: only those records in October. When we are in November: only records
    November ...

    Have any function I can use?

    Thanks for helping.
    Last edited by fabiobarreto10; 2012-10-08 at 12:33.

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    Probably the use of function Month to get the month of the dates from the records you are querying and the use of Month(Now()) to get the current month can be used to build the WHERE clause for the query.

    Of course, you also will need the function Year used in the same way, to make sure you just select the records for the current year.

    Is this what you were asking about?


    P.S: Edited to add the year part.

  3. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-10-09)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Fabio,

    Here's an Access query that does the trick.

    Here's the generated SQL:
    Code:
    SELECT Hospital.FName, Hospital.LName, Hospital.DeceasedDte
    FROM Hospital
    WHERE (((Hospital.DeceasedDte)>=DateValue(Month(Now()) & "/1/" & Year(Now()))) AND ((Hospital.DeceasedDte)<DateValue(Month(Now())+1 & "/1/" & Year(Now()))-1));
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    fabiobarreto10 (2012-10-09)

  6. #4
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    rui and retiredGeek, thanks for help.

    rui, I had managed to get the records from the current month, but you're right, I also need the current year. I created an extra field in the query: PurchaseMonth: Month ([LogData]) to the following criteria:
    Month (Now ())

    Returns records correctly. I tried adding the criterion and year (now ()), but it did not work.



    retiredGeek, I did exactly as you sent me, but did not return records.

    Even tried to get the same data fields and table Hospital, as in the example, but did not return the record as the picture you sent. I made a bench with my only problem is:

    windows.secrets.forum@gmail.com

    password is:

    windows2012


    is in the inbox.

    thank you.

  7. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Fabio,

    I downloaded your .accdb and imported the query from my test data base and it worked just fine.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    fabiobarreto10 (2012-10-09)

  9. #6
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    You ran the query named "consulta1". I do not understand because they do not want to work with me.

  10. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Fabio,

    No, I imported the query from my test database.
    Your Consulta1 query had a 2 for the day instead of 1 in both calculations!

    If you change that it will work properly.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. The Following User Says Thank You to RetiredGeek For This Useful Post:

    fabiobarreto10 (2012-10-10)

  12. #8
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    RetiredGeek,

    I put the number two (2) because I was making attempts. But now I put one (1) again, but did not return any records. see the attached image. My access is in Portuguese. When I type in English on the criteria, it automatically translates into Portuguese.

    MÍs = Month
    Agora = Now
    Ano = Year

    the image is in the e-mail sent earlier, I am not able to send at forum

  13. #9
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Rui and RetiredGeek,

    I managed to solve my problem doing a subquery of the query.

    Month ([LogData]) to the Following criteria:
    Month (Now ())

    I made another appointment after that first consultation subtituindo Month by Year.

    I think you could make a single query, but as he was not getting ...
    The important thing that I managed to solve the problem.

    Rui and RetiredGeek, thank you so very much.

  14. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Fabio,

    Ah European dates! I guess the start and end dates should have been constructed as dd/mm/yy vs the USA mm/dd/yy.
    I don't have any experience using the functions with European date format but it seems a likely culprit.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. The Following User Says Thank You to RetiredGeek For This Useful Post:

    fabiobarreto10 (2012-10-10)

  16. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    How about this, if the date field does not have a time component:
    ...WHERE Hospital.DeceasedDt Between (Date-Day(Date)+1) AND DateAdd("m",1,(Date-Day(Date)))

    Or this if it does have a time component:
    ...WHERE Hospital.DeceasedDt >= (Date-Day(Date)+1) AND Hospital.DeceasedDte < DateAdd("m",1,(Date-Day(Date)+1))
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  17. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    fabiobarreto10 (2012-10-10)

  18. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    or you could try:
    WHERE Year(Hospital.DeceasedDt) = Year(Date) AND Month(Hospital.DeceasedDt) = Month(Date)

  19. The Following User Says Thank You to patt For This Useful Post:

    fabiobarreto10 (2012-10-10)

  20. #13
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    Date formats are always such a pain. I am glad you solved it, Fabio.

  21. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-10-10)

  22. #14
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Patt, your tip worked perfectly. I was not wanting to work, just missing put "()" after Date. I appreciate your help, now I just use a query.

    Thank you.

  23. #15
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    MarkLiquorman, their condition also worked perfectly. Just had to add "()" after Date. Maybe this is the version of access or language. If I do not put "()" after Date, does not work. I appreciate your help.

    Thank you.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •