multiple update query

[Guest]

hi all, can anyone tell me how to write a multiple update query in ONE
query. e.g i write a query
UDATE ROUTINDB SET ROUTINDB.[NEXT SCHEDULE DATE] =
DateAdd("m",3,[ROUTINDB]![SCHEDULE DATE])
WHERE (((ROUTINDB.FREQ)="3M"));

but i want to add more update query in same query

 

[JK]

Zaheer hi,

If your WHERE clause is applicable to all update, add more fields updates
separated, by a comma:

UDATE ROUTINDB SET ROUTINDB.[NEXT SCHEDULE DATE] =
DateAdd("m",3,[ROUTINDB]![SCHEDULE DATE]), RoutinDb.[AnotherField]=
someting, Routin. [More]=somethingElse
WHERE (((ROUTINDB.FREQ)="3M"));

Or use the query greed to accomplish the same thing

Regards/JK


| hi all, can anyone tell me how to write a multiple update query in ONE
| query. e.g i write a query
| UDATE ROUTINDB SET ROUTINDB.[NEXT SCHEDULE DATE] =
| DateAdd("m",3,[ROUTINDB]![SCHEDULE DATE])
| WHERE (((ROUTINDB.FREQ)="3M"));
|
| but i want to add more update query in same query
| --
| Zaheer
| Acesss Database Designer
| Planning Supervisor

 

[Guest]

thanks for tip...but i need more information like i have made 2 query

first one
UDATE ROUTINDB SET ROUTINDB.[NEXT SCHEDULE DATE] =
DateAdd("m",3,[ROUTINDB]![SCHEDULE DATE])
WHERE (((ROUTINDB.FREQ)="3M"));

2nd
UDATE ROUTINDB SET ROUTINDB.[NEXT SCHEDULE DATE] =
DateAdd("Y",1,[ROUTINDB]![SCHEDULE DATE])
WHERE (((ROUTINDB.FREQ)="1Y"));

pls can you help to write correctly these 2 above query in one query, i mean
i want to run both query from one place

 

[JK]

Zaheer,

Use nested IIf Statement instead of your WHERE clauses:


'(Untested)

UDATE ROUTINDB SET ROUTINDB.[NEXT SCHEDULE DATE] =
IIf(RoutinDB.Freq]="3M",DateAdd("m",3,[ROUTINDB]![SCHEDULE DATE]),
IIf(RoutinDB.FREQ="1Y",DateAdd("Y",1,[ROUTINDB]![SCHEDULE DATE]),
[Routin.Db.[Next Schdule Date]))

Regards/JK







| thanks for tip...but i need more information like i have made 2 query
|
| first one
| UDATE ROUTINDB SET ROUTINDB.[NEXT SCHEDULE DATE] =
| DateAdd("m",3,[ROUTINDB]![SCHEDULE DATE])
| WHERE (((ROUTINDB.FREQ)="3M"));
|
| 2nd
| UDATE ROUTINDB SET ROUTINDB.[NEXT SCHEDULE DATE] =
| DateAdd("Y",1,[ROUTINDB]![SCHEDULE DATE])
| WHERE (((ROUTINDB.FREQ)="1Y"));
|
| pls can you help to write correctly these 2 above query in one query, i
mean
| i want to run both query from one place
| --
| Zaheer
| Acesss Database Designer
| Planning Supervisor
|
|

 

[JK]

Oops

UDATE ROUTINDB SET ROUTINDB.[NEXT SCHEDULE DATE] =
IIf(RoutinDB.Freq="3M",DateAdd("m",3,[ROUTINDB].[SCHEDULE DATE]),
IIf(RoutinDB.FREQ="1Y",DateAdd("yyyy",1,[ROUTINDB].[SCHEDULE DATE]),
RoutinDB.[Next Schdule Date]))

'(use "yyyy" in DateAdd() to increase by 1 year)

Regards/JK

| Zaheer,
|
| Use nested IIf Statement instead of your WHERE clauses:
|
|
| '(Untested)
|
| UDATE ROUTINDB SET ROUTINDB.[NEXT SCHEDULE DATE] =
| IIf(RoutinDB.Freq]="3M",DateAdd("m",3,[ROUTINDB]![SCHEDULE DATE]),
| IIf(RoutinDB.FREQ="1Y",DateAdd("Y",1,[ROUTINDB]![SCHEDULE DATE]),
| [Routin.Db.[Next Schdule Date]))
|
| Regards/JK
|
|
|
|
|
|
|
| || thanks for tip...but i need more information like i have made 2 query
||
|| first one
|| UDATE ROUTINDB SET ROUTINDB.[NEXT SCHEDULE DATE] =
|| DateAdd("m",3,[ROUTINDB]![SCHEDULE DATE])
|| WHERE (((ROUTINDB.FREQ)="3M"));
||
|| 2nd
|| UDATE ROUTINDB SET ROUTINDB.[NEXT SCHEDULE DATE] =
|| DateAdd("Y",1,[ROUTINDB]![SCHEDULE DATE])
|| WHERE (((ROUTINDB.FREQ)="1Y"));
||
|| pls can you help to write correctly these 2 above query in one query, i
| mean
|| i want to run both query from one place
|| --
|| Zaheer
|| Acesss Database Designer
|| Planning Supervisor
||
||
|
|