Results 1 to 3 of 3
  1. #1
    veejay is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Nov 2018
    Location
    Montreal, Canada
    Posts
    46

    Select query with start date + 7

    I would like to set up a query in which the where clause ask for a start date in this format dd/mm/yyyy and then asks for an end date in the same format.



    Since this particular report is created to be a weekly report is there a way to tell access that no mather what the start date is, the end date should be +7 days and not having to type the full date?

    Also, since the information in my table is saved as dd/mm/yyyy I assume it's the reason why I have to type the date like this, but was wondering if it's possible to just type it straight as ddmmyyyy?

    EDIT
    I figured maybe it would be a good thing to have the current query:

    Code:
    SELECT QUERYCOLLECT1.Date, QUERYCOLLECT1.[THETYPE], QUERYCOLLECT1.[THENAME], QUERYCOLLECT1.APPLICATIO, QUERYCOLLECT1.REVIEWER_S, QUERYCOLLECT1.COMMENTS, QUERYCOLLECT1.EXAMINER_S, QUERYCOLLECT1.DRAWING, QUERYCOLLECT1.Notes, QUERYCOLLECT1.[DATE OF ACTION], [SCORE RESULTS].[TEXT BODY] FROM [SCORE RESULTS] INNER JOIN QUERYCOLLECT1 ON [SCORE RESULTS].APPLICATIO = QUERYCOLLECT1.APPLICATIO
    And specificaly in the QUERYCOLLECT1 Query :
    Code:
    WHERE (((REVIEW11.[DATE_OF_ACTION]) Between [Start Date dd/mm/yyyy] And [End Date dd/mm/yyyy]));

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is there a way to tell access that no mather what the start date is, the end date should be +7 days and not having to type the full date?
    dates are stored as numbers so criteria would be along the lines of

    PARAMETERS [Enter Start Date] DateTime;
    SELECT ….your sql here
    WHERE startDate Between [Enter Start Date] and [Enter Start Date]+7

    since the information in my table is saved as dd/mm/yyyy I assume it's the reason why I have to type the date like this,
    no the dd/mm/yyyy is just a format of a number today for example is 43483 - the number of days since 30/12/1899.

    You need to understand the difference between a format property and the format function. You are seeing the date because that is the way the format property has been set (the default is as specified by windows system settings). the format function returns a string - 'dd/mm/yyyy'. And a string is not a date. In sql be aware that it uses the US format for dates (mm/dd/yyyy) when converting strings to dates

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Be aware that "Date" is a reserved word in Access and shouldn't be used as an object name.

    Also, it is best to avoid spaces in object names.

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

Similar Threads

  1. Replies: 5
    Last Post: 04-06-2017, 03:24 PM
  2. Start Date Query Help
    By aamer in forum Access
    Replies: 6
    Last Post: 03-10-2016, 10:41 AM
  3. Replies: 6
    Last Post: 03-02-2016, 12:58 PM
  4. call specific start date in crosstab query
    By tngirl in forum Queries
    Replies: 4
    Last Post: 03-11-2014, 07:20 AM
  5. Start & End Date Query
    By batowl in forum Queries
    Replies: 1
    Last Post: 05-16-2011, 09:11 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