Get value from one field, and paste it in to a new column

[Neymar0001]

Hello everyone!

I'm relatively new to Microsoft Access, and just a beginner with SQL programming.

I have a table in MS Access. It is an import from a .txt file. In the first column There is a String with a Number or Code in it. This number always Comes after a string "Arbeitslpatz "(The first character starts at string Position 17) and always changes every 76 rows. (Total rows 29790). What I want to achiev is to copy this number (for example 101100) and paste it in a new column in the first 76 rows. Then comes the next number for the next 76 rows, and so on. (I later want to to a lookup function with SQL WHERE functions and return some desired values, and for that I need to have these "Arbeitsplatz numbers" in a new row.
I already assigned an ID to the file, but I didn't manage to get farther. (I experimented with VBA Loops, but didn' really succeed)
Either SQL or VBA Code would be appreciated.



I don't know how well the screenshots can be seen &/ if I provided enough Information...

Thank you all for your help

[mike60smart]

Hi

It would be far easier if you Copy the .txt File into Excel and then carry out a Text to Columns Process.

[CJ_London]

easiest to use a function - something like

function getCode(s as string) as string
static lastCode as string

    if s like "Arbeitslpatz*" then   lastCode=split(mid(s,17)," ")(0)
    getCode=lastCode

end if

then in your query, something like

SELECT getCode([Arbeitslpatz]),

[Neymar0001]

Thanks for all the tips!

[Neymar0001]

I know that, but unfortunately that is not an option for me. I have to update the file daily, and don't want to do it always manually. But thanks for your tip anyway

@mike60smart

[Neymar0001]

easiest to use a function - something like

function getCode(s as string) as string
static lastCode as string

    if s like "Arbeitslpatz*" then   lastCode=split(mid(s,17)," ")(0)
    getCode=lastCode

end if

then in your query, something like

SELECT getCode([Arbeitslpatz]),

I copied your code in the VBA Module in access. I then created a query, and tried to run this code> SELECT getCode([CM01_Total]![Arbeitsplatz]); I receive an error message which says undifined function 'getCode' in expression. What did I do wrong? Thanks in advance!

[CJ_London]

doesn't look like you completed your query

and to be clear the getCode function needs to be in a standard vba module (with a different name such as modString), not a form or class module

[Neymar0001]

doesn't look like you completed your query

and to be clear the getCode function needs to be in a standard vba module (with a different name such as modString), not a form or class module

I almost succeded...

This is the slightly modified VBA code:

Option Compare Database
Function modString(s As String) As String
Static lastCode As String
    If s Like "Arbeitsplatz*" Then lastCode = Split(mi(s, 17), " ")(0)
    modString = lastCode
    End If
End Function

And the SQL:

SELECT modString([Arbeitsplatz]) AS Expr1, *
FROM CM01_Total;

I get this error message:
There was an error compiling this function.
The Visual Basic module contains a sytax error.
Check the code, and the recompile it.


Thank you for your help!

[CJ_London]

Split(mi(s, 17), " ")(0)

recommend

a. Put Option Explicit at the top of each module which will capture errors like this
b. when you written your code, compile it (debug>compile on the vba ribbon

Just out of curiositity why did you not just copy/paste my code from the thread?- it was complete and did not need any changes

[Neymar0001]

I finally found a sollution. It works with the original .txt file, and imports it as I want it.

VBA Code:

Option Compare Database
Function Macro1()
'' dim variables
Dim tmp As String, temp() As String
Dim rs As Recordset
'' open source for reading
Open "My\File\Location\File.txt" For Input As #1
'' attach recordset to table
Set rs = CurrentDb.OpenRecordset("Target_Table")
'' loop source - read line-by-line until its end
Do Until EOF(1)
  '' read one line
  Line Input #1, tmp
  '' look if it is a header, if yes, extract and store the number
  If Left(tmp, 12) = "Arbeitsplatz" Then num = Trim(Mid(tmp, 17, 8))
  '' look if it is a data line
  If InStr(tmp, "|") > 0 And InStr(tmp, "Woche") = 0 Then
    '' divide the line to separate fields
    temp = Split(tmp, "|")
    With rs
      '' add new, empty, record
      .AddNew
      '' set values into fields
      !Arbeitsplatz = num
      !Woche = temp(1)
      !Bedarf = temp(2)
      !Angebot = temp(3)
      !Belast = temp(4)
      !Freie_Kap = temp(5)
      !Einh = temp(6)
      '' save editions
      .Update
    End With
  End If
'' loop to reading the next line
Loop
'' close source file and recordset
Close #1
rs.Close
Set rs = Nothing
End Function

I hope this helps someone (It helped me a LOT)