Avatar billede rickie Juniormester
05. marts 2015 - 17:32 Der er 5 kommentarer og
1 løsning

Substring eller lignende

Hej,

Jeg har et dataudträk "DOE JOHN, HANSEN"
Nu vil jeg at det ska omvandles til: "Doe John" og "Hansen"

Hvordan gör jeg det?

Hele min kode er her:
-----
Private Sub btnSearch_Click()
DoCmd.SetWarnings False
Dim exeSQL As String

Me.txtLicenseplate = UCase(Me.txtLicenseplate.Value)

If IsNull(Me.txtLicenseplate.Value) Then

    MsgBox "Du måste ange ett registreringsnummer!", vbCritical, "Fel!"

Else
     
'Me.srcCustomerName = UCase(Left(Me.srcCustomerName, 1)) & Substring(Me.srcCustomerName, 1, 5)

    exeSQL = "INSERT INTO tblReco ([srcLicenseplate],[srcEmail],[srcName1],[srcPhone], [srcDate],[srcExported])" & vbCrLf & _
            "SELECT (TBLBR.[BIL_RENR]),(TBLKR.[KUN_EPOSTADRESS]),(LCase(TBLKR.[KUN_NAMN])),(TBLKR.[KUN_TEL2]),date(),'no'" & vbCrLf & _
            "FROM [BILREG] AS TBLBR, [KUNREG] AS TBLKR" & vbCrLf & _
            "WHERE TBLKR.[KUN_KUNR] = TBLBR.[BIL_KUNR] AND TBLBR.[BIL_RENR] LIKE '*" & txtLicenseplate.Value & "*'"
    DoCmd.RunSQL exeSQL
   
        If IsNull(DLookup("[srcEmail]", "[tblReco]", "[srcLicenseplate] = txtLicenseplate")) Then
            prompt = "Det finns ingen e-postadress på " & Me.txtLicenseplate.Value & vbNewLine & vbNewLine & "Vill du registrera den ändå?"
           
            If MsgBox(prompt, vbYesNo) = vbNo Then
                Me.Refresh
                Me.Requery
                exeSQL = "DELETE * FROM tblReco WHERE srcId = " & Me.srcID.Value & ""
                DoCmd.RunSQL exeSQL
            'Else
            '    Me.txtLicenseplate = ""
            End If
       
        End If
End If

Me.txtLicenseplate.Value = ""
Me.txtLicenseplate.SetFocus

Me.Requery
Me.Refresh

End Sub
Avatar billede terry Ekspert
05. marts 2015 - 18:05 #1
if there is always a , between first and last names you can use something like this

Dim fullName As String
Dim firstName As String
Dim lastName As String

Dim inx As Integer

fullName = "DOE JOHN, HANSEN"

inx = InStr(1, fullName, ",", vbBinaryCompare)

firstName = Left(fullName, inx - 1)
lastName = Trim(Right(fullName, Len(fullName) - inx))
Avatar billede rickie Juniormester
06. marts 2015 - 10:35 #2
Hi Terry,

Thank you, but how would you put it in my code? Sorry for asking :-)
Avatar billede terry Ekspert
06. marts 2015 - 11:34 #3
I'd like to, but what part of the code needs changing?
I can see this:
'Me.srcCustomerName = UCase(Left(Me.srcCustomerName, 1)) & Substring(Me.srcCustomerName, 1, 5)

but that's commented out!
Avatar billede terry Ekspert
06. marts 2015 - 11:38 #4
You know your code better than I do :-)


just add the code code I gavce and then you will need to replace "DOE JOHN...." with the correct value (variable/field name)

fullName = "DOE JOHN, HANSEN"

Then after code is executed you have two variables containing first and last names. You can use these in your SQL
Avatar billede rickie Juniormester
06. marts 2015 - 14:37 #5
Fik det til at virke. Tusind tak :-)

Dim Name As String
Dim NameLast As String
Dim NameFirst As String

Dim Inx As Integer

Name = Me.txtName

Inx = InStr(1, Name, ",", vbBinaryCompare)

NameLast = Left(Name, Inx - 1)
NameFirst = Trim(Right(Name, Len(Name) - Inx))
Avatar billede terry Ekspert
06. marts 2015 - 14:49 #6
super, thanks and a good weekend
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Dyk ned i databasernes verden på et af vores praksisnære Access-kurser

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester