Results 1 to 7 of 7
  1. #1
    Lo-Wang is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    3

    Problem with DateAdd

    Hello,

    The problem I have might see very simple but I cannot get past it and am stuck.
    The situation is the following:

    There is a column with dates with "Date/Time" data type (and Short Date format). In a query I want to display all records which are 14 days later from the assigned date (there are different dates for each record).

    I have tried with the following expression in the quety criteria ("InvLetDate" is the name of the column with the dates that are being checked):

    DateAdd("d";14;[InvLetDate])

    With this expression I receive always no results although there should be some.

    What am I doing wrong



    Please help

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    DateAdd("d",14,[InvLetDate])

    try this

  3. #3
    Lo-Wang is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    3
    Unfortunately when I put it with commas I get the error message:

    "The expression you entered contains invalid syntax"

  4. #4
    kimmer is offline Competent Performer
    Windows 7 Access 2010 (version 14.0)
    Join Date
    May 2010
    Posts
    100
    Maximus is correct. There must be something else going on. Are you putting this in the criteria cell for Assigned Date?

  5. #5
    Lo-Wang is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    3
    Hi,

    Yes. The expression is put in the criteria of the date column that is to be evaluated. There are no other criterias set up.
    As mentioned before. Commes separating the variables do not work as I receive the error message. Only with semicolon (maybe it is the MS version - I have 2007).

    Maybe it is something with the format of the column in the main database?

    Maybe there is a different expression that can give the same result but will work for sure?

  6. #6
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    I m attaching a mdb file see query table1 query 1 I have used DateAdd Function in the Criteria.

  7. #7
    provet is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    1
    Apparently this thread is still read by a lot of people looking for an answer to this problem if anyone wonders why I reply to such an old thread.
    (at least according to the banner at the bottom of this thread before I logged in).

    If you're only here for the solution and not why it isn't working, go to the bottom.

    When I had this problem it was because access automatically added invisible citation marks (in design-view) around the whole expression.


    It seems the reason this error message occur is when the expression contains citation marks it doesn't recognize.
    This is because the whole expression is also inside citation marks, and then it can't interpret the expression correctly.

    For example:
    DateAdd("d",14,Now())

    In the design view the above expression is viewed that way,
    but in reality it looks like this:
    "DateAdd("d",14,Now())" <- which is interpreted as a string.
    And that is incorrect syntax for a string; you must have two citation marks
    when using citation marks inside a string, like so:
    "DateAdd(""d"",14,Now())"

    But that would output a string that looks like an expression.

    Solution:
    So to really solve it you either;
    1: use two citation marks instead of one, then when you're done with the expression switch over to sql view and change back to only 1 citation mark and then remove the outer citation marks so it's no longer interpreted as a string.
    2: complete or write the whole expression in the sql view.

    I do not know why they appear. Some simple expressions like Now() seems to work fine without needing to go into the sql view to fix it,
    so it's probably a bug.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-14-2010, 03:25 PM
  2. Help Please - DateAdd Function
    By graviz in forum Queries
    Replies: 3
    Last Post: 03-02-2010, 02:34 PM
  3. query problem i have a problem wi
    By maxx3 in forum Queries
    Replies: 0
    Last Post: 06-29-2009, 02:29 AM
  4. Change Between by DateDiff or DateAdd.
    By wagner.bts in forum Queries
    Replies: 1
    Last Post: 06-05-2009, 11:24 AM

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