{"id":153,"date":"2004-07-09T15:33:24","date_gmt":"2004-07-09T13:33:24","guid":{"rendered":"http:\/\/www.marblestation.com\/blog\/?p=153"},"modified":"2012-04-26T19:06:38","modified_gmt":"2012-04-26T17:06:38","slug":"ms-access","status":"publish","type":"post","link":"https:\/\/www.marblestation.com\/?p=153","title":{"rendered":"Transformaci\u00f3n de unidades en MS Access"},"content":{"rendered":"<p>He escrito c\u00f3digo nuevo para intentar transformar un campo &#8220;string&#8221; en uno &#8220;long&#8221; y tratar las unidades, es decir, por ejemplo si tenemos un campo con &#8220;1000 kg&#8221; que se transforme simplemente en 1 y la unidad es la misma para todos los registros (toneladas). Hay una funci\u00f3n que la he cogido de una web donde ofrecen peque\u00f1as <a href=\"http:\/\/www.peterssoftware.com\/strfn.htm\">funciones para el tratamiento de strings<\/a>. A continuaci\u00f3n todo el c\u00f3digo:<\/p>\n<p><!--more--><\/p>\n<pre>\r\nOption Compare Database\r\n\r\n' ExecuteQuery: Realitza una modificaci\u00f3 a la BD\r\n' Provar amb:\r\n'  ExecuteQuery \"INSERT INTO CamaraEnvasadoPreenvasado VALUES ('test', 'borrar')\"\r\nSub ExecuteQuery(strSQL As String)\r\n   Dim cnn          As ADODB.Connection\r\n   Dim lngAffected  As Long\r\n   \r\n   ' Open the connection.\r\n   Set cnn = CurrentProject.Connection\r\n\r\n   ' Execute the query.\r\n   cnn.Execute CommandText:=strSQL, _\r\n               RecordsAffected:=lngAffected, _\r\n               Options:=adExecuteNoRecords\r\n\r\n   'Debug.Print \"Records Affected = \" & lngAffected\r\n   \r\n   ' Close connection and destroy object variables.\r\n   cnn.Close\r\n   Set cnn = Nothing\r\nEnd Sub\r\n\r\n' TransformDB: Convierte los campos \"volumen\", \"produccion_por_dia\"\r\n' y \"Camara_frigorifica\" de string a integer, realizando las\r\n' conversiones necesarias y descartando valores incorrectos\r\n' ** Tabla FrutasHortalizas **\r\n' \"volumen\" -> toneladas\r\n' \"produccion_por_dia\" -> toneladas\/dia\r\n' \"Camara_frigorifica\" -> m3\r\n' \"Num_Asocidos\" -> quitamos coletilla \"socios\" y dejamos\r\nSub ConvertDB()\r\n    Dim cnnDB As ADODB.Connection\r\n    Dim recordSt As New ADODB.Recordset\r\n    Dim strSQL As String\r\n    Dim idSuscriptor As String\r\n    Dim i As Integer\r\n    Dim volumen As String\r\n    Dim produccion As String\r\n    Dim camara As String\r\n    Dim NumAsociados As String\r\n    Dim volumenFinal As Long\r\n    Dim produccionFinal As Long\r\n    Dim camaraFinal As Long\r\n    Dim NumAsociadosFinal As Long\r\n    \r\n   \r\n    ' Connectar a la BD actual:\r\n    Set cnnDB = CurrentProject.Connection\r\n\r\n    ' Obtenim tots els registres que tenen alguna\r\n    ' dada a algun dels camps que tractem\r\n    'strSQL = \"SELECT * FROM Frustas_Hortalizas WHERE NOT (volumen = '') OR NOT (produccion_por_dia = '') OR NOT (camara_frigorifica = '') \"\r\n    strSQL = \"SELECT * FROM Frustas_Hortalizas\"\r\n    With recordSt\r\n        Set .ActiveConnection = cnnDB\r\n        .CursorType = adOpenKeyset\r\n        .CursorLocation = adUseClient\r\n        .LockType = adLockOptimistic\r\n        .Open strSQL\r\n    End With\r\n    \r\n    ' Tractar els camps i actualitzar taula\r\n    i = 0\r\n    If Not recordSt.EOF Then\r\n        recordSt.MoveFirst\r\n    End If\r\n    Do While Not recordSt.EOF\r\n        idSuscriptor = recordSt.Fields(\"Numero de suscriptor\").Value\r\n        \r\n        If Not IsNull(recordSt.Fields(\"Volumen\").Value) Then\r\n            volumen = recordSt.Fields(\"Volumen\").Value\r\n            volumenFinal = convertirUnidades(GetFirstWord(volumen), GetLastWord(volumen))\r\n            'Debug.Print \"volumen \" & volumenFinal\r\n        Else\r\n            volumenFinal = 0\r\n            'Debug.Print \"zero \" & volumenFinal\r\n        End If\r\n               \r\n        If Not IsNull(recordSt.Fields(\"Produccion_por_dia\").Value) Then\r\n            produccion = recordSt.Fields(\"Produccion_por_dia\").Value\r\n            produccionFinal = convertirUnidades(GetFirstWord(produccion), GetLastWord(produccion))\r\n        Else\r\n            produccionFinal = 0\r\n        End If\r\n        \r\n        If Not IsNull(recordSt.Fields(\"Camara_frigorifica\").Value) Then\r\n            camara = recordSt.Fields(\"Camara_frigorifica\").Value\r\n            If (GetLastWord(camara) = \"m3\") Then\r\n                camaraFinal = CLng(GetFirstWord(camara))\r\n            Else\r\n                camaraFinal = 0\r\n            End If\r\n        Else\r\n            camaraFinal = 0\r\n        End If\r\n        \r\n        If Not IsNull(recordSt.Fields(\"Num_asociados\").Value) Then\r\n            NumAsociados = recordSt.Fields(\"Num_asociados\").Value\r\n            If (GetLastWord(NumAsociados) &#60;> GetFirstWord(NumAsociados)) Then\r\n                NumAsociadosFinal = CLng(GetFirstWord(NumAsociados))\r\n            Else\r\n                NumAsociadosFinal = CLng(NumAsociados)\r\n            End If\r\n        Else\r\n            NumAsociadosFinal = 0\r\n        End If\r\n        \r\n        'If Not IsNull(recordSt.Fields(\"N_trabajadores\").Value) Then\r\n            'NTrabajadores = recordSt.Fields(\"N_trabajadores\").Value\r\n            'If (GetLastWord(NTrabajadores) &#60;> GetFirstWord(NTrabajadores)) Then\r\n                'NTrabajadores = CLng(GetFirstWord(NTrabajadores))\r\n            'End If\r\n        'Else\r\n            'NTrabajadores = 0\r\n        'End If\r\n        \r\n        'Debug.Print \"UPDATE Frustas_Hortalizas SET volumen = '\" & volumenFinal & \"', Produccion_por_dia = '\" & produccionFinal & \"', Camara_frigorifica = '\" & camaraFinal & \"' WHERE \"\"Numero de suscriptor\"\" = \" & idSuscriptor & \"\"\r\n        Debug.Print \"UPDATE Frustas_Hortalizas SET volumen = '\" & volumenFinal & \"', Produccion_por_dia = '\" & produccionFinal & \"', Camara_frigorifica = '\" & camaraFinal & \"', Num_asociados = '\" & NumAsociadosFinal & \"' WHERE [Numero de suscriptor] = \" & idSuscriptor & \"\"\r\n        ExecuteQuery \"UPDATE Frustas_Hortalizas SET volumen = '\" & volumenFinal & \"', Produccion_por_dia = '\" & produccionFinal & \"', Camara_frigorifica = '\" & camaraFinal & \"', Num_asociados = '\" & NumAsociadosFinal & \"' WHERE [Numero de suscriptor] = \" & idSuscriptor & \"\"\r\n        'Exit Do\r\n        i = i + 1\r\n        recordSt.MoveNext\r\n    Loop\r\n    'Debug.Print \"Fields \" & recordSt.Fields.Count\r\n    Debug.Print \"Found \" & i & \" registers\"\r\n   ' Close Connection object and destroy object variable.\r\n   cnnDB.Close\r\n   Set cnnDB = Nothing\r\nEnd Sub\r\n\r\n' TransformDB: Convierte los campos \"Anyo_creacion\", \"N_trabajadores\"\r\n' \"Ventas_anuales\" a numeros sin texto (tabla Empresa)\r\nSub ConvertDB2()\r\n    Dim cnnDB As ADODB.Connection\r\n    Dim recordSt As New ADODB.Recordset\r\n    Dim strSQL As String\r\n    Dim idSuscriptor As String\r\n    Dim i As Integer\r\n    Dim anyoCreacion As String\r\n    Dim NTrabajadores As String\r\n    Dim VentasAnuales As String\r\n    Dim anyoCreacionFinal As Long\r\n    Dim NTrabajadoresFinal As Long\r\n    Dim VentasAnualesFinal As Long\r\n\r\n    ' Connectar a la BD actual:\r\n    Set cnnDB = CurrentProject.Connection\r\n\r\n    ' Obtenim tots els registres que tenen alguna\r\n    ' dada a algun dels camps que tractem\r\n    'strSQL = \"SELECT * FROM Frustas_Hortalizas WHERE NOT (volumen = '') OR NOT (produccion_por_dia = '') OR NOT (camara_frigorifica = '') \"\r\n    strSQL = \"SELECT * FROM Empresa\"\r\n    With recordSt\r\n        Set .ActiveConnection = cnnDB\r\n        .CursorType = adOpenKeyset\r\n        .CursorLocation = adUseClient\r\n        .LockType = adLockOptimistic\r\n        .Open strSQL\r\n    End With\r\n    \r\n    ' Tractar els camps i actualitzar taula\r\n    i = 0\r\n    If Not recordSt.EOF Then\r\n        recordSt.MoveFirst\r\n    End If\r\n    Do While Not recordSt.EOF\r\n        idSuscriptor = recordSt.Fields(\"Numero de subscriptor\").Value\r\n\r\n        If Not IsNull(recordSt.Fields(\"Anyo_creacion\").Value) Then\r\n            anyoCreacion = recordSt.Fields(\"Anyo_creacion\").Value\r\n            If (GetLastWord(anyoCreacion) &#60;> GetFirstWord(anyoCreacion)) Then\r\n                anyoCreacionFinal = CLng(GetFirstWord(anyoCreacion))\r\n            Else\r\n                anyoCreacionFinal = CLng(anyoCreacion)\r\n            End If\r\n        Else\r\n            anyoCreacionFinal = 0\r\n        End If\r\n        \r\n        If Not IsNull(recordSt.Fields(\"N_trabajadores\").Value) Then\r\n            NTrabajadores = recordSt.Fields(\"N_trabajadores\").Value\r\n            If (GetLastWord(NTrabajadores) &#60;> GetFirstWord(NTrabajadores)) Then\r\n                NTrabajadoresFinal = CLng(GetFirstWord(NTrabajadores))\r\n            Else\r\n                NTrabajadoresFinal = CLng(NTrabajadores)\r\n            End If\r\n        Else\r\n            NTrabajadoresFinal = 0\r\n        End If\r\n        \r\n        If Not IsNull(recordSt.Fields(\"Ventas_anuales_estimadas_Euros\").Value) Then\r\n            VentasAnuales = recordSt.Fields(\"Ventas_anuales_estimadas_Euros\").Value\r\n            If (GetLastWord(VentasAnuales) &#60;> GetFirstWord(VentasAnuales)) Then\r\n                VentasAnualesFinal = CLng(GetFirstWord(VentasAnuales))\r\n            Else\r\n                VentasAnualesFinal = CLng(VentasAnuales)\r\n            End If\r\n        Else\r\n            VentasAnualesFinal = 0\r\n        End If\r\n        \r\n        Debug.Print \"UPDATE Empresa SET Ventas_anuales_estimadas_Euros = '\" & VentasAnualesFinal & \"', N_trabajadores = '\" & NTrabajadoresFinal & \"', anyo_creacion = '\" & anyoCreacionFinal & \"' WHERE [Numero de subscriptor] = \" & idSuscriptor & \"\"\r\n        ExecuteQuery \"UPDATE Empresa SET Ventas_anuales_estimadas_Euros = '\" & VentasAnualesFinal & \"', N_trabajadores = '\" & NTrabajadoresFinal & \"', anyo_creacion = '\" & anyoCreacionFinal & \"' WHERE [Numero de subscriptor] = \" & idSuscriptor & \"\"\r\n        'Exit Do\r\n        i = i + 1\r\n        recordSt.MoveNext\r\n    Loop\r\n    'Debug.Print \"Fields \" & recordSt.Fields.Count\r\n    Debug.Print \"Found \" & i & \" registers\"\r\n   ' Close Connection object and destroy object variable.\r\n   cnnDB.Close\r\n   Set cnnDB = Nothing\r\nEnd Sub\r\nFunction convertirUnidades(left As String, right As String) As Long\r\n    Dim final As Long\r\n    \r\n    If (left = right) Then\r\n        final = 0\r\n    Else\r\n        If (LCase(right) = \"tn\") Or (LCase(right) = \"t\") Or (LCase(right) = \"tn\/dia\") Or (LCase(right) = \"tn\/d\u00eda\") Or (LCase(right) = \"palets\/dia\") Or (LCase(right) = \"palets\/d\u00eda\") Then\r\n            final = CLng(left)\r\n        Else\r\n            If (LCase(right) = \"kg\") Or (LCase(right) = \"kg\/dia\") Or (LCase(right) = \"kg\/d\u00eda\") Then\r\n              final = CLng(CLng(left) \/ 1000)\r\n            Else\r\n                final = 0\r\n            End If\r\n        End If\r\n    End If\r\n    \r\n    'MsgBox final\r\n    convertirUnidades = final\r\nEnd Function\r\n\r\n'http:\/\/www.peterssoftware.com\/strfn.htm\r\nFunction GetLastWord(sStr As String) As String\r\n'* Returns the last word in sStr\r\nDim i As Integer\r\nDim ilen As Integer\r\nDim s As String\r\nDim stemp As String\r\nDim sLastWord As String\r\nDim sHold As String\r\nDim iFoundChar As Integer\r\n\r\nstemp = \"\"\r\nsLastWord = \"\"\r\niFoundChar = False\r\nsHold = sStr\r\nilen = Len(sStr)\r\nFor i = ilen To 1 Step -1\r\n    s = right(sHold, 1)\r\n    If s = \" \" Then\r\n        If Not iFoundChar Then\r\n            '* skip spaces at end of string.\r\n        Else\r\n            sLastWord = stemp\r\n            Exit For\r\n        End If\r\n    Else\r\n        iFoundChar = True\r\n        stemp = s & stemp\r\n    End If\r\n    If Len(sHold) > 0 Then\r\n        sHold = left(sHold, Len(sHold) - 1)\r\n    End If\r\nNext i\r\n\r\nIf sLastWord = \"\" And stemp &#60;> \"\" Then\r\n    sLastWord = stemp\r\nEnd If\r\n'MsgBox \"lastword =\" & Trim(sLastWord)\r\nGetLastWord = Trim(sLastWord)\r\nEnd Function\r\n\r\n'http:\/\/www.peterssoftware.com\/strfn.htm\r\nFunction GetFirstWord(sStr As String) As String\r\n'* Returns the last word in sStr\r\nDim i As Integer\r\nDim ilen As Integer\r\nDim s As String\r\nDim stemp As String\r\nDim sLastWord As String\r\nDim sHold As String\r\nDim iFoundChar As Integer\r\n\r\nstemp = \"\"\r\nsLastWord = \"\"\r\niFoundChar = False\r\nsHold = sStr\r\nilen = Len(sStr)\r\nFor i = 1 To ilen Step 1\r\n    s = left(sHold, 1)\r\n    If s = \" \" Then\r\n        If Not iFoundChar Then\r\n            '* skip spaces at end of string.\r\n        Else\r\n            sLastWord = stemp\r\n            Exit For\r\n        End If\r\n    Else\r\n        iFoundChar = True\r\n        stemp = stemp & s\r\n    End If\r\n    If Len(sHold) > 0 Then\r\n        sHold = right(sHold, Len(sHold) - 1)\r\n    End If\r\nNext i\r\n\r\nIf sLastWord = \"\" And stemp &#60;> \"\" Then\r\n    sLastWord = stemp\r\nEnd If\r\n'MsgBox \"lastword =\" & Trim(sLastWord)\r\nGetFirstWord = Trim(sLastWord)\r\nEnd Function\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>He escrito c\u00f3digo nuevo para intentar transformar un campo &#8220;string&#8221; en uno &#8220;long&#8221; y tratar las unidades, es decir, por ejemplo si tenemos un campo con &#8220;1000 kg&#8221; que se transforme simplemente en 1 y la unidad es la misma para todos los registros (toneladas). Hay una funci\u00f3n que la he cogido de una web &hellip; <a href=\"https:\/\/www.marblestation.com\/?p=153\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Transformaci\u00f3n de unidades en MS Access<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,6],"tags":[],"class_list":["post-153","post","type-post","status-publish","format-standard","hentry","category-espanyol","category-tecnologia"],"_links":{"self":[{"href":"https:\/\/www.marblestation.com\/index.php?rest_route=\/wp\/v2\/posts\/153","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.marblestation.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.marblestation.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.marblestation.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.marblestation.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=153"}],"version-history":[{"count":1,"href":"https:\/\/www.marblestation.com\/index.php?rest_route=\/wp\/v2\/posts\/153\/revisions"}],"predecessor-version":[{"id":1768,"href":"https:\/\/www.marblestation.com\/index.php?rest_route=\/wp\/v2\/posts\/153\/revisions\/1768"}],"wp:attachment":[{"href":"https:\/\/www.marblestation.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=153"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.marblestation.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=153"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.marblestation.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=153"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}