Splitfunktion i notatfelt

Function GetNumbers()
Dim strTest As String


strTest = "asaadada journalnr 123456 jkjlkjkjlkjlkjlkjlkj journalnr 567890 jkjlkjjjkkj"




End Function

Public Function Journal(felt1)
Dim VARa As String
Dim O As Variant
Dim i As Integer



If Len(felt1) > 0 Then
    O = Split(felt1, "journalnr")
    If UBound(O) > 0 Then
    For i = 1 To UBound(O)
        VARa = Left(O(i), 7)
        Journal = Right(VARa, 6)
       
        Debug.Print Journal
        'You can use Docmd.runsql INSERT into SomeTableOrOther......"
       
    Next i
    Else
        Journal = "Journalnr findes ikke

Jeg prøver lige og tak indtil videre

Hej Terry. Jeg er ikke så skrap. Har du også kodeeksempel til sql linjen ?
Jeg går ud fra at jeg ikke skal anvende din testlinje ?

Public Function Journal(notatfeltet)
Dim VARa As String
Dim O As Variant
Dim i As Integer



If Len(felt1) > 0 Then
    O = Split(felt1, "journalnr")
    If UBound(O) > 0 Then
    For i = 1 To UBound(O)
        VARa = Left(O(i), 7)
        Journal = Right(VARa, 6)
       
        Debug.Print Journal
        'You can use Docmd.runsql INSERT into SomeTableOrOther......"
       
    Next i
    Else
        Journal = "Journalnr findes ikke

You need a table with a field to hold the numbers

table name = tblJournalNumbers
field name  JournalNumber



Docmd.runsql "INSERT into tblJournalNumbers (JournalNumber) VALUES ('" & Journal & "')"

Public Function Journal(felt1)
Dim VARa As String
Dim O As Variant
Dim i As Integer

    If Len(felt1) > 0 Then
        O = Split(felt1, "journalnr")
        If UBound(O) > 0 Then
        DoCmd.SetWarnings False
        For i = 1 To UBound(O)
            VARa = Left(O(i), 7)
            Journal = Right(VARa, 6)
           
            DoCmd.RunSQL "INSERT into tblJournalNumbers (JournalNumber) VALUES ('" & Journal & "')"
           
        Next i
        Else
            Journal = "Journalnr findes ikke"
        End If
       
        DoCmd.SetWarnings True
    End If
End Function

Jeg har lavet en knap og indsat
koden call Journal.
Det virker ikke. Kan du give mig et tip

you need to call the function with the memo field contents

something like

Journal YourMemoField

Jeg prøver at kalde funktionen fra en forespørgsel. Udtryk1: Journal([notatfeltet])
men den dør på kørsel af sql. I forespørgslen har jeg også andre call's

Funktionen fangede godt nok det sidste Journalnr og lagde det i forespørgslen. Men meningen var jo også at det skulle lægges i en særlig tabel med hver sin række.

Why are you calling the function from a query? The code will NOT return all journal numbers it will put them in a table.

Når jeg skriver

call Journal (notatfelt)

og kører det fra en knap,
så kører funktionen tilsyneladende, men numrene ligger ikke i tabellen ?

is it possible for you to send me your dB and I will take a look this evening?

eksperten@NOSPAMsanthell.dk

remove NOSPAM

mangler der noget select from i sql-sætningen ?

No, the SQL should be OK with just a simple INSERT. But it only INSERTS the journal numbers.

Hej Terry. Måske har du tid. Jeg har problemer med at få sql til at køre fra funktionen. Kan jeg mangle en reference eller andet. Jeg har prøvet at lave en tilføjelsesforespørgsel og lagt den ind i funktionen. Den vil heller ikke afvikle docmd.runsql

can you see if the code is executed?

Comment out the lines

DoCmd.SetWarnings False
and
DoCmd.SetWarnings True

If the line of code

..
DoCmd.RunSQL "INSERT into tblJournalNumbers (JournalNumber) VALUES ('" & Journal & "')"

gets executed then you should get a message.

You can also set a breakpoint at that line

no message. jeg har også prøvet at slå warnings fra. Der kommer ingen fejltekst.
Proceduren blir ikke kaldt på den rigtige måde. Jeg anvender en knap:
Call Journal(notatfeltet) og koden i proceduren:

Public Function Journal(notatfeltet)
Dim VARa As String
Dim O As Variant
Dim i As Integer
    If Len(notatfeltet) > 0 Then
        O = Split(notatfeltet, "Journalnr")
        If UBound(O) > 0 Then
       
        'DoCmd.SetWarnings False
       
        For i = 1 To UBound(O)
            VARa = Left(O(i), 7)
            Journal = Right(VARa, 6)
           
          DoCmd.RunSQL "INSERT into [Data_udtræk] (Data) VALUES ('" & Journal & "')"
                     
           
        Next i
        Else
            Journal = "Journalnr findes ikke"
        End If
       
        'DoCmd.SetWarnings True
End If
End Function
,,,,,,,,,,,
jeg har en tabel: tbloplysninger med et felt "notatfeltet".
Og jeg har en tabel Data_udtræk med et felt "Data" som også har notatformat
,,,,,,,,,,,
Journalnr skal udtrækkes fra "notatfeltet"(kun en række) og insertes i "Data" hvor der gerne skulle optræde flere rækker med de fundne Journalnr. Går til frokost nu.

it should NOT be necessary to us CALL

this should be enough

Journal notatfeltet

If you set a breakpoint at the first line of code

if....

and then press the button, do you stop at the first line of code?

I can set a toggle breakpoint. Er det OK. Og hvilken button do I press. I have made a macro med the sql, and can execute outside the procedure. I tried put the macro insode the procedure, but it did not execute

13/09-2005 15:51:05

forsøgt at sende via TDC online mail. Vil ikke lykkes. Prøver i aften igen

it may be an idea to ZIP the database

and remember to remove NOSPAM

thank you

have you sent your database?

Yes

I have NOT received it!

can you send it again. Please make sure that you use the correct email WITHOUT

NOSPAM

Your e amil: eksperten@NOSPAMsanthell.dk  ? I use outlook 6.0 there is not a nospam button. I send it last night 17:58

you have to remove NOSPAM from the email address!

Nu kører call. Men fejltekst lyder: "Objektet indeholder ikke automationsobjektet "Journal". Jeg forsøger at sende basen i aften.

its hard to help if I cant see the dB!
Compress the dB (Tools-Database Utilities-Compact Repair menu)
then ZIP it.

You can also try sending it to
sandy@NOSPAMstofanet.dk

again you MUST remove NOSPAM

I send it to info@santhell.dk

I have received no mail at the info... account!

The reason why I place NOSPAM in my email address when making it public is to limit the SPAM I receive!!!! So NOSPAM must be removed from the email address.


Do you have an email address I can send a mail to?

Yes I have: susannehougaard@mail.dk
But now i mdb works. I did not have my tbl in the form. I just had a button without no data in the form. Is it possible to get the funktion to execute without the data.
Can I write to the button in code that it has to tke the data from the tbl field.

I do understand danish (almost 100%) so you  do not need to answer in English.

The function will work as long as you give it some data! But as I dont know exactly how you want to use it then its hard to give an example.


Something else you should concider:

You should maybe have another table where you write your information on each Journal. Having to find the information in one memo field isnt the best method.

are we getting there tolveren?

Ja vi er der faktisk og det virker. Kan du ændre koden så den samtidig finder opretteren. F eks "xxx xxcdd tekst "Journalnr 345666" cxcmdk tekst dllf "Oprettet af Peter Nielsen" sdllæssææ tekst dææføøøas teskstjkll "Journalnr 888889" llæas dlls "Oprettet af Niels Nielsen"

when searching for Journalnr then we know that the number is six charactres. The problem in search for "Opretteren" is we dont know how long the name is, or do we?

I suggest that you consider normalizing your database, this will make things MUCH easier.

The name "Opretteren" is max 30 tegn

what if it less than 30 characters? The code can not guess when the name ends so it needs som eway of knowing.

Det er OK at Opretteren indeholder navnet + noget ekstra tekst. Må også gerne indeholde evt blanke tegn.

