Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2010

    Question Unanswered: Convert Measurement in Access VBA and update the same Access table


    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.



    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


    End Sub

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if this is a one off then you would be better off issuing a simple SQL UPDATE query.
    the problem is how do you identify which rows are currently in Kilowatts, and which rows are currently in Megawatts

    update mytable set mycolumn = mycolumn/1000 WHERE mycolumn =insert whatever means you cna work out its currently in kilowatts.
    ...presumably thats going tobe if the current value is say greater than 1000

    as to why your current code isn't working...
    what do you think set [1]. set[2] and set[24] are supposed to represent... please say thats not your column names... please, no I really mean please.......

    what does that sql look like when you step through the code?
    often there can be a substantial mismatch between what you think the SQL is saying and what it actually says
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2010
    It is.

    I didn't design the table,
    I got to copy data from Excel to Access and do change format stuff.
    That's why I named a table as importMeter_t, which data imported from Excel.
    I have to dealing with what I have in Excel to Access.

    I will change table format to be pID mDate, mHour, MWh once I change KWh to MWh. So at that time, all the [1] ....[24] will be gone and just simple field mHour.

    thank you for your input, and it converts from KWh to MWh.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts