Results 1 to 14 of 14
  1. #1
    lukusm's Avatar
    lukusm is offline Novice
    Windows 7 Access 2007
    Join Date
    Jan 2010
    Location
    Lodz, Poland
    Posts
    9

    Unhappy Problem with datediff (interval quotes completion)

    Hello,

    I have problem with datediff function. When I try to calculate number of years between two dates (and use yyyy interval) something goes wrong. No matter if I insert yyyy into pair of quotes or write it without quotes, access adds two quotes before and two after interval.



    for example:
    instead of datediff("yyyy",dateOfBirth,now())
    i have datediff("""yyy""",dateOfBirth,now()).

    I don't know what's going on with this... Any help suggestions will be appreciated.

    Greets from Lodz, Poland.

    Lucas

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have you done a Compact and Repair yet? How about *importing* your db into a fresh, new db? http://www.btabdevelopment.com/ts/impnew

  3. #3
    lukusm's Avatar
    lukusm is offline Novice
    Windows 7 Access 2007
    Join Date
    Jan 2010
    Location
    Lodz, Poland
    Posts
    9
    Unfortunately this not work. I saw this problem on a few access installations on different laptops...

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Hmm...how about trying:
    DateDiff("yyyy",dateOfBirth,Date)

  5. #5
    lukusm's Avatar
    lukusm is offline Novice
    Windows 7 Access 2007
    Join Date
    Jan 2010
    Location
    Lodz, Poland
    Posts
    9
    Quote Originally Posted by RuralGuy View Post
    Hmm...how about trying:
    DateDiff("yyyy",dateOfBirth,Date)
    Thanks for reply, but I said I have problem not with any kind of data, but with formatting datediff function. Access adds to datediff additional quotas (like in my example painted on red). And with those three pairs of quotas my query doesn't work. Even if I delete 2 pairs, access before running adds them. This is terrible. And I cannot find any solution of this problem....

  6. #6
    KrisDdb is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    58
    what is the name of the textbox that displays 'dateOfBirth' ??

    Is the textbox for which you are using the DateDiff function, on the same form as the 'dateOfBirth'?

  7. #7
    pka4916 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    17
    Did you try this?


    Enddate = now();


    DateDiff("yyyy", dateOfBirth, enddate)

    This seems to work for me just fine

  8. #8
    lukusm's Avatar
    lukusm is offline Novice
    Windows 7 Access 2007
    Join Date
    Jan 2010
    Location
    Lodz, Poland
    Posts
    9

    Problem with datediff...

    I'm explaining that problem is not with any kind of datas or variables, but only with formatting...

    For example I want to have that kind of datetiff
    datediff("yyyy";date1;date2)
    but ACCESS returns for me this
    datediff("""yyyy""";date1;date2)
    There are too much quotas....

  9. #9
    KrisDdb is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    58
    lukusm,

    there could be a problem with the variable. That is the reason i asked for the name of the textbox. Anyways.. u can try putting this in the control source of the textbox which displays DateDiff

    =DateDiff("yyyy",([Forms]![SubformName if you have one].[yourFormName].[name of textbox that has dateofbirth]),Now())

    Try to substitue in the above with the names of your forms and subforms in the parantheses. Hope that helps.

  10. #10
    pka4916 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    17
    stupid question, but does the code still work or does it give you an error?
    Meaning is it just the look for it... Kinda like a spellchecker with autochange enabled.

  11. #11
    pka4916 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    17
    Also, when you start typing DateDiff, and you get the help part
    is it coming up with Interval As string,Date1,Date2 etc ?

  12. #12
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    How about posting a copy of a sample database that shows the misbehaving function in action?

  13. #13
    lukusm's Avatar
    lukusm is offline Novice
    Windows 7 Access 2007
    Join Date
    Jan 2010
    Location
    Lodz, Poland
    Posts
    9
    Quote Originally Posted by pka4916 View Post
    stupid question, but does the code still work or does it give you an error?
    Meaning is it just the look for it... Kinda like a spellchecker with autochange enabled.
    So where I can disable this option?

  14. #14
    pka4916 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    17
    Quote Originally Posted by lukusm View Post
    So where I can disable this option?

    I checked but that's not in Access

    Maybe you can post the Mdb file, so we can look at it.

    If it's happening all the time, just create a new Mdb file and put the code for it in, when it happens.

    Maybe we can see what's causing it.

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

Similar Threads

  1. datediff problem
    By dseeburg in forum Queries
    Replies: 3
    Last Post: 04-07-2011, 06:05 AM
  2. Replies: 6
    Last Post: 12-02-2010, 06:07 PM
  3. DateDiff
    By ROB in forum Access
    Replies: 2
    Last Post: 10-30-2010, 03:58 AM
  4. How to use DateDiff?
    By teirrah1995 in forum Queries
    Replies: 10
    Last Post: 10-19-2010, 12:07 PM
  5. Remove Quotes within Data Values
    By kfschaefer in forum Programming
    Replies: 0
    Last Post: 02-26-2009, 01:15 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