String too long? 2

[bdjb]

I'm creating a VBA string in MS Access 2003, it's inserting data into a table and the string has gotten so long that it now drops to the next line and gives an error. How can I "wrap" the string to the next line, or create another insert statement that would insert onto the same line of data in the table?

thanks

 

[Remou]

You can continue " _
& "Strings with the " _
& "Line continuation " _
& "Character.

 

[genomon]

But you there is a maximum number of continuation characters allowed (255?). You can concatenate strings to the max string limit with:
strYourString = strYourString & "new data".

There are two kinds of strings: variable-length and fixed-length strings.
A variable-length string can contain up to approximately 2 billion (2^31) characters.
A fixed-length string can contain 1 to approximately 64K (2^16) characters.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[genomon]

Oops - forgot to mention that the table you are trying to insert into has a max character size - either by default, or defined in design view.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[bdjb]

Hi Thank you both for your input. I've tried the &_, but get the error missing ; at end of SQL statment.

Here's my code, sorry it's so ugly!

strSQLApp1 = "INSERT INTO Training_Taken (Employee_Number, Job_Title, BPML1, BPML2, BPML3, BPML4, BPML5, BPML6, BPML7, BPML8, BPML9," _
& "BPML10, BPML11, BPML12, trnDate, trnTime, AC, Training_Status, Course, Course_Description, Hours, Trainer)" _
& "VALUES (" & Forms!frmSearch_Detail_After!employee & ", '" & Forms!frmSearch_Detail_After!Job_Title & "'" _
& ", '" & Forms!frmSearch_Detail_After!BPML1 & "', '" & Forms!frmSearch_Detail_After!BPML2 & "'" _
& ", '" & Forms!frmSearch_Detail_After!BPML3 & "', '" & Forms!frmSearch_Detail_After!BPML4 & "'" _
& ", '" & Forms!frmSearch_Detail_After!BPML5 & "', '" & Forms!frmSearch_Detail_After!BPML6 & "'" _
& ", '" & Forms!frmSearch_Detail_After!BPML7 & "', '" & Forms!frmSearch_Detail_After!BPML8 & "'" _
& ", '" & Forms!frmSearch_Detail_After!BPML9 & "', '" & Forms!frmSearch_Detail_After!BPML10 & "'" _
& ", '" & Forms!frmSearch_Detail_After!BPML11 & "', '" & Forms!frmSearch_Detail_After!BPML12 & "'" _
& ", '" & Forms!frmSearch_Detail_After!trnDate & "', '" & Forms!frmSearch_Detail_After!trnTime & "'" _
& ", '" & "A" & "' , '" & Forms!frmSearch_Detail_After!Training_Status & "', '" & Forms!frmSearch_Detail_After!Course & "'" _
& ", '" & Forms!frmSearch_Detail_After!Description & "', '" & Forms!frmSearch_Detail_After!Hours & "', '" & Forms!frmSearch_Detail_After!Trainer & "')"";

 

[Remou]

Are you sure Hours is a text field?

Dim frm As Form

Set frm = Forms!frmSearch_Detail_After

strsqlapp1 = "INSERT INTO Training_Taken (Employee_Number, Job_Title, BPML1, BPML2, BPML3, BPML4, BPML5, BPML6, BPML7, BPML8, BPML9," _
& "BPML10, BPML11, BPML12, trnDate, trnTime, AC, Training_Status, Course, Course_Description, Hours, Trainer)" _
& " VALUES (" & frm!employee & ", '" & frm!Job_Title & "'" _
& ", '" & frm!BPML1 & "', '" & frm!BPML2 & "'" _
& ", '" & frm!BPML3 & "', '" & frm!BPML4 & "'" _
& ", '" & frm!BPML5 & "', '" & frm!BPML6 & "'" _
& ", '" & frm!BPML7 & "', '" & frm!BPML8 & "'" _
& ", '" & frm!BPML9 & "', '" & frm!BPML10 & "'" _
& ", '" & frm!BPML11 & "', '" & frm!BPML12 & "'" _
& ", '" & frm!trnDate & "', '" & frm!trnTime & "'" _
& ", '" & "A" & "' , '" & frm!Training_Status & "', '" & frm!Course & "'" _
& ", '" & frm!Description & "', '" & frm!Hours & "', '" & frm!Trainer & "')"

 

[bdjb]

Thank you very much, that fixed it. I'm trying to learn this as I go, and everyone's input really helps in cleaning things up.

 

[genomon]

Glad you got it running!

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[Remou]

You may want to consider parameter queries, they are neater. Here are some notes:

    Set db = CurrentDb

    strSQL="PARAMETERS txtHospital Text(255) " _
       & "INSERT INTO tblHospitals ( [Hospital] ) " _
       & "VALUES ( [txtHospital] )"

    Set qdf = db.CreateQueryDef("", strSQL)
    qdf.Parameters!txtHospital = Trim(frm!HospName)
    
    qdf.Execute dbFailOnError