update query for plus numbers

[Konstadin]

Hi, I want to make an update query in order to plus every
Month (+1) in one Field. The Form's name is "Chief" and
the Field's name is "Total".
For example...this month there is the number 3 in the
Field, the next month I must put the number 4 manual,
next month I must put 5,etc.
I want this happen auto every month. How I can do' it? I
need to write something to the Criteria? I need to make
except the update query and Macro in order to load the
query? And How?
Of course if there is another way I can use it.

Thanks and good morning to everyone.

 

[Michel Walsh]

Hi,



In the designer, add a computed expression:

Month( YourDateTimeField )

and under it, add the criteria:

= Month( Now() )


Change the query from a SELECT query to an UPDATE query, and update your
date_time field to:


DateAdd("m", 1, yourDateTimeField )


Run the query to update the datetime field by adding one month to it (for
those records being in the actual month, so, you can run the query multiple
times, per month, without problems).



Hoping it may help
Vanderghast, Access MVP

 

[Konstadin]

Hi MICHEL and thanks you answer to me, but I still can
not do it, you write to add a computed expression in the
designer...you mean in the designer of a new SELECT query?
And the Month (YourDateTimeField) is like Month ([Chief]!
[Total])? And where I must write this?
The (= Month ( Now() ) ) I must write in the criteria?
If I put the DateAdd ("m", 1, yourDateTimeField) in the
criteria I have error...
If you can write step by step you will help me.

Thanks for all.

I use WinXP with OfficeXP

 

[Michel Walsh]

Hi,


In a new query, indeed. We can start with a standard SELECT type, and we
will have to change it to an UPDATE one, to get UpdateTo line, in the grid.

If your field with the "month" value is called Total, and if the table
is called chief, then yes, the computed expression, first line, new column,
would be

Month( Chief.[Total] )


The idea is to get the month value. As example, Month( Now ) would return
9 if you are in September. So, Month( someDate ) returns the month that
correspond to the date supplied as argument. Here, if Total is holding a
date, then Month( [Total] ) returns the month of that date.


Under the same column, at the criteria line, type

= Month( Now( ) )

that means we want to limit the update (to come in a moment) to those
records having their month equal to this actual month,


Always under the same column, at the UpdateTo line (you should have such a
line if you have change the SELECT query type to an UPDATE query type), you
type

DateAdd("m", 1, Chief.[Total] )


here, again, it is assuming that Total hold the date. We update it adding 1
month to it, so, since it is actually in September, by virtue of the
criteria, that will update it to a date of the same day, but in October...
which, I assume, was you originally asked.



Hoping it may help,
Vanderghast, Access MVP

Hi MICHEL and thanks you answer to me, but I still can
not do it, you write to add a computed expression in the
designer...you mean in the designer of a new SELECT query?
And the Month (YourDateTimeField) is like Month ([Chief]!
[Total])? And where I must write this?
The (= Month ( Now() ) ) I must write in the criteria?
If I put the DateAdd ("m", 1, yourDateTimeField) in the
criteria I have error...
If you can write step by step you will help me.

Thanks for all.

I use WinXP with OfficeXP

-----Original Message-----
Hi,



In the designer, add a computed expression:

Month( YourDateTimeField )

and under it, add the criteria:

= Month( Now() )


Change the query from a SELECT query to an UPDATE query, and update your
date_time field to:


DateAdd("m", 1, yourDateTimeField )


Run the query to update the datetime field by adding one month to it (for
those records being in the actual month, so, you can run the query multiple
times, per month, without problems).



Hoping it may help
Vanderghast, Access MVP






.