Convert Measurement in Access VBA and update the same Access table 1

[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

 

[PHV]

Why not a single Update query (SQL code)?
UPDATE importMeter_t
SET pID=2,[1]=[1]/1000,[2]=[Z]/1000, ...,[24]=[Z4]/1000
WHERE pID=1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wuju]

Hi PHV,

Thank you for your advise.
It looks like working well except that it always have integer number. I want to keep decimal point (up to .001)
Is it possible to convert KWh to MWh in three decimal?
For example, if 1234 KWh then I want to see 1.234 MWh.

Please let me know if you have idea.

Regards,

Wuju

 

[PHV]

What is the data type of the 1,2,...,24 fields ?
Should be SINGLE, DOUBLE or DECIMAL in order to keep a fractional part.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wuju]

the data type of the 1,2,...,24 fields is SINGLE

 

[wuju]

by the way, at least I have solution for having backward.

MWh to KWh and [1]/[1]*1000.

If you can help me in KWH to MWh in three decimal, it is good, otherwise I change to Wh to KWh and [1]/[1]*1000.

Thank you for your great help.

regards,

wuju

 

[PHV]

the data type of the 1,2,...,24 fields is SINGLE
So, updating such field should keep the fractional part.
Seems like a display format issue.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

wuju,
I the future, please keep your question to a single thread, not multiple threads in different forum thread705-1629467.

Duane
Hook'D on Access
MS Access MVP

 

[wuju]

Hello PHV,

My data type in Access 2007 is just NUMBER, so I am not sure it is Single or integer, or something else.

My answer to you as Single is what I actually want it to be.

I scrolled down to choose type of data, Access 2007 gives the choice of number, autonumber, text,....., but not a single or integer.
Do you know how to set as Single?

Beside this question, I have general question for you.
It seems like it can code as VBA or sql. What case VBA prefers over sql and vise versa?

thank you for your help.

wuju

 

[PHV]

Single is what I actually want it to be
Look at the field size ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wuju]

I got it.
Thanks PHV.

As you my mentor, could you help me general question below?
It seems like it can code as VBA or sql. What case VBA prefers over sql and vise versa?

thanks for your great help.

wuju