Results 1 to 4 of 4
  1. #1
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Dec 2010
    Posts
    209

    Show Data For Next 7 Days Only

    Hello, I have a table that has a startUpDate field - then we want to add anniversary date which we use


    Code:
    DateAdd('yyyy',1,startUpDate)
    to populate. Well now I want to have a query show me all records where anniversary is coming in the next 7 days, then a second query to show whose anniversary is in the next 30 days.

    I tried this query - but I get the error
    Data type mismatch in criteria expression

    Code:
    SELECT UserID, startUpDate, DateAdd('yyyy',1,startUpDate) AS Anniversary, Month([startUpDate]) AS AnniversaryMonth, Firstname, Lastname, Email
    FROM test
    WHERE (((DateAdd('yyyy',1,startUpDate))=DateAdd('yyyy',1,"startUpDate")-7))
    ORDER BY startUpDate DESC;

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you run that from sql view the offending part will often be highlighted. I don't see an obvious problem other than this should never work
    (((DateAdd('yyyy',1,startUpDate))=DateAdd('yyyy',1 ,"startUpDate")-7))
    If the left side results in a date 1 year from today, it will never equal the same date but minus 7 days.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Dec 2010
    Posts
    209
    Unfortunately when I run that from the SQL View of my query - there is no part highlighted which is what left me scratching my head.

    How would I write the query so that it returns all rows where anniversary is coming in the next 7 days

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Field name cannot be within quote marks. However, should use Date() for calculating "next 7 days".

    WHERE DateAdd('yyyy',1,startUpDate)=DateAdd('d',7,Date() )

    If you want all records that fall within 7-day period as opposed to just those that fall on the 7th day:

    WHERE DateAdd('yyyy',1,startUpDate) BETWEEN Date() AND DateAdd('d',7,Date())
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 9
    Last Post: 03-07-2016, 04:17 PM
  2. Replies: 2
    Last Post: 04-10-2015, 03:51 AM
  3. get last 5 days of data
    By skifreak in forum Queries
    Replies: 3
    Last Post: 03-11-2013, 01:13 PM
  4. Replies: 3
    Last Post: 07-18-2012, 10:08 PM
  5. Only Show Last 7 Days of data
    By MFS in forum Queries
    Replies: 8
    Last Post: 04-05-2012, 01:25 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