Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    May 2007
    Posts
    6

    Unanswered: Using "The last 28 days" in a query.

    I am using Access 2007 in a Windows 7 system

    I have a table which includes a "Changed On" date and I need to make a query which will always deliver the records for the recent four week period.

    My attempts were:

    WHERE ChangedOn > (Date()-28). and

    and

    Where [ChangedOn] >= (Date() - 28)

    Each produces an error message

    How should I do that?

    Many thanks,

    Robin Chapple

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look at the MS Access date time functions

    BTW its usually helpful to include the error message when requesting help
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2007
    Posts
    6

    Using "The last 28 days" in a query.

    With 57 million choices I did not know where to look. The error message was "Complie error in query expression".

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    A compile error suggests that there's a problem in the SQL statement that the syntax checker can't see. If you post the SQL statement, we'll have a better idea of where the problem might be.

    BTW, using Access 2k, the following code works in a query to return 28 days' worth of results:
    Code:
    WHERE (((qryResults.CheckDate) Between DateAdd("m",-1,Date()) And Date()))
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    more specifically
    Code:
    WHERE (((qryResults.CheckDate) Between DateAdd("d",-28,Date()) And Date()))
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Sorry, yes - my query is based on a calendar month, but worked when I tried it to run for 28 days.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    May 2007
    Posts
    6

    Using "The last 28 days" in a query.

    Thanks for your advice. I am a struggling 81 yesr old.

    This is how I interpretted you suggestion:

    SELECT tDataChanges.Email, tDataChanges.Email2, tDataChanges.Email3,
    tDataChanges.ChangedOn
    FROM tDataChanges
    WHERE (((tDataChanges.ChangedOn) Between DateAdd("d",-28,Date()) And Date()))
    ORDER BY tDataChanges.ChangedOn DESC;

    The original SQL was:

    SELECT tDataChanges.Email, tDataChanges.Email2, tDataChanges.Email3,
    tDataChanges.ChangedOn
    FROM tDataChanges
    Where [ChangedOn] >= (Date() - 28)
    ORDER BY tDataChanges.ChangedOn DESC;

    Each set of SQL give the "Compile error in query".

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Hmm. I can see nothing wrong with that. The only thing that comes to mind is to check that the data type of the date field is set to date/time, but I'm sure that's not the issue. Also, I have no experience of Access beyond 2000, so my input from here is likely to be limited.

    If you do get to the bottom of this, please post the answer as I'm intrigued now, and anticipating the latest version of Office at work at some point in the next 24 months!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    May 2007
    Posts
    6

    Using "The last 28 days" in a query.

    I confirm that the date field is set to date/time.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how did you build the SQL
    did you use the query builder or did you type the SQL?

    what you could do is break the SQL into segments and try to see where the problem lies

    assuming the changedon is a column in the table then it should be OK, if nto you may need to coerce it to a date using cdate(). bear in mind that Access is very US centric it doesn't recognise proper dates such as dd/mm/yyyy instead it 'prefers' us format mm/dd/yyyy. you cna get round that sometimes by using iso foramt yyyy/mm/dd

    it could be a problem with the date function. Ive not used 2007 or later much but I did struggle with the date function in A 2010
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    May 2007
    Posts
    6

    'Using "The last 28 days" in a query

    Quote Originally Posted by healdem View Post
    how did you build the SQL
    did you use the query builder or did you type the SQL?
    I have never learned how to use the query builder. This time I used it with this result:

    SELECT tDataChanges.Email, tDataChanges.Email2, tDataChanges.Email3, tDataChanges.ChangedOn
    FROM tDataChanges
    WHERE (((tDataChanges.ChangedOn)>Date()-28));

    Which produced the error message. "Compile error in query expression".

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I tried this query that works as expected:
    Code:
    SELECT tDataChanges.Email, tDataChanges.Email2, tDataChanges.Email3, tDataChanges.ChangedOn
    FROM tDataChanges
    WHERE (((tDataChanges.ChangedOn)>=(Date()-28)))
    ORDER BY tDataChanges.ChangedOn DESC;
    Table: tDataChanges
    Email: Text (50)
    Email2: Text (50)
    Email3: (Text (50)
    ChangedOn: Date/Time

    On a system with:
    Windows XP Pro V. 2002 SP3
    MS Office Access 2007 (12.0.6535.5005) SP2 MSO (12.0.6545.5004)

    Could you post a copy of your database or a database containing the table and the query?
    Attached Thumbnails Attached Thumbnails Table_Builder.jpg   Query_Builder.jpg   Query_SQL.jpg   Query_Result.jpg  
    Have a nice day!

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    i suspect its something in your table design or column names
    theres eithger a typo or a column wrongly named
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    May 2007
    Posts
    6

    Using "The last 28 days" in a query.

    My attempts still fail. I have been diverted by other more important projects. The basic data has many fields not used for this project and sits on a remote server and is collected by a 'Bitvise Tunnelier' ODBC connection from an SQL database. In order to ensure that this special case was not the cause of the problem I have made a separate table in Access called "tEmailChanges".

    One comment that has been made is that I cannot have a 'compline error' in a query. I have collected the various stages by screen grabs here:

    Test Page

    It seems to be a major problem.

    There must be a solution somewhere.

    Since writing the above I experimented by making a new Access file, making a link to the table from the original separate table and the same query gives the required result. Now that I really do not understand.

    Many thanks,

    Robin Chapple

  15. #15
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Here's a thought. Try this:
    Code:
    Where Date([ChangedOn]) >= (Date() - 28)
    Although I admit that I'm clutching at straws with this, but there might be a time element to the date values that are screwing things up a bit.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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