Can I calculate a field in table & update in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Can I calculate a field in table & update in query

Author

Topic

itchocks
Starting Member

44 Posts

Posted - 2005-09-19 : 07:12:01

Hi,

i am using vb6 and the database is in access, i have fields with following datatype in the table

Field DataType

AreaName Text
Equipment Text
NextTIDate Date/Time
Duration Number
CompletionDt Date/Time

when the user choose a date in the MonthView i update the choosen date to the NexTI Date by the Following query.

update EqMaster set NextTIDate =#" & MonthView1.Value & "# Where AreaName='" & ArNm & "' And NextTiDate=#" & OldTiDt & "#"

i need to check if the Duration field is empty it should not do anything.

For example If the Duartion field has the value 10 and the NextTiDate has the value 1/15/2005 then when i update the table using the update statement. the NextTiDate (i.e the newly updated MonthView1.Value) should be added with the Duration Field and stored in the CompletionDt Field.

Is it possible to do it in the same query what i written above, if the Duration Field is empty then nothing should be stored in the Duration field.

I prefer to do the job in the same Update query, instead of opening a recordsetset and check whether value is there or not in the Duration Field and then updating the CompletionDt.

Chock

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-09-19 : 08:31:23

can you give us sample input data and matching expected results?

itchocks
Starting Member

44 Posts

Posted - 2005-09-20 : 03:06:25

hi,

assume the table by default contains the below values, some records will have values & some will not.

quote:

AreaName NextTiDt Duration CompletionDt

Area1 1/1/2005 10 1/11/2005
Area2 3/10/2005 10
Area3 3/10/2005

if i am going to update NextTiDt Field where the AreaName is "Area 1"
with the new MonthView1.Values = 1/1/2006

then the CompletionDt should be updated for the Record "Area1" as
1/11/2006 for 1st record

for second record if MonthView1.Value = 10/10/2005 then CompletioDt for the Record Area2 should be updated as 10/20/2005

for 3rd record if MonthView1.Value = 10/10/2005 then then the 3 rd record should be as below

AreaName NextTiDt Duration CompletionDt

Area3 3/10/2005 3/10/2005

Thankyou,
Chock.

Chock

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources