DateAdd function in Access report (1 Viewer)

luzz

Registered User.
Local time
Today, 15:41
Joined
Aug 23, 2017
Messages
346
Hello everyone! I am facing issue with my delivery table as it only allow me to have one promised date = can only use dateadd function once for user to input the date in themselves. I would like the user to input two different date. How can i do that?

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).

I would like my table to look something similar to the second image.
 

Attachments

  • IMG_9447.jpg
    IMG_9447.jpg
    85 KB · Views: 62
  • IMG_9448.jpg
    IMG_9448.jpg
    106.7 KB · Views: 65

Minty

AWF VIP
Local time
Today, 22:41
Joined
Jul 26, 2013
Messages
10,355
Your data isn't stored correctly.
If you want to display / use a promised delivery date you should store it against each order line, then you can have as many dates as you have lines.
 

luzz

Registered User.
Local time
Today, 15:41
Joined
Aug 23, 2017
Messages
346
Your data isn't stored correctly.
If you want to display / use a promised delivery date you should store it against each order line, then you can have as many dates as you have lines.


the delivery date is based on each line of recordset
 

Minty

AWF VIP
Local time
Today, 22:41
Joined
Jul 26, 2013
Messages
10,355
Okay - so rather than asking for one date why not ask for a range.

Pseudo code; PromisedDate = Between StartDate and EndDate
 

luzz

Registered User.
Local time
Today, 15:41
Joined
Aug 23, 2017
Messages
346
Okay - so rather than asking for one date why not ask for a range.

Pseudo code; PromisedDate = Between StartDate and EndDate

Hmm, i am not sure if range work in my case as the user will have to input two different date which will display 7 days in advance in the report. The result should show different data(in my case will be colors and grossweight) for this two date.
 

Minty

AWF VIP
Local time
Today, 22:41
Joined
Jul 26, 2013
Messages
10,355
I'm confused by your description.
Show us two things - some sample starting data, and the data(not a report) that you expect out.

Formatting the report is secondary to being able to get the data.
 

luzz

Registered User.
Local time
Today, 15:41
Joined
Aug 23, 2017
Messages
346
I'm confused by your description.
Show us two things - some sample starting data, and the data(not a report) that you expect out.

Formatting the report is secondary to being able to get the data.

I have attached my database. This will act as a combine PO as they are of different fabrication however they have the same delivery date, content and width.
The delivery date will be based on the PO. Lets assume that this PO has lots of color and with the different fabrication hence will have to split into two batch of shipment thus the production will have to input two delivery date for the respective batch
 

Attachments

  • Database for delivery table.png
    Database for delivery table.png
    26.7 KB · Views: 56

Minty

AWF VIP
Local time
Today, 22:41
Joined
Jul 26, 2013
Messages
10,355
You haven't attached anything.
If you need to get to a customers preferred delivery date to an "actually when its really going to happen " delivery date then store the two or three dates against the order.

At the moment it appears that you are not storing the "Promised date" just adding it to the report, which means you can't do anything with it.

It sounds like you need three date fields for your order lines - ExpectedDeliveryDate, CustomerPreferredDate and ActualDeliveryDate. You could then report on them anyway round you need.
 

luzz

Registered User.
Local time
Today, 15:41
Joined
Aug 23, 2017
Messages
346
You haven't attached anything.
If you need to get to a customers preferred delivery date to an "actually when its really going to happen " delivery date then store the two or three dates against the order.

At the moment it appears that you are not storing the "Promised date" just adding it to the report, which means you can't do anything with it.

It sounds like you need three date fields for your order lines - ExpectedDeliveryDate, CustomerPreferredDate and ActualDeliveryDate. You could then report on them anyway round you need.

I have attached, do take a look.
Yes, i do not have a field to store my "Promised date" as i feel that it is not necessary to do so.

In my point of view, the date on the delivery act as a guideline for the production team thats why it will the day will be display 7 days in advance. Besides that, the main purpose of the delivery table is to hsow the delivery date 7 days in advance and the respective color for that specific delivery date and print it out. Thats why i do not store any data for delivery table
 
Last edited:

Minty

AWF VIP
Local time
Today, 22:41
Joined
Jul 26, 2013
Messages
10,355
I have attached, do take a look.
Sorry it wasn't there when I replied.
Yes, i do not have a field to store my "Promised date" as i feel that it is not necessary to do so.
That's why you can't query it. Access can't guess at data that doesn't exist. If you want to use this as a data point you have to store it.
 

luzz

Registered User.
Local time
Today, 15:41
Joined
Aug 23, 2017
Messages
346
Sorry it wasn't there when I replied.

That's why you can't query it. Access can't guess at data that doesn't exist. If you want to use this as a data point you have to store it.

How do i store?

Can you show me an example?
 

Minty

AWF VIP
Local time
Today, 22:41
Joined
Jul 26, 2013
Messages
10,355
Well personally I would add the field to each order line, in fact I don't know where else you could store it that would make sense?

It's no different to any other field that would relate to a specific order line. What if your customer orders Red Yellow Blue at the same time. You know at the point of order and you tell the customer that there is a world shortage of Blue and it will likely be 3 weeks later than the rest of the delivery. You have to store that against the order line, not the order header.
 

Users who are viewing this thread

Top Bottom