Update query to concatenate values into a string field

[kee2ka4]

I have the following VBA code that runs the Update sql and updaes the field CandidateNumber.

[FONT=Verdana][SIZE=2]strCNum = "UPDATE tblExamDetails " & _
                 "SET tblExamDetails.candidateNumber =" & Me.Text38 & "/0" & Me.fkDesignerID & " " & _
                 "WHERE ((tblExamDetails.fkDesignerID)=" & Me.fkDesignerID.Value & " " & _
                 "AND (tblExamDetails.fkExamType)=" & Me.fkExamType.Value & ");"
        
    DoCmd.RunSQL strCNum
[/SIZE][/FONT]

My Question is I want to update the field by joining the following three elements into one string:

(Me.Text38) + “/0” + “Me.fkDesignerID”

So in the above case if Me.Text28 is 0602007 and Me.fkDesignerID is 70, then the updated field value will be 0602007/070.

When I run the above code it uses the forward slash (“/”) as a divide symbol and gives the wrong output. Can someone plz point me in the right direction on how to this?

Thanks,
Ket

 

[pbaldy]

Try

"SET tblExamDetails.candidateNumber ='" & Me.Text38 & "/0" & Me.fkDesignerID & "' " & _

 

[DCrake]

The plus sign + is an implyed AND the & sign is an implied concatenation. The / sign means divide. Because you are employing the + and / symbols it is trying to perform an arithmetic equasion.

CStr(Me.Text38) & '/0' & CStr(Me.fkDesignerID)

Should give you what you want.

David

 

[pbaldy]

After seeing David's excellent point I'll add that candidateNumber would have to be a text field in the table. I don't think it will ever hold the value in that format if it's numeric.