Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58

    DateAdd in Query Criteria to display a date in a field 2 days prior to the date in another field


    Hello All, I am trying to use the DateAdd feature in an Access 2010 Query. The criteria I am using is [DateAdd("d",-2,[StartDate])
    This code is in the criteria field of a field called "PurchaseDate" in design view. The "PurchaseDate" field is blank right now when I run the Query. I want to display a date in the "PurchaseDate" field when I run the query that is 2 days prior to the current date that is in the "StartDate" field (which is currently populated with a date in the format mm/dd/yyyy. I haven't had any luck getting it to work. I displays nothing when I run the Query. Thanks for any help.

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    The DateAdd function isn't really necessary. You should just be able to use:
    Code:
    =[StartDate]-2
    in your criteria.

    But let's talk about the StartDate field. Is that field directly from a table?
    If so, what is its Data Type?
    Or is it the result of calculation? If so, what is that calculation?

  3. #3
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    The StartDate field is from a table via another query named Production_Query. I populate that StartDate directly in the Production_Query. I am running another Query (Purchase_Query) off the Production_Query which is where I am trying to populate the "PurchaseDate" using the mentioned code. I tried [=[StartDate]-2] without success. Thanks.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So, what is the calculation for the StartDate?
    Also, if it really is a DateTime field (and not text), does it contain a time component?

  5. #5
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    There is no calculation for the StartDate. It is just a manually entered date. It is a DateTime field and does not have any time component. Thanks.

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Try this.

    In your query, at first do not use any Criteria.
    Then, add the following calculated field:
    Code:
    MyTest: [StartDate] - 2
    And view the results.

    Does it return a valid date in that column?
    Is it the date you expect?
    Last edited by JoeM; 01-13-2015 at 10:46 AM. Reason: minor typo

  7. #7
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    It gives me. Data type mismatch in criteria expression.

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It gives me. Data type mismatch in criteria expression.
    That tells me that, in fact, your StartDate field is probably NOT of DateTime data type.
    I am guessing it is probably Text.

    So it may be that either the underlying field in the original table is not Date, or you are doing some sort of manipulation/calculation to it along the way converting it to Text.

    If you are having trouble figuring it out, you can upload a copy of your database for analysis. Just be sure to remove any confidential data. I cannot download files from my current location, but could do so tonight.

  9. #9
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    I went to the original table and confirmed that it is indeed Date/Time data. And I checked the other queries along the path also. As for uploading the database, I do not have authorization to do that. Sorry.

  10. #10
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Can you post the SQL code of your Query?
    Maybe something will stand out...

  11. #11
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    You just went beyond my capabilities. But I think this is what you want.

    SELECT Production_Schedule.CustomerName, Production_Schedule.SalesOrderNo, Production_Schedule.LineNo, Production_Schedule.ItemName, Production_Schedule.Rev, Production_Schedule.Qty, Production_Schedule.PW, Production_Schedule.StartDate, Production_Schedule.PurchaseDate
    FROM Production_Schedule
    WHERE (((Production_Schedule.PW) Is Null) AND ((Production_Schedule.StartDate)>#1/1/2014#)) OR (((Production_Schedule.PurchaseDate)=[StartDate]-2));

  12. #12
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    OK, how about the PurchaseDate field? Is that also of data type DateTime?
    If you run this query without any Criteria, do you see any odd entries in either of the two date fields (StartDate and PurchaseDate)?
    Any errors?

  13. #13
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    PurchaseDate field is DateTime field. No odd entries if I run it without any criteria. No errors at all that I can see. Everything looks normal.

  14. #14
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    PurchaseDate field is DateTime field. No odd entries if I run it without any criteria. No errors at all that I can see. Everything looks normal.
    The data in these two Date fields, when you are viewing it in the query (without the criteria), are the entries left or right justified within the cells?

    Regarding the SQL code for the query you posted up in post #11 (with the criteria), is the issue with that code that it returns errors or it does't return any data
    If it is not returning any records and you think it should be, could you give me a few examples?
    I know you said that you cannot post the database, but if you could post the value of the following three fields (PW, StartDate, Purchase) for a few of your actual records that you are expecting to be returned by the table, that would be great. Then I could at least try to recreate your situation, as best as I can.

  15. #15
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Assuming today's date is 1/13/2015

    WHERE (((Production_Schedule.PW) Is Null) AND ((Production_Schedule.StartDate)>#1/1/2014#)) OR (((Production_Schedule.PurchaseDate)=[StartDate]-2));

    This query is saying show me everything where:

    1. The PW field of the PRODUCTION_SCHEDULE table is null

    AND

    2a. the STARTDATE field from the PRODUCTION_SCHEDULE table is greater than january 1, 2014
    OR
    2b. the PURCHASEDATE field from the PRODUCTION_SCHEDULE table is exactly 2 days prior to the STARTDATE

    Is that really what you're after?

    Your original post was asking how you display the results of a formula, NOT a criteria.


    If you just want to display value based on a formula it would be:

    SELECT Production_Schedule.CustomerName, Production_Schedule.SalesOrderNo, Production_Schedule.LineNo, Production_Schedule.ItemName, Production_Schedule.Rev, Production_Schedule.Qty, Production_Schedule.PW, Production_Schedule.StartDate, dateadd("d", -2, cdate(production_schedule.startdate)) as PurchaseDateCalc
    FROM Production_Schedule
    WHERE (((Production_Schedule.PW) Is Null) AND ((Production_Schedule.StartDate)>#1/1/2014#))


    I am using the cdate function because you said it was choking on adding days to startdate

    I am also using an alias for the purchase date calculation because you shouldn't (and perhaps can't) name a field the same way as it appears on one of the source tables.


Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query with criteria a date field of a form
    By panoss in forum Queries
    Replies: 1
    Last Post: 12-22-2014, 09:03 AM
  2. Replies: 1
    Last Post: 09-06-2014, 01:08 PM
  3. Replies: 2
    Last Post: 06-09-2014, 11:23 AM
  4. Replies: 9
    Last Post: 04-18-2014, 08:51 PM
  5. Replies: 2
    Last Post: 04-27-2010, 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