Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Querying a date/time field

    Using <=Now()+30 to show me the next 30 days in a query. I am using this on a date/time field, formatted to 1/16/2010. For some reason when I query on this particular table it will not work. To get it to work I have to use a between #1/16/2010 # and #2/15/2010 #. I really don't want to go that route because I will have to change the date everytime it's run. Is there something else I can use? Is it a formatting issue with the field? Thanks.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    What do you mean by "does not work?"
    Does it produce an error? return too many results? too few results?

    Now() returns the current date and time, Date() returns just the current date. That is why my previous post suggested the Date() function.
    The Time component will add extra digits after the decimal point, and so could change the results returned a little.
    Regards
    John



  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    You need to use the DateAdd function. Something like this should work:

    <=DateAdd("d",+30,Now()))

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    By not work I should clarify. I mean it will show all the 2010 data not the next 30 days that I am looking for. I tried <=DateAdd("d",+30,Now()) . It gave me all tha data for 2010. Any other solutions? Thanks so much..

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by ruirib View Post
    You need to use the DateAdd function. Something like this should work:

    <=DateAdd("d",+30,Now()))
    Dates are stored in Access just as numbers, so you can use the DateAdd function, but to add a number of days you can just also use plain arithmetic.
    Regards
    John



  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Do you mean all the 2011 Dates? Did you put in the extra test to exclude dates that have come and gone?

    You need <=Date()+30 and also >=Date() to exclude past dates.
    Regards
    John



  7. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Quote Originally Posted by johnhutchison View Post
    Dates are stored in Access just as numbers, so you can use the DateAdd function, but to add a number of days you can just also use plain arithmetic.
    Hi,

    Maybe so, I won't deny it, but after using a few DBs and languages and getting some issues sometimes, I have resorted to a common, always working strategy - use the date handling functions to perform date calculations.

Posting Permissions

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