Re: INSERT statement error "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 03.03.2006 20:40:00 strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _ & " VALUES (" & strJcn & "," & strParcTag & ")" If one of these variable contains a string value, for example strParcTage, then delimite it with single quotes and take the precaution of replacing all encased single quotes with two single quotes: strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _ & " VALUES (" & strJcn & ",'" & Replace (strParcTag, "'", "''") & "')" -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "AkAlan via AccessMonster.com" <u18147[ at ]uwe> wrote in message news:5cb7fc30c15ac[ at ]uwe... [Quoted Text] > I'm trying to add a record to a table and am getting Error 8152: > String or binary data would be truncated. > > Here is my statement written with VBA in an Access project. > Both datatypes are nvarchar > > Private Sub cmdCreateJob_Click() > > Dim strJcn As String > Dim strParcTag As String > Dim strSql As String > > strJcn = 999999 > strParcTag = Me.cboEquipID.Column(0) > > strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _ > & " VALUES ('strJcn','strParcTag')" > > DoCmd.RunSQL strSql > > End Sub > > Can anyone see what I'm doing wrong? Thanks. > > -- > Message posted via http://www.accessmonster.com

INSERT statement error "AkAlan via AccessMonster.com" <u18147[ at ]uwe> 03.03.2006 21:00:53 I'm trying to add a record to a table and am getting Error 8152: String or binary data would be truncated. Here is my statement written with VBA in an Access project. Both datatypes are nvarchar Private Sub cmdCreateJob_Click() Dim strJcn As String Dim strParcTag As String Dim strSql As String strJcn = 999999 strParcTag = Me.cboEquipID.Column(0) strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _ & " VALUES ('strJcn','strParcTag')" DoCmd.RunSQL strSql End Sub Can anyone see what I'm doing wrong? Thanks. -- Message posted via http://www.accessmonster.com

Re: INSERT statement error "AkAlan via AccessMonster.com" <u18147[ at ]uwe> 03.03.2006 21:14:25 solved it myself. Here is the change that workd : strJcn = "'" & 999999 & "'" strParcTag = "'" & Me.cboEquipID.Column(0) & "'" strSQL = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _ & " VALUES (" & strJcn & "," & strParcTag & ")" AkAlan wrote: [Quoted Text] >I'm trying to add a record to a table and am getting Error 8152: >String or binary data would be truncated. > >Here is my statement written with VBA in an Access project. >Both datatypes are nvarchar > >Private Sub cmdCreateJob_Click() > > Dim strJcn As String > Dim strParcTag As String > Dim strSql As String > > strJcn = 999999 > strParcTag = Me.cboEquipID.Column(0) > > strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _ > & " VALUES ('strJcn','strParcTag')" > > DoCmd.RunSQL strSql > >End Sub > > Can anyone see what I'm doing wrong? Thanks. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200603/1

Re: INSERT statement error "AkAlan via AccessMonster.com" <u18147[ at ]uwe> 03.03.2006 22:11:27 Sylvan, first thanks for answering, you have solved a lot of problems for me in the past. I don't understand what the Replace function accomplishes in your example. As you can see from my re-post, I got it to work on my own but I would like to have a better understanding of why you did what you did. Knowing where to put single and double quotes has always kicked my ass. I looked up the replace function in BOL and understand how it works, I just don't see why it is needed. Sylvain Lafontaine wrote: [Quoted Text] > strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _ > & " VALUES (" & strJcn & "," & strParcTag & ")" > >If one of these variable contains a string value, for example strParcTage, >then delimite it with single quotes and take the precaution of replacing all >encased single quotes with two single quotes: > > strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _ > & " VALUES (" & strJcn & ",'" & Replace (strParcTag, "'", "''") & "')" > >> I'm trying to add a record to a table and am getting Error 8152: >> String or binary data would be truncated. >[quoted text clipped - 19 lines] >> >> Can anyone see what I'm doing wrong? Thanks. -- Message posted via http://www.accessmonster.com

Re: INSERT statement error "AkAlan via AccessMonster.com" <u18147[ at ]uwe> 03.03.2006 22:33:06 Ok I got everything to work up until I add the datOpenDate field. It only puts 12:00 AM in the record when I put 1 mar 06 in the form. Here is what I have so far: I have tried formatting the datefield but no go. Thanks, strJcn = "'" & NextJCN() & "'" strParcTag = "'" & Me.cboEquipID.Column(0) & "'" strPerfWc = "'" & Me.cboPWC & "'" strRptby = "'" & Me.cboReportedBy.Column(0) & "'" strDisc = "'" & Me.txtDisc & "'" datOpenDate = “’” & Me.txtOpenDate & “’” strSQL = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG,ESR_DISC,ESR_RPTBY, ESR_OPEN_DATE)" _ & " VALUES (" & strJcn & "," & strParcTag & "," & strDisc & "" _ & "," & strRptby & ", " & datOpenDate & " )" DoCmd.RunSQL strSQL Sylvain Lafontaine wrote: [Quoted Text] > strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _ > & " VALUES (" & strJcn & "," & strParcTag & ")" > >If one of these variable contains a string value, for example strParcTage, >then delimite it with single quotes and take the precaution of replacing all >encased single quotes with two single quotes: > > strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _ > & " VALUES (" & strJcn & ",'" & Replace (strParcTag, "'", "''") & "')" > >> I'm trying to add a record to a table and am getting Error 8152: >> String or binary data would be truncated. >[quoted text clipped - 19 lines] >> >> Can anyone see what I'm doing wrong? Thanks. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200603/1