Update query

[confused_Pete]

Hello,

Im looking into a way of using an update query to take a date from a field add a certain number of days to that date and then update another field with that date.

here is my select statement, how do i modify this to actually update the Date

SELECT tblorder_no.Date_ordered, DateAdd("d",3,[tblorder_no]![date_ordered]) AS Date_expected
FROM tblorder_no;

(created using the QBE)

Any help would be appreciated, (probably just me being dense again)

Pete

 

[Rich]

You should not store this, it's a calculated field which can be returned at any time via a query, or a calculated field on a form/report

 

[confused_Pete]

Cheers for the reply, but i do need to calculate and store this field. Is there a way to store this information, as each time a date is entered for a new order i need to add 3 days to this date and enter that information into the date_expected field. is there a way to do this, or should i not have that field as part of my table and purely store this as a calculated field?

Cheers

Pete

 

[neileg]

If the expected date is always 3 days on from the order date, then you should not store this. However, if the expected date is liable to amendment, then you should store the expected date.

You can use an update query to fill in this date (test to see if the field is empty, if it is then add your calculated value).

 

[confused_Pete]

This brings me right back to my first question which was, 'How do i create an update query to update the date field?' (please refer to my first post). Any help on how to do this would be appreciated. (as regardless of whether concerned members of the forum think i should be doing this or not is irrelevant). I still think i need to do this. However have been unable to get an update query to update a date field based on another field within the same table with that information.

Any help is appreciated.

Cheers

Pete

 

[FoFa]

UPDATE tblorder_no
SET tblorder_no.Date_expected = DateAdd("d",3,[tblorder_no]![date_ordered]
WHERE your criteria for doing this

 

[confused_Pete]

many thanks FoFa, I had an error in my update query SQL.

many thanks for your help, and for the other suggestions that were put forward.

Cheers

Pete

 

[KevinM]

Add me to the 'You should not store this in a table' list.

Why do you need to store it?