Convert Measurement in Access VBA and update the same Access table

[WuJu]

Howdy,

I need to convert some records (not all) in my access table to match with same unit.

I have a access table with mismatch in unit: some in kilowatt and some in megawatt.

I want to unify unit in megawatt and update the table.
The following is the access table I have with fields pID, Date, 1, 2,,3, ....24 (each number stands for hour).

AS you see, pID 1 has unit in kilowatt and pID 2 has unit in megawatt for each hour field. I want to right VBA to convert kilowatt of each hour(1 through 24) in pID = 1 to megawatt: such as [1]/[1]/1000, [2]=[2]/1000,.... [24]=[24]/1000.

The following is part of my Access table.

pID Date 1 2 3 4 ......... 24
1 1/1/2010 2500 2200 1800 2300 ..........2200
1 1/2/2010 1800 1200 1700 1300 ..........2100
.......

1 11/28/2010 2300 2100 2800 2600 ..........2500
2 1/1/2010 2.7 2.2 1.8 1.3 ............ 2.9
2 1/2/2010 2.4 2.8 1.9 1.7 ............ 2.4
........


So far, I wrote the VBA below to work out as what I want to see, but it is not working. Please help me out.

Regards,

Wuju




FYI, the following is my incomplete VBA code.

Private Sub convertKWh2MWh()

Dim pID As Integer

For EOF 'I am not sure what I need to put here (may use DO UNTIL EOF)

Select Case pID
Case 1
CurrentDb.Execute "update importMeter_t set [1]='" & [1] / 1000 & ""
CurrentDb.Execute "update importMeter_t set [2]='" & [2] / 1000 & ""
.....
CurrentDb.Execute "update importMeter_t set [24]='" & [24] / 1000 & ""

Case 3
CurrentDb.Execute "update importMeter_t set [1]='" & [1] / 1000 & ""
...

End Select

Next

End Sub

 

[SpentGeezer]

Private Sub convertKWh2MWh()
 
Dim pID As double 'or long or whatever is needed....
 dim rst as recordset
set rst = currentdb.openrecordset("tbl_Name")
rst.movefirst
do until rst.EOF
pID = rst!1
pID = pID/1000
rst!1 = pID

pID = rst!2
pID = pID/1000
rst!2 = pID

pID = rst!3
pID = pID/1000
rst!3 = pID

'etc........
pID = rst!24
pID = pID/1000
rst!24 = pID

rst.movenext
loop

msgbox "Done"
end sub

 

[boblarson]

And just for your information, just in case you really do have a field named DATE - you should rename it because DATE is an Access Reserved Word and function and using it as an object or field name can cause strange things to happen in your database.

 

[WuJu]

Thank Spentgeezer for VBA code and boblarson for tip.

Spentgeezer's VBA code will definately help me for converting.

wuju