K
Kurt Monroe
I'm trying get 50 character chunks of a large text field
(it's a "long" column in a linked Oracle table) one at a
time so I can put them into 50 character text columns in
an Access table.
I create a recordset on the Oracle table, then I use
the "Mid" VBA function to set variables equal to the
chunks. This Oracle column is displayed in a text box
with "hard" returns - if the user inserts a "line feed" in
the text, it moves the remaining text down one line, as
expected. I want to reproduce the lines exactly as they
appear - if I find a "line feed", then I don't want to
fetch any more characters. VBA seems to do this on its
own somehow and that's OK. It actually stops fetching when
it encounters a "line feed", though I don't explicitly
tell it to do so.
BUT, I can't figure out how to use the "line feed" in the
Mid function. I need to somehow go through the text and
store the location of the next-found line feed and then
use that location to start the next fetch of 50 characters
(or up to the next line feed it encounters within the next
50 characters). If I simply increment the start position
by 50, it treats any line feeds as a character, and it
skips text. So, if I could capture the position that it
stops fetching (which would be the position of the next
line feed) and then use that position plus 1 to start the
next fetch, all would be fine.
Here is a sample of my code. For this example, I start my
comments on the same line after an apostrophe:
Dim varRemarks As String 'the whole column from the table
Dim SubRemarks1 As String 'I put the first chunk here
Dim SubRemarks2 As String 'I put the second chunk here
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rsCDRL As Recordset 'the Oracle table is named CDRLs
Dim strsql As String 'used to fetch the column
strsql = "Select remarks from CDRLs where cdrl_id
= '20026'" 'this gets the "long" Oracle column
named "remarks"
Set dbs = CurrentDb
Set rsCDRL = dbs.OpenRecordset(strsql)
varRemarks = rsCDRL("remarks") 'puts the text into a local
variable
varSubRemarks1 = Mid(varRemarks, 1, 50) 'gets the first
chunk
varSubRemarks2 = Mid(varRemarks, 51, 50) 'gets the second
chunk
'the following inserts the chunks into a local Access table
'named "af1423". Columns m1 and m2 are text(50)
strsql = "insert into af1423(m1,m2) values ('" &
varSubRemarks1 & "','" & varSubRemarks2 & "')"
set rsCDRL = nothing
The Oracle column, when displayed in a text box, looks
exactly like this (in between the **** lines below):
****
This is line one, it stops here.
This is line 2, done with a hard return.
****
I did in fact put a "hard return" after line one.
My code puts "This is line one, it stops here" in m1, but
then it puts "done with a hard return." in m2. (The "D"
in done is in the 51st position.)
Can anyone help me find the position of the hard returns
and then use that position to get the next 50 or less
characters?
Thanks in advance,
Kurt
(it's a "long" column in a linked Oracle table) one at a
time so I can put them into 50 character text columns in
an Access table.
I create a recordset on the Oracle table, then I use
the "Mid" VBA function to set variables equal to the
chunks. This Oracle column is displayed in a text box
with "hard" returns - if the user inserts a "line feed" in
the text, it moves the remaining text down one line, as
expected. I want to reproduce the lines exactly as they
appear - if I find a "line feed", then I don't want to
fetch any more characters. VBA seems to do this on its
own somehow and that's OK. It actually stops fetching when
it encounters a "line feed", though I don't explicitly
tell it to do so.
BUT, I can't figure out how to use the "line feed" in the
Mid function. I need to somehow go through the text and
store the location of the next-found line feed and then
use that location to start the next fetch of 50 characters
(or up to the next line feed it encounters within the next
50 characters). If I simply increment the start position
by 50, it treats any line feeds as a character, and it
skips text. So, if I could capture the position that it
stops fetching (which would be the position of the next
line feed) and then use that position plus 1 to start the
next fetch, all would be fine.
Here is a sample of my code. For this example, I start my
comments on the same line after an apostrophe:
Dim varRemarks As String 'the whole column from the table
Dim SubRemarks1 As String 'I put the first chunk here
Dim SubRemarks2 As String 'I put the second chunk here
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rsCDRL As Recordset 'the Oracle table is named CDRLs
Dim strsql As String 'used to fetch the column
strsql = "Select remarks from CDRLs where cdrl_id
= '20026'" 'this gets the "long" Oracle column
named "remarks"
Set dbs = CurrentDb
Set rsCDRL = dbs.OpenRecordset(strsql)
varRemarks = rsCDRL("remarks") 'puts the text into a local
variable
varSubRemarks1 = Mid(varRemarks, 1, 50) 'gets the first
chunk
varSubRemarks2 = Mid(varRemarks, 51, 50) 'gets the second
chunk
'the following inserts the chunks into a local Access table
'named "af1423". Columns m1 and m2 are text(50)
strsql = "insert into af1423(m1,m2) values ('" &
varSubRemarks1 & "','" & varSubRemarks2 & "')"
set rsCDRL = nothing
The Oracle column, when displayed in a text box, looks
exactly like this (in between the **** lines below):
****
This is line one, it stops here.
This is line 2, done with a hard return.
****
I did in fact put a "hard return" after line one.
My code puts "This is line one, it stops here" in m1, but
then it puts "done with a hard return." in m2. (The "D"
in done is in the 51st position.)
Can anyone help me find the position of the hard returns
and then use that position to get the next 50 or less
characters?
Thanks in advance,
Kurt