Need to change the format of the work field

[Biggles]

Hello

I am building a series of large projects in one MPD project database. I am
familiar with Access VBA, and that might be causing me some problems. Enough
background, here is my question.

The driving factor for all my tasks is work. I have made each task fixed
work, and I am importing tasks that have 150 - 1000 hours of work. If I try
to import that into the work column of the MSP_TASKS table through VBA with
this code:

ssql(11) = "INSERT INTO MSP_TASKS (" & _
"PROJ_ID,TASK_UID,TASK_ID,TASK_NAME,TASK_OUTLINE_LEVEL," & _
"EXT_EDIT_REF_DATA,TASK_WBS,task_type,task_work,task_is_milestone) " & _
"VALUES (" & tr_projid & "," & _
rs_proj(3)!next_taskuid & "," & _
rs_proj(3)!next_taskid & "," & _
"'" & rs_plan(3)!ae_abbv & " " &
v_taskname & "'," & _
rs_plan(4)!outline_level & "," & _
"1,'" & WBS_PART(6) & "'" & ",2," & _
HOURS * 60 * 1000 & "," &
v_milestone & ")"

I am using the HOURS * 60 * 1000 to store the work in the way I understand
project wants it. However, I keep getting an overflow error. How can I
change the format of the work field for every task in my project database (7
different projects) in VBA?

 

[John]

Hello

I am building a series of large projects in one MPD project database. I am
familiar with Access VBA, and that might be causing me some problems. Enough
background, here is my question.

The driving factor for all my tasks is work. I have made each task fixed
work, and I am importing tasks that have 150 - 1000 hours of work. If I try
to import that into the work column of the MSP_TASKS table through VBA with
this code:

ssql(11) = "INSERT INTO MSP_TASKS (" & _
"PROJ_ID,TASK_UID,TASK_ID,TASK_NAME,TASK_OUTLINE_LEVEL," & _
"EXT_EDIT_REF_DATA,TASK_WBS,task_type,task_work,task_is_milestone) " & _
"VALUES (" & tr_projid & "," & _
rs_proj(3)!next_taskuid & "," & _
rs_proj(3)!next_taskid & "," & _
"'" & rs_plan(3)!ae_abbv & " " &
v_taskname & "'," & _
rs_plan(4)!outline_level & "," & _
"1,'" & WBS_PART(6) & "'" & ",2," & _
HOURS * 60 * 1000 & "," &
v_milestone & ")"

I am using the HOURS * 60 * 1000 to store the work in the way I understand
project wants it. However, I keep getting an overflow error. How can I
change the format of the work field for every task in my project database (7
different projects) in VBA?

Biggles,
I haven't done any work directly with Project's database, but I do have
a basic comment. Project stores all time based data (e.g. work) in
minutes so you are correct in thinking you need to convert hours to
minutes before dumping it into Project. But I don't understand the extra
"1000" factor that you are using. That may very well be why you are
getting an overflow.

John
Project MVP

 

[Biggles]

This is taken from the PJDB.htm file:

Because duration, work, rate, and cost values can be displayed using
different units, Project saves each using a standard multiple:

Duration values are saved as minutes * 10. Eight hours would be saved as
4800 (that is, 8*60*10).
Work values are saved as minutes * 1000. Eight hours would be saved as
480000 (that is, 8*60*1000).
Rate values are saved as units per hour. For example, fifteen dollars an
hour would be saved as 15.
Cost fields are saved as units * 100. For example, seventy dollars and
twenty-five cents would be saved as 7025.
Note All formats are valid for timephased data units except y, or year.
Year is only valid for Cost Rate.

However, that was not my problem. My problem was that I was using a hours
field type of integer, where I should have been using double. Works now.

 

[John]

This is taken from the PJDB.htm file:

Because duration, work, rate, and cost values can be displayed using
different units, Project saves each using a standard multiple:

Duration values are saved as minutes * 10. Eight hours would be saved as
4800 (that is, 8*60*10).
Work values are saved as minutes * 1000. Eight hours would be saved as
480000 (that is, 8*60*1000).
Rate values are saved as units per hour. For example, fifteen dollars an
hour would be saved as 15.
Cost fields are saved as units * 100. For example, seventy dollars and
twenty-five cents would be saved as 7025.
Note All formats are valid for timephased data units except y, or year.
Year is only valid for Cost Rate.

However, that was not my problem. My problem was that I was using a hours
field type of integer, where I should have been using double. Works now.

Biggles,
I stand corrected on my response. I'm used to working with the Project
database through VBA. I guess direct queries to/from the Project
database are handled a little differently. Nonetheless, you figured out
the problem. Now I'm a little better informed and you are up and
running. We both win.

By the way, thanks for the feedback.
John