Add 1 Each Time

[DemonHub]

I made a button with a click event and in this event I want to add 1 to "Days of waiting" for each rows in a table with waiting in the field "Status" , each time I click on the button.

I don't know how to go in the table with VBA code and add 1 to the rows with the criteria.

The button is not push in the week-end so I can't do something that add 1 every day.But if something is done automaticly every day except Saturday and Sunday without pressing a button , this thing is probably better but I don't know how to do that either.

If you find a way to do that or something else , your help will be fantastic.

 

[lespaul]

In the record that contains the field 'Status', is there a date field? Why bother storing the # of days waiting when you can get it in a query when you need it?

SELECT Field1, DateDiff("d", YourDateField, Date()) As [Days of Waiting] FROM tablename WHERE Status = "Waiting"

no update required, you get on the fly results whenever you need them. Storing calculated fields breaks normalization rules and should be avoided.

Leslie

 

[DemonHub]

I need to stock the # of day waiting because I'am doing report with it and It's not the Date of Today - the first date . It's not a calculated fields. The + 1 is only added when the status is "Waiting

 

[TomCologne]

There are several options, the easiest way is probably:

1. Create an update query that adds one day.

2. Have the wizard create a button that runs this query.


However, a well designed database should hardly require such an update, it should actually be avoided.

Do some research: "store calculated values in tables" or generally "Normalization".

Under normal circumstances the status "Waiting" shouldn't even be necessary.

Example:

In a database for orders you'd have an order date and a delivery date.

As long as there is no delivery date, you would calculate the "Days of waiting" with a query/expression like

IIf(IsNull([DeliveryDate]), Date()-[OrderDate],"Delivered")

or something similar.

You might want to review your table structure,



TomCologne

PS: Leslie & you posted while I was still typing. Don't fool yourself, it's still a calculated field, the "Waiting" criterion doesn't change the fact.