once again: some problems with the sql UPDATE statement

[NiMora]

Hey guys,



I try it again.

I have a string I get from outlook with the hole name (fist name, sure name) and I want to split it into two fields.

actually it works, but the UPDATE of the database won´t work. could you take a look please?

Option Compare DatabaseOption Explicit


Private Sub Befehl9_Click()
Dim db As DAO.Database
Dim strGlatt
Dim strLaenge
Dim strPosLeer
Dim strLinkeSeite
Dim strRechteSeite
Dim strSQL
Dim strID As Byte


Set db = CurrentDb
strID = Me.ID
Debug.Print "die ID ist: " & strID


strGlatt = Trim$(Me.ganzerName)
strLaenge = Len(strGlatt)
Debug.Print "gesamtlänge: " & strLaenge
strPosLeer = InStr(strGlatt, " ")
Debug.Print "Position Leerzeichen: " & strPosLeer


strLinkeSeite = Left(strGlatt, strPosLeer - 1)
Debug.Print "Linker Name: " & strLinkeSeite
strRechteSeite = Right(strGlatt, strLaenge - strPosLeer)
Debug.Print "Rechter Name: " & strRechteSeite


db.Execute ("UPDATE Tabelle1 SET Vorname = '" & strLinkeSeite & "', Nachname = '" & strRechteSeite & "' WHERE ID = strID")


Forms![formTabelle1].Requery


End Sub

I think the proleme is the WHERE part, isn´t so?

thx

[pbaldy]

You have to concatenate the ID variable into the string, much as you did with the other. If the data type of the field is numeric, you wouldn't want the single quotes.

[orange]

I would add a variable

Dim sql as string

and before your current db.execute add this line (I did edit your line of code)

sql = "UPDATE Tabelle1 SET Vorname = '" & strLinkeSeite & "', Nachname = '" & strRechteSeite & "' WHERE ID = " & strID

Then comment your current db.execute line

Then add

Debug.Print sql

This will show you what Access thinks your SQL means.

[NiMora]

Hey, thx for quick help.

this one helps:

strSQL = "UPDATE Tabelle1 SET Vorname = '" & strLinkeSeite & "', Nachname = '" & strRechteSeite & "' WHERE ID = " & strID

result of debug.print: UPDATE Tabelle1 SET Vorname = 'Max', Nachname = 'Mustermann' WHERE ID = 3

It works now and I untderstand that one part of the statement is a string and another part (especially the ID) is a number and has to put outside the string.

cool, thx again