Error Using StrString With SQL INSERT INTO

CharlesWhiteman

Registered User.
Local time
Today, 16:48
Joined
Feb 26, 2007
Messages
421
Each time the code is executed Access asks for the value of each field rather than using the StrStrings. In debug the StrStings show the correct values but dont seem to transmit through to the SQL INSERT INTO part - as below

Dim strSQL As String
Dim StrCompanyCode As String
Dim StrCallReference As String
Dim StrCallDate As String
Dim StrDepartment As String
Dim StrPerson As String
Dim StrCallNote As String
Dim StrResult As String


StrCompanyCode = Forms.frmdatabase.CompanyCode
StrCallReference = DMax("[Call Reference]", "TblCalls") + 1
StrCallDate = Now()
StrDepartment = "Sales"
StrPerson = CurrentUser()
StrCallNote = "Relationship Change"
StrResult = "Note"

Works Fine down to Here

strSQL = "INSERT INTO TblCalls (CompanyCode, [Call Reference], [Call Date], Department, Person, [Call note], Result)"
strSQL = strSQL & " VALUES(StrCompanyCode, StrCallReference, StrCallDate, StrDepartment, StrPerson, StrCallNote, StrResult)"


DoCmd.RunSQL strSQL
 
Found the answer as follows: Change the value's:


strSQL = "INSERT INTO TblCalls (CompanyCode, [Call Reference], [Call Date], Department, Person, [Call note], Result)"
strSQL = strSQL & " VALUES(' " & [strcompanycode] & " ', ' " & [strcallreference] & " ', ' " & [strcalldate] & " ', ' " & [strdepartment] & " ', ' " & [strperson] & " ', ' " & [strCallnote] & " ', ' " & [strResult] & " ')"

DoCmd.RunSQL strSQL
 
Can anyone tell me why, after the code executes, in the table, there is an extra space before each record detail?
 
Only where the field type is text does the extra space before the record detail happen
 
Charles,
you have added the spaces yourself, consider:
Code:
strSQL = strSQL & " VALUES(' " & [strcompanycode] & " ', ' " & [strcallreference] & " ', ' " & [strcalldate] & " ', ' " & [strdepartment] & " ', ' " & [strperson] & " ', ' " & [strCallnote] & " ', ' " & [strResult] & " ')"
change it to:
Code:
strSQL = strSQL & " VALUES('" & [strcompanycode] & "', '" & [strcallreference] & "', '" & [strcalldate] & "', '" & [strdepartment] & "', '" & [strperson] & "', '" & [strCallnote] & "', '" & [strResult] & "')"
you can see that I have removed the spaces you put in after each opening ' and before each closing '

HTH,
Chris
 

Users who are viewing this thread

Back
Top Bottom