Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2005
    Posts
    9

    Unanswered: HELP HURRY MS Acess Query

    I am building an MS Access Query and need to be able to calculate dates.

    Example:

    =Today() + 3
    =Date() + 3
    are the formulas I have tried but MS Access does not recognized that function. I can use the Now() function but it includeds the time which I do not need.

    Could someone tell me a function that will automatically show the current date that MS Access recognizes. I need this as soon as possible

    Also thanks you for any help or comments you post for this problem.

    Beebop

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The database used in most Acess applications knows doodly squat about dates, like most database engines it knows about date/time values.
    now()+x returns a date/time valie x days in the future
    if you want to display the value you need to format it
    eg
    =format(now()+x,"dd mmm yyyy")
    if you assing that value to a date/time value JET still stores a time but its 00:00:00

    The basic lesson is that what is stored isn't neccesarily the same as what is displayed. If you round a number in a report the sum of the same colun in the report won't neccesarily = the same as the sum of round(column)
    HTH

  3. #3
    Join Date
    Nov 2005
    Posts
    9
    Quote Originally Posted by healdem
    The database used in most Acess applications knows doodly squat about dates, like most database engines it knows about date/time values.
    now()+x returns a date/time valie x days in the future
    if you want to display the value you need to format it
    eg
    =format(now()+x,"dd mmm yyyy")
    if you assing that value to a date/time value JET still stores a time but its 00:00:00

    The basic lesson is that what is stored isn't neccesarily the same as what is displayed. If you round a number in a report the sum of the same colun in the report won't neccesarily = the same as the sum of round(column)
    HTH

    Okay I have tried the formula you recommended and now I am getting a message of ....... Undefined Function 'Format' in Expression....
    Why

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    DateTime

    Would the DateAdd function work?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2005
    Posts
    9
    Quote Originally Posted by pkstormy
    Would the DateAdd function work?


    It works with the Now() function but not with any other functions.
    I need the system to show the current date without the time in the query, so that then the system will pull records that are plus 3 days from the current date. Having the time in the field will not pull all records I need

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by beebop716
    It works with the Now() function but not with any other functions.
    I disagree. How did you use it that it failed to return the data you want?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jan 2006
    Posts
    14
    If you already have the date you want to add 3 days to in a table, pull that field into a query. Create an expression like:
    newdate: =dateadd("d",3,[date])
    where [date] is the field name for the date field in your table and that will add 3 days to it.

    If you don't already have a date in a table and are just doing it in the query:
    Date: =dateadd("D",3,now())
    Go to properties of this field in your query and change the format to short date to take the time stamp out.

    Hope this helps!

  8. #8
    Join Date
    Nov 2005
    Posts
    9
    Quote Originally Posted by adaviskheslc
    If you already have the date you want to add 3 days to in a table, pull that field into a query. Create an expression like:
    newdate: =dateadd("d",3,[date])
    where [date] is the field name for the date field in your table and that will add 3 days to it.

    If you don't already have a date in a table and are just doing it in the query:
    Date: =dateadd("D",3,now())
    Go to properties of this field in your query and change the format to short date to take the time stamp out.

    Hope this helps!

    Let me make this simple

    I want to build a query in MS Access and in that query I want to create a new field in the query that list the current date (without the time) how would I do that?

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    month(now()) & "/" & day(now()) & "/" & year(now())
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Jan 2006
    Posts
    14
    Quote Originally Posted by beebop716
    Let me make this simple

    I want to build a query in MS Access and in that query I want to create a new field in the query that list the current date (without the time) how would I do that?
    Did you try (typing this in exactly):

    Date: =dateadd("D",3,now())

    And then right click on this expression, go to properties, then format and change it to short date?

    This works for me when I did it

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    refinement
    ...what happens very close to midnight with three calls to Now()
    ...what if you are in the other 95% of the world not using US date format

    dim myNow as date
    myNow = now()
    myNow = dateserial(year(myNow),month(myNow),day(myNow))


    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Nov 2005
    Posts
    9
    Yes that formula worked now tell me this

    I want the query to return all records where the duedate(field in my query) is equal to 3 days after the current date eg .... =now() + 3..... but I do not want the time calculated. I have tried the following

    today() + 3 error message of ......undefined function Today in expression......
    date() + 3 error message of ......undefined function Date in expression......
    getdate() + 3 error message of ......undefined function GetDate in expression......
    format(now() + 3,"mm dd yy") error message of ......undefined function Format in expression......

    The following formula works without giving me an error message but it does not return records that are today's date plus three days

    now() + 3

    Help

  13. #13
    Join Date
    Jan 2006
    Posts
    14
    Quote Originally Posted by beebop716
    Yes that formula worked now tell me this

    I want the query to return all records where the duedate(field in my query) is equal to 3 days after the current date eg .... =now() + 3..... but I do not want the time calculated. I have tried the following

    today() + 3 error message of ......undefined function Today in expression......
    date() + 3 error message of ......undefined function Date in expression......
    getdate() + 3 error message of ......undefined function GetDate in expression......
    format(now() + 3,"mm dd yy") error message of ......undefined function Format in expression......

    The following formula works without giving me an error message but it does not return records that are today's date plus three days

    now() + 3

    Help
    because you have to tell it to add 3 days to the now(), + will not work the way you have it. You have to say =DateAdd("d",3,now())
    this is saying add 3 in the day part "D" to the current date now() and just change the format to short date so it does not show the time

  14. #14
    Join Date
    Nov 2005
    Posts
    9
    I tried that in the criteria, but the record that should return is not returning

    I have a record with a duedate (this field is set to short date format) of 01/08/2006. I would like for that record to return in my query

  15. #15
    Join Date
    Jan 2006
    Posts
    14
    Quote Originally Posted by beebop716
    I tried that in the criteria, but the record that should return is not returning

    I have a record with a duedate (this field is set to short date format) of 01/08/2006. I would like for that record to return in my query

    OK, so if you already have a date in your duedate field, go to a blank field in your query and create a new expression to calculate it off the duedate field:
    NewDuedate: =dateadd("D",3,[duedate])
    You then have to change the format of this new expression to short date too or it will still show the time

Posting Permissions

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