changing date automatically in a table field

[stressedbrian]

Hi there,

I have a table of customers and dates when their orders were placed. Is their a way of having a field within said table that shows the order date plus 3 weeks (ie when the order is due for delivery). It would be good if this can be done automatically as soon as the first date is entered in the first field without the operator having to do anything.

 

[SeanD]

I'm not an expert, but maybe you could do it with some code.
How do you enter the date?
Like dd/mm/yyyy?
In VBA there are function who takes characters out of a string from a textbox.
Put them all into variables. (dd = day, mm = month, yyyy = year)
Add 3 weeks ( = 21 days) to the date.
U need to check the months also (January has 31 days , ...) and adapt it.

That's how I should do it ^^
(Sorry for bad English ^^)

 

[stressedbrian]

Hi thanks for that, is there any way of making it a bit more simple than that for example; I have read a few articles on the DateAdd function I have tried using this as the line in the Defaultvalue in the properties of my new field with the different date but its not working says 'TypeMismatch' in the data value
code reads

DateAdd("ww",3,"StartDate")

 

[SeanD]

Like I said, I'm not an expert ^^
Maybe this will help.
http://www.techonthenet.com/access/functions/date/dateadd.php

 

[Galaxiom]

Storing the extra date is a breach of normalization rules because it is a derived value. It should be calculated each time it is required in a query or form.

It can be done as a calculated field if you are using Access 2010. This feature is intended for web-based database but should otherwise be avoided.

 

[stressedbrian]

So am I correct in saying then that I can't have use the dateadd function in a field in a table if the source field for the dateadd is blank or can I just not use it generally in atable??

I have tried using a report instead but the expression still doesn't work when put in the control source where am I going wrong!!??

PS galaxiom if you don't mind me asking where are you in NSW roughly just out of interest? (I'm in scotland but used to live in Canberra, then Wagga then Narranderra).

 

[Galaxiom]

So am I correct in saying then that I can't have use the dateadd function in a field in a table if the source field for the dateadd is blank or can I just not use it generally in atable??

A table is not like a spreadsheet. It is to store data and does not hold calculations. (Except in A2010 but I have never used it.)

I have tried using a report instead but the expression still doesn't work when put in the control source where am I going wrong!!??

What do you have as the ControlSource?

PS galaxiom if you don't mind me asking where are you in NSW

Hinterland behind the Far North Coast. Best place on the planet.

I can see Queensland from here but at a safe distance.

 

[stressedbrian]

The control Source reads:

=DateAdd("d",33,[StartDate])

for some reason think it is now working am I correct in saying this should add 31 days (4 weeks 3 days) onto my start date?

 

[SeanD]

try to add 21 days in stead of 3 weeks
Maybe that should do the trick ^^