Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    IsaacRCCL is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    10

    Need Help with DateAdd (range)

    I have a database with Clients Names (Field1) , Programs (Field2), and Dates (Field3)



    I have to search for all Clients that have a date range of current date + 1 year. But i need the entire month of clients to display.

    So if I'm searching today, I need it to give me all the clients that have a date in between 01/01/2021 - 01/31/2021. 1 year in the future of the current date but the entire month.

    Thank you

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    try
    Code:
    BETWEEN Dateserial(Year(date),month(date),01) AND DateAdd("d", -1, CDate(Format(DateAdd("m", 1, Date()), "m/1/yyyy")))
    I tried to use DateSerial in both halves but it got too confusing.

    EDIT - expression corrected - copied wrong expression from my immediate window.
    You could write 2 functions if this is for a query, and call each one from there. One for begin date, one for end date; rather than have this on one query field.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    IsaacRCCL is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    10

    Doesnt Work

    Quote Originally Posted by Micron View Post
    try
    Code:
    BETWEEN Dateserial(Year(date),month(date),01) AND DateAdd("d", -1, CDate(Format(DateAdd("m", 1, Date()), "m/1/yyyy")))
    I tried to use DateSerial in both halves but it got too confusing.

    EDIT - expression corrected - copied wrong expression from my immediate window.
    You could write 2 functions if this is for a query, and call each one from there. One for begin date, one for end date; rather than have this on one query field.
    Doesnt work for me. Am I adding this to the Criteria in the query?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry, thought I tested that well enough. Review the following:
    Code:
    ?DateAdd("yyyy",1,Dateserial(Year(date),month(date),01))
    result: 1/01/21

    Code:
    ?dateadd("m",1,DateAdd("yyyy",1,Dateserial(Year(date),month(date),01)))-1
    result: 1/31/21
    so
    Code:
    Between DateAdd("yyyy",1,Dateserial(Year(date),month(date),01)) AND dateadd("m",1,DateAdd("yyyy",1,Dateserial(Year(date),month(date),01)))-1
    Starting to look messy. If you were doing this in code or via a form it would look simpler. As a query, yes, you'd have to put that as criteria. You could also call a function from the query via a calculated field, such as MyDate: NameOfTheFunction(nameOfYourDateField)
    Last edited by Micron; 01-17-2020 at 01:58 PM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    IsaacRCCL is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    10
    I dont know what I'm doing wrong. Field3 is a Date/Time field. When I put that code in the Criteria I get the following message: Undefined 'DateAdd' in expression. What should I be doing?

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    DateAdd function dates back to at least version 2007, so here's some things to look at
    - missing or broken references in vb editor (Tools>References)
    - for your version, likely a reference to vba (Visual Basic for Applications; I think version 6); pic of your references dialog box is OK
    - copy & paste your attempt at the expression/code (always a good idea to post what you've tried so that syntax can be looked at) but not as a picture
    - post a zipped copy of your db for analysis

    I wish there was a source for relating functions to references (these are "libraries" needed by functions/code) but I have never found one so I'm not 100% sure which one covers DateAdd. Maybe I can find something...

    EDIT - is something other than Access involved here? mySql or something else?

  7. #7
    IsaacRCCL is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    10
    Quote Originally Posted by Micron View Post
    DateAdd function dates back to at least version 2007, so here's some things to look at
    - missing or broken references in vb editor (Tools>References)
    - for your version, likely a reference to vba (Visual Basic for Applications; I think version 6); pic of your references dialog box is OK
    - copy & paste your attempt at the expression/code (always a good idea to post what you've tried so that syntax can be looked at) but not as a picture
    - post a zipped copy of your db for analysis

    I wish there was a source for relating functions to references (these are "libraries" needed by functions/code) but I have never found one so I'm not 100% sure which one covers DateAdd. Maybe I can find something...

    EDIT - is something other than Access involved here? mySql or something else?
    I'm using 2010 Professional. I uploaded the DB, not sure if it worked. But I dont have any broken references. I'm only using Access.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #9
    IsaacRCCL is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    10

    Trying

    Quote Originally Posted by orange View Post
    I don't see the database????
    I'm trying to upload, but once it gets to 100% it doesnt do anything. I cant insert, doesnt show up at the bottom. Anyway, I put it on Filebin: https://filebin.net/73q1yz1haqejrryg

    Hope that helps.

    Thanks again for all your help

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    did you zip it first?
    I don't see any instructions as to what to open (form, query,etc) or what to look for. My mind reading hat is broken...

  11. #11
    IsaacRCCL is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    10

    DID you get it?

    Quote Originally Posted by Micron View Post
    did you zip it first?
    I don't see any instructions as to what to open (form, query,etc) or what to look for. My mind reading hat is broken...
    i Zipped it first. I tried to make it smaller than 12KB but its only a table and a query. Cant make it any smaller. Its just a Query. Field3 is my date column. Right now I have to type the range manually in the Query.

    Its just a Query.

    I hope the finebin thing worked because i cant upload here. Might be firewall cause i'm at work.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I take it that you didn't compact it after removing everything. There is a 2? Gb file size upload limit here. Yours was over 12 zipped. After I compacted, it is only 516 Kb.
    Will see what I can do today but will be busy otherwise.
    I wouldn't have asked about what to look at if I had opened it, but I didn't because unzipped it was over something like 100 meg. I figured it must have been loaded with forms, reports, data, etc. and that I wouldn't know what to look for. Sorry for making that assumption.

  13. #13
    IsaacRCCL is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    10
    12GB! Thats weird. on finbin, it says it only 12MB. Thats well below the 2Gb file limit. Unless you meant to say 2Mb. Dont know how much more I could have stripped it down. its just a table and a query.

    Thanks again tho.. sorry

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you look at my first attempt, I used (Date()). In my second, I used (date) - this will work as expected in the immediate window but not in a query (it gets converted to text; i.e."date"). However, if you look at the sql in your query, you used neither, but used [field3]. That won't work because there is no singular date value - it represents a data set of dates. A subquery might handle that but this is ugly enough!

    So this does seem to work
    Code:
    Between DateAdd("yyyy",1,DateSerial(Year(Date()),Month(Date()),1)) And DateAdd("m",1,DateAdd("yyyy",1,DateSerial(Year(Date()),Month(Date()),1)))-1
    The solution is predicated on what you initially wrote, which is "So if I'm searching today..." which I took to mean that you want this to be based on the current date. Thus if you ran this in February, it would be based on that month and not January or March. If not, then all that was for nothing really.

    Based on what's in your db, I'd say you ought to review how to name things...
    Naming conventions
    http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html

    Hope that works for you.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're right, it was only 12 Mb. I need to pay more attention, I guess!
    Maybe this will point out where you were going wrong with the attachment attempt (assuming you were)
    https://www.accessforums.net/showthread.php?t=70301

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. DateAdd
    By Burnsie in forum Access
    Replies: 5
    Last Post: 01-29-2018, 08:02 AM
  2. Replies: 7
    Last Post: 02-09-2015, 12:35 PM
  3. the DateAdd
    By azhar2006 in forum Queries
    Replies: 1
    Last Post: 04-20-2014, 03:18 PM
  4. DateAdd()
    By cbrsix in forum Programming
    Replies: 3
    Last Post: 11-22-2011, 02:20 PM
  5. dateadd help
    By dubsdj in forum Queries
    Replies: 3
    Last Post: 03-03-2011, 07:22 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