Results 1 to 14 of 14
  1. #1
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47

    Smile DateAdd function in Access report

    Hi guys,
    This is the background of my project:


    I am currently working on a Purchase Order project and some PO might contain a large amount of goods and will have to split into two delivery date hence, i come up with a delivery report whereby it will prompt user to enter the delivery date. Besides that, I have also used the DateAdd function in my delivery textbox control source =DateAdd('d',-7,[PromisedDate]). With this function, the date on the report will display 7 days in advance.

    Issue:
    My issue is that this function can only prompt user to key in one delivery date instead of two. I tried =DateAdd('d',-7,[1stPromisedDate] OR [2ndPromisedDate]) it did prompt user to key in two delivery date however result result returned me 22DEC1899. I want the report to prompt user to enter the delivery date twice and result will return two different delivery date that show 7 days in advance.
    I do not have any idea where i goes wrong hence, I would like to seek help from expert here! I greatly appreciate any help that offer! Thank you very much!

    Have a good day everyone!

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Maybe you need two textboxes for the user to enter the delivery dates with calculations for each.

  3. #3
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47
    Hi, I tried using two textbox but the result i get is the same tho the date are different. By right with different delivery date the detail should be different as well.Click image for larger version. 

Name:	Untitled.jpg 
Views:	15 
Size:	40.3 KB 
ID:	30658

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your question was about DateAdd - we need to know a lot more details about how the data is collected, how do you determine what goes with which date, etc. in order to assist you.

  5. #5
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47
    Hi, these data that is in the scrrenshot above is collected based on purchase order and lets say the purchase order have lots of data, the purchase order will have to split into two batches of shipping date thus that explain why i would like to use dateadd to prompt user to enter two date. Besides that, two batches of shipping date should show two different data that was retrieve from by the same purchase order.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In your screenshot above, you have 4 items which you want split into two deliveries. How do you determine what gets delivered with date 1 and what with date 2? Do you take exactly half the weight of each, or do you take half the number of items, or is there a maximum weight for the first delivery and what is left is what goes on the second delivery?

  7. #7
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47
    Quote Originally Posted by aytee111 View Post
    In your screenshot above, you have 4 items which you want split into two deliveries. How do you determine what gets delivered with date 1 and what with date 2? Do you take exactly half the weight of each, or do you take half the number of items, or is there a maximum weight for the first delivery and what is left is what goes on the second delivery?
    Hi! The splitting of data into two deliveries are based on the fabric. For instance one PO number might consist of more than one fabric content, for example 100%Cotton will be categories as date 1 then 60%Cotton 40% Poly will be categories as date 2. I have also attach an example of the data and the report above.
    Attached Files Attached Files

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If we're talking about a report, then the data is already entered, yes? Then why are you not just basing the report on a query that returns all delivery dates for a given PO?
    If you're wanting someone to input a date and get all deliveries for all POs for a given date, then you need to query the delivery data based on date, and include the PO numbers as well. If it's neither, then I'm confused - as usual.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47
    Quote Originally Posted by Micron View Post
    If we're talking about a report, then the data is already entered, yes? Then why are you not just basing the report on a query that returns all delivery dates for a given PO?
    If you're wanting someone to input a date and get all deliveries for all POs for a given date, then you need to query the delivery data based on date, and include the PO numbers as well. If it's neither, then I'm confused - as usual.
    The delivery date based on the database is the dateline. I want the merchandiser to input the date as they can set the date on when they would want the order to be delivered out because they will normally set one fabric to delivery out earlier than the other fabric if the PO contains more than one fabric. Besides that, the data that user input in will show 7 days in advanced on the report.

    I hope the above explanation can resolve your confusion

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    'Fraid not. You're really just repeating what you said before in a different way. Still have no idea if we're inputting data in a form and for what purpose. I mean, you don't input data using reports, and you're talking about reports and inputting data - makes no sense to me.
    At any rate, the answer may be in post 2, but using the correct application of DateAdd:

    BETWEEN DateAdd('d',-7,[1stPromisedDate]) AND [2ndPromisedDate]
    or maybe
    BETWEEN DateAdd('d',-7,[1stPromisedDate]) AND DateAdd('d',-7,[2ndPromisedDate])
    If either of these don't contain a date, expect strange results as written.
    The above is the criteria part of a query and cannot be typed into a control as is.
    Last edited by Micron; 10-09-2017 at 10:21 PM. Reason: clarification

  11. #11
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47
    We are inputting date into a report call fabric delivery, where the date will display 7 days in advance based on the promised date user key in. The purpose is to let the production team know that that's the date that they have to finished the order so that it will be able to deliver on time

  12. #12
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47
    Quote Originally Posted by Micron View Post
    'Fraid not. You're really just repeating what you said before in a different way. Still have no idea if we're inputting data in a form and for what purpose. I mean, you don't input data using reports, and you're talking about reports and inputting data - makes no sense to me.
    At any rate, the answer may be in post 2, but using the correct application of DateAdd:

    BETWEEN DateAdd('d',-7,[1stPromisedDate]) AND [2ndPromisedDate]
    or maybe
    BETWEEN DateAdd('d',-7,[1stPromisedDate]) AND DateAdd('d',-7,[2ndPromisedDate])
    If either of these don't contain a date, expect strange results as written.
    The above is the criteria part of a query and cannot be typed into a control as is.
    User will only have to key in the promised date twice if the PO contain more than one fabric.
    And as seen in the image i attached which is my fabric delivery table which have display 2 different date just that the value are the same. I want the value to be different for the 2 delivery date.Click image for larger version. 

Name:	delivery.png 
Views:	11 
Size:	7.2 KB 
ID:	30716

  13. #13
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So if I get it now, you need a PO table to act as a header, and a POLineItems table to hold the line items for a given PO for each delivery date as in
    tblPO
    PO_ID PO_Num
    1 123
    The next table shows that for PO 123 (it's ID is 1 from tblPO), you have 3 delivery dates, description and quantity showing as well.
    tblPO_Line
    PO_ID DESC DelDate Weight
    1 bright 1/01/17 2000
    1 dark 1/15/17 2200
    1 red 1/22/17 3300
    If you already have a PO line items table, then you'll need a PO_Delivery table instead. The thing is, you should not attempt to split delivery dates up when storing the dates, PO number and items in the same table. Personally, I'd have 3 since each is really a separate thing.

  14. #14
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47
    Quote Originally Posted by Micron View Post
    So if I get it now, you need a PO table to act as a header, and a POLineItems table to hold the line items for a given PO for each delivery date as in
    tblPO
    PO_ID PO_Num
    1 123
    The next table shows that for PO 123 (it's ID is 1 from tblPO), you have 3 delivery dates, description and quantity showing as well.
    tblPO_Line
    PO_ID DESC DelDate Weight
    1 bright 1/01/17 2000
    1 dark 1/15/17 2200
    1 red 1/22/17 3300
    If you already have a PO line items table, then you'll need a PO_Delivery table instead. The thing is, you should not attempt to split delivery dates up when storing the dates, PO number and items in the same table. Personally, I'd have 3 since each is really a separate thing.
    I figure out that it will be easier for you to understand in this way. I have attached two images above.

    The first image: Purchase Order Form, and you will be able to see that in this PO form there are two type of fabrication with lots of colors.
    The second image: Is the fabric delivery table that i am referring to and you can see that it has two different date with two different colors. The first delivery table ( 3/AUG) is based on the first fabrication(100% Cttn 28/1 1x1 RIB). The second delivery table (25/AUG) is based on the second fabrication (100% Cttn 16/1 Jersery).Click image for larger version. 

Name:	IMG_9447.jpg 
Views:	12 
Size:	134.8 KB 
ID:	30717Click image for larger version. 

Name:	IMG_9448.jpg 
Views:	11 
Size:	157.8 KB 
ID:	30718

    I want my delivery table to look like the second picture, which i have done the delivery date, but unable to pull out the data that i want

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

Similar Threads

  1. Dateadd function
    By JackieFeng in forum Access
    Replies: 2
    Last Post: 05-02-2016, 10:48 AM
  2. used function DateAdd
    By azhar2006 in forum Queries
    Replies: 2
    Last Post: 10-07-2015, 03:31 PM
  3. DateAdd Function?
    By zburns in forum Reports
    Replies: 7
    Last Post: 06-23-2015, 01:55 PM
  4. Replies: 7
    Last Post: 08-14-2013, 03:57 PM
  5. Open Report w DateAdd function
    By libraccess in forum Programming
    Replies: 3
    Last Post: 02-21-2013, 12:31 AM

Tags for this Thread

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