Public Function opretteren(notatfeltet)
Dim O As Variant
Dim i As Integer
    If Len(notatfeltet) > 0 Then
       
        O = Split(notatfeltet, "Oprettet af")
        If UBound(O) > 0 Then
       
        For i = 1 To UBound(O)
            opretteren = Mid(O(i), 2, 30)
           
          Debug.Print opretteren
                     
           
        Next i
        Else
            opretteren = "Opretteren findes ikke"
        End If
       
End If
End Function

Ja, Ja Terry. Men det skulle jo bygges sammen med det der Journalnr. Koden skal gennemløbe notatfeltet og finde både Journalnr og Opretter i samme gennemløb. Kan man det ? og hvordan ser den sammebyggede kode ud ? Journalnr skulle lægges i den ny tabel i felt1 og Opretteren i samme tabel men bare i felt2.

I had a feeling that that was what you would want :o)

This is NOT tested. It assumes that for each journal there is a "opretter", if not then it will fail!

Public Function Journal(notatfeltet)
Dim VARa As String
Dim O As Variant
Dim P As Variant
Dim i As Integer

    If Len(notatfeltet) > 0 Then
        O = Split(notatfeltet, "Journalnr")
    P = Split(notatfeltet, "Oprettet af")

        If UBound(O) > 0 Then
       
        'DoCmd.SetWarnings False
       
        For i = 1 To UBound(O)
            VARa = Left(O(i), 7)
            Journal = Right(VARa, 6)
        opretteren = Mid(P(i), 2, 30)

           
          DoCmd.RunSQL "INSERT into [Data_udtræk] (Data, fld2) VALUES ('" & Journal & "', '" & opretteren  & ")"
                     
           
        Next i
        Else
            Journal = "Journalnr findes ikke"
        End If
       
        'DoCmd.SetWarnings True
End If
End Function

Ser ellers godt ud, men opretteren(variablen er ikke defineret ? eller subscript out of range ?)

just add
Dim opretteren as String to the variables at the top of the function and try again

Nu kun subscript out of range. Nå slut for i dag og tak indtil videre

script out of range indicates that there is a differnt number of elements in the arrays

try adding this

msgbox Ubound(O) & " - " UBound(P) before this line

If UBound(O) > 0 Then

what do you see?

Jeg får compile error:

Public Function Journal2(notatfelt)
    Dim VARa As String
    Dim VARb As String
    Dim O As Variant
    Dim p As Variant
    Dim i As Integer
    Dim opretteren As String
    Dim Journal As String
   
    If Len(notatfelt) > 0 Then
        O = Split(notatfelt, "Journalnr")
        p = Split(notatfelt, "Oprettet af")

msgbox Ubound(O) & " - " UBound(P)
        If UBound(O) > 0 Then
       
        'DoCmd.SetWarnings False
       
        For i = 1 To UBound(O)
            VARa = left(O(i), 7)
            Journal = Right(VARa, 6)
        opretteren = Mid(p(i), 2, 30)

        DoCmd.RunSQL "INSERT into [Data_udtræk] (Data,Opretter) VALUES ('" & Journal & "','" & opretteren & "')"
                     
           
        Next i
        Else
            Journal = "Journalnr findes ikke"
        End If
               
        'DoCmd.SetWarnings True
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End If

End Function

which line of code?

På den nye: msgbox Ubound(O) & " - " UBound(P)

sorry

msgbox Ubound(O) & " - " & UBound(P)

Den skriver 9-1 og bagefter subscript out of range.

Min tekst indeholder "Journalnr" 9 gange og "Oprettet af" 1 gang. Koden fanger det første Journalnr og den ene Opretter.

theres your problem!

I think the original question is answered now, but I can take a look at this if you can send me your database. If I cant see the data and the code then its very hard to find out what is wrong.

You have my email. If your mails do notget through then I suspect that your mail server is stopping it.

Jeg prøver i aften

did you send anything to me?

Har været lidt hængt op og er det stadig.

Hi Susanne
Do you think we could close this question now? The original question has been answered.
If you need further help when you have more time then you can just place a comment here.

mvh
Terry

Jeg har 2 gange forsøgt at give dig point. Jeg prøver nu at flytte prikken ned på svar og klikke på accepter. Tak for hjælpen.

Lukker og slukker. Du har fået point den 13 sept kan jeg se.

thanks
There is a problem when trying to give points, it is necessary to choose the name in the laist and then accept.