Change Field Format using VBA

[Swatskeb]

I have a table that contains information along with a field named "Date Input" and then a calculated field called "Due NLT Date". New records are appended to this table daily. The calculated "Due NLT Date" field is simply "[Date Input]+5".



In some unforeseen circumstances the "Due NLT Date" field needs to be extended to 6 or 7 days from the "Date Input". I am trying to find a way adjust the number of days using a command button/VBA getting the desired number of days either from an input box or from a txt box on a form.

I have been playing with using SQL ALTER TABLE in VBA but can't get it to work. If someone knows how to write this please walk me through it, Thanks.

I'd image it would look something like this but I have no idea how to write it correctly:

RunSQL ALTER TABLE "table" FIELD "field" to calculated "[Date Input]+ & input box "Enter number of days""

[June7]

If you do manage to modify the calculated field, then the calc will be reflected in all records, not just the ones subject to the 'unforeseen circumstances'. Incorporating a routine into database that regularly modifies design is not a good idea.

Do this dynamic calc in a query or in a textbox, not a calculated field in table.

[Swatskeb]

If you do manage to modify the calculated field, then the calc will be reflected in all records, not just the ones subject to the 'unforeseen circumstances'. Incorporating a routine into database that regularly modifies design is not a good idea.

Do this dynamic calc in a query or in a textbox, not a calculated field in table.

That is correct I would like all records in the table to have the Due NLT Date adjusted by a certain number of days when it is deemed necessary. Records are being appended daily for example on the 1st, 2nd, 3rd of a month and due on the 6th, 7th, 8th respectively, I want the due date pushed out two days to the 8th, 9th, 10th. It is fine that all the records change at the same time because they all require additional days, when it is deemed that the extra time is not needed the Due date can be backed up incrementally to the standard five days, I am currently doing it manually when needed, however I'm am moving locations and need some sort of automated process/button so it can easily be changed by the average Office Product user.

The dates limit the amount of time the records are view-able in the front end of the database however I need to be able to adjust the number of days in all the Front End Forms, Queries, etc... as well as the back end simultaneously. The only way to adjust everything at one point is to adjust it once from the back end so that it reflects in everything that pulls from it. I can't go in and adjust it in four different places every time it needs to be changed like over a holiday for example.

[June7]

I doubt the SQL ALTER TABLE action can modify a calculated field. Calculated field is unique to Access and SQL probably doesn't have parameters to handle.

Might be able to use VBA code and Tables collection to modify but not sure. Again, I advise not to design this.

Use a query with the dynamic calc as the source for all those forms, reports, other queries.

[John_G]

Hi -

Why not put another field into your table to indicate the number of days to increment by? Then the calculated field could use that instead of the hard-coded "5". Changing the increment then becomes easy: "Update tablename set increment = newvalue" with no where clause so all records are updated.

This also gives you the flexibility of having different increment values throughout the table.

John

[Swatskeb]

I got it figured out not the same as I originally was asking but...I just created a field call NUMDAYS and made an update query that updates everything in that field based on the number being entered into a txt box on the form then a command button that calls a function to turn warnings off and run query then turns warnings on and closes and opens the form again the caluclated field now reades [DATE INPUT]+[NUMDAYS]