Date Adding

[Titch]

Hi, I'm setting a database up to enable me to follow up customer orders after delivery. I have the tables set up and the delivered date as a date/time field along with follow up 1 week, 1 month and 3 month being set as date/time field. I am wanting these to populate automatically based on delivered date so that I can create a report to make follow up easier. However, using the DateAdd doesn't work, I've tried this in a query and in a report. Would anyone be able to help me please. It is a very long time since I was working with Access and I've tried everything I can remember but nothing is working. I have attached a screenshot of my main table to give an idea of what I have set up and want to do.table screen shot.zip

[Bob Fitz]

...... However, using the DateAdd doesn't work, I've tried this in a query and in a report. Would anyone be able to help me please. ....

First of all, telling us that it doesn't work will help nobody. If it worked you wouldn't be asking for help. Posting the Code/Expression that you are using would helpful.

Secondly, it sounds like you want to save these calculated dates to a table which is not usually considered to be best practice. Usually better to do the calculation whenever and wherever it is required.

[ranman256]

use DATEADD("d",3,[eventdate])
(add 3 days. or other time interval: yyyy,m,w,d,h,n

or DATEDIFF("h",date1,date2)
how many hrs different

[Titch]

First of all, telling us that it doesn't work will help nobody. If it worked you wouldn't be asking for help. Posting the Code/Expression that you are using would helpful.

Secondly, it sounds like you want to save these calculated dates to a table which is not usually considered to be best practice. Usually better to do the calculation whenever and wherever it is required.

I have used DATEADD("d",7,[DeliveredDate]) - copy of screenshot attached. When I use this in the query it asks for the delivered date which then populates the field regardless of delivery date with 7 days from the date I enter (previously it didn't do this, it just returned error or blank query results).
I am wanting to use the query - not necessary fill the existing field (although that would be nice) as I can do that manually when I do the follow up - but to be able to create a report that I can run each week to check which customers I need to follow up with.
Hope this clarifies what I'm wanting to do.query screen shot.zipquery screen shot.zip

[Titch]

Thank you, I tried that and yesterday did not work at all, this morning it partially works but if you see the screen shots I added in reply to Bob, it fills dates even for blank fields

[Welshgasman]

If access asks for DeliveredDate, then that field does not exist or you have misspelled it?
As you have supplied a random date, then the date calculated will always be the same? 7 days later than what you typed in.

I have not looked at the pic, as i do not want to go through the rigmarole of downloading a zip file, unzipping just to be able to view the pic.
You can attach the pic directly, and I cannot imagine the pic is huge, so size considerations should not matter.?

[Titch]

OK, this is the table the query is based on

and this is the query I have used

[pbaldy]

Post 7 was moderated, I'm posting to trigger email notifications.

The attachments do not appear to have come through. Try attaching them again.

[Titch]

Hopefully both will show now

[Welshgasman]

OK [DeliveredDate] is actually [Delivered Date]

Not a great idea to have spaces in fieldnames.?

[Minty]

Your table design is very wrong - you have multiple repeating fields and other things inherent when someone has "Spreadsheeted" a table.
Your orders table should have child table of order Lines, that way if you have 1 product or 100 the design accommodates it.

Samples and follow up's should equally be child records or seperate tables linked back to the main top level record.

Have a read up about normalisation and converting spreadsheets into Access.

[Titch]

thank you, I'll look at amending

[Titch]

Thank you, I'll have a look at that. With the existing format, will I be able to convert it into the right format easily rather than re-entering the information

[Minty]

As a one off process, yes just take a bit of fiddling with.

All the follow up dates can be calculated from the order or delivery date, so as others have stated don't store those.

[Titch]

As a one off process, yes just take a bit of fiddling with.

All the follow up dates can be calculated from the order or delivery date, so as others have stated don't store those.

Thanks, I'll look at updating