By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,523 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

2-column Listbox - punctuation interpreted as column delimiters

P: n/a
I need some help with this problem.
I'm using Access 2002 with XP.

My problem is with a 2-column listbox.
My VBA code puts text data into the listbox.
The problem is that the text data ocasionally includes commas (,) and
full-stops (.) - and when the data is placed in a column, Access confuses
the punctuation within the text with delimiters and splits the text across
columns.

Here are some details:

There are 2 'source' listboxes - lstSelectedCompetencies and lstQuestions.
After the user has selected an item from each listbox, my code tries to put
the 2 selections into another listbox called lstInterviewGuide which has 2
columns. Here is the line of code that tries to put the first selection into
column 1 and second selection into column 2:

lstInterviewGuide.AddItem lstSelectedCompetencies.Value & ";" &
lstQuestions.Value

The problem occurs with data such as the following:
If lstSelectedCompetencies.Value = "Problem Solving"
and lstQuestions.Value = "Think of a time when you were under time pressure,
and had to solve a complex problem"
the result in lstInterviewGuide looks like this:

Column 1
Column 2
Item(0) Problem Solving
Think of a time when you were under time pressure
Item(1) and had to solve a complex problem

whereas the result I want is this:
Column 1
Column 2
Item(0) Problem Solving
Think of a time when you were under time pressure, and had to solve a
complex problem

Can you tell me how to stop VBA interpreting punctuation within data text as
column delimiters?

Thanks for your ideas / advice

Peter
May 2 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Loss of formatting in my original message may have caused confusion....
Just to clarify....

The result puts "Problem Solving" into column 1, and then puts "Think of a
time when you were under time pressure" into column 2,
and then interprets the comma (,) as a column delimiter, so puts the rest of
the text ("and had to solve a complex problem") into the 1st column of the
next item.

How do I stop it splitting the data like that?

NB - please reply to whole group, not to my email - it is not displaying
correctly in this post.

Thanks

Peter
----------------------------------------------------
"Peter Gibbs" <pe******@paradise.net.nz> wrote in message
news:44********@clear.net.nz...
I need some help with this problem.
I'm using Access 2002 with XP.

My problem is with a 2-column listbox.
My VBA code puts text data into the listbox.
The problem is that the text data ocasionally includes commas (,) and
full-stops (.) - and when the data is placed in a column, Access confuses
the punctuation within the text with delimiters and splits the text across
columns.

Here are some details:

There are 2 'source' listboxes - lstSelectedCompetencies and lstQuestions.
After the user has selected an item from each listbox, my code tries to
put the 2 selections into another listbox called lstInterviewGuide which
has 2 columns. Here is the line of code that tries to put the first
selection into column 1 and second selection into column 2:

lstInterviewGuide.AddItem lstSelectedCompetencies.Value & ";" &
lstQuestions.Value

The problem occurs with data such as the following:
If lstSelectedCompetencies.Value = "Problem Solving"
and lstQuestions.Value = "Think of a time when you were under time
pressure, and had to solve a complex problem"
the result in lstInterviewGuide looks like this:

Column 1 Column 2
Item(0) Problem Solving Think of a time when you were under time
pressure
Item(1) and had to solve a complex problem

whereas the result I want is this:
Column 1 Column 2
Item(0) Problem Solving Think of a time when you were under time
pressure, and had to solve a complex problem

Can you tell me how to stop VBA interpreting punctuation within data text
as column delimiters?

Thanks for your ideas / advice

Peter

May 2 '06 #2

P: n/a
"Peter Gibbs" <pe******@paradise.net.nz> wrote in
news:44********@clear.net.nz:
Loss of formatting in my original message may have caused
confusion.... Just to clarify....

The result puts "Problem Solving" into column 1, and then puts
"Think of a time when you were under time pressure" into
column 2, and then interprets the comma (,) as a column
delimiter, so puts the rest of the text ("and had to solve a
complex problem") into the 1st column of the next item.

How do I stop it splitting the data like that?

NB - please reply to whole group, not to my email - it is not
displaying correctly in this post.

Thanks

Peter
----------------------------------------------------
"Peter Gibbs" <pe******@paradise.net.nz> wrote in message
news:44********@clear.net.nz...
I need some help with this problem.
I'm using Access 2002 with XP.

My problem is with a 2-column listbox.
My VBA code puts text data into the listbox.
The problem is that the text data ocasionally includes commas
(,) and full-stops (.) - and when the data is placed in a
column, Access confuses the punctuation within the text with
delimiters and splits the text across columns.

Here are some details:

There are 2 'source' listboxes - lstSelectedCompetencies and
lstQuestions. After the user has selected an item from each
listbox, my code tries to put the 2 selections into another
listbox called lstInterviewGuide which has 2 columns. Here is
the line of code that tries to put the first selection into
column 1 and second selection into column 2:

lstInterviewGuide.AddItem lstSelectedCompetencies.Value & ";"
& lstQuestions.Value

The problem occurs with data such as the following:
If lstSelectedCompetencies.Value = "Problem Solving"
and lstQuestions.Value = "Think of a time when you were under
time pressure, and had to solve a complex problem"
the result in lstInterviewGuide looks like this:

Column 1 Column 2
Item(0) Problem Solving Think of a time when you were
under time pressure
Item(1) and had to solve a complex problem

whereas the result I want is this:
Column 1 Column 2
Item(0) Problem Solving Think of a time when you were
under time pressure, and had to solve a complex problem

Can you tell me how to stop VBA interpreting punctuation
within data text as column delimiters?

Thanks for your ideas / advice

Peter

I don't know what version of Access you are using, but
in '97 and in 2K3:
"Problem solving";"Think, think again. then a third time" gets
handled properly.
Problem solving;Think, think again. then a third time
does not.

In other words, put quotes around your data.
--
Bob Quintal

PA is y I've altered my email address.
May 2 '06 #3

P: n/a
Hi Bob

Thanks for the reply.
I'm using Access 2002 with WinXP Professional.

What you suggest doesn't work for me.
But I have discovered that if I use the Replace method and replace every
comma with Chr$(130) it works fine.
I'm happy to have found a solution, but still puzzled as to why.

Peter
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
"Peter Gibbs" <pe******@paradise.net.nz> wrote in
news:44********@clear.net.nz:
Loss of formatting in my original message may have caused
confusion.... Just to clarify....

The result puts "Problem Solving" into column 1, and then puts
"Think of a time when you were under time pressure" into
column 2, and then interprets the comma (,) as a column
delimiter, so puts the rest of the text ("and had to solve a
complex problem") into the 1st column of the next item.

How do I stop it splitting the data like that?

NB - please reply to whole group, not to my email - it is not
displaying correctly in this post.

Thanks

Peter
----------------------------------------------------
"Peter Gibbs" <pe******@paradise.net.nz> wrote in message
news:44********@clear.net.nz...
I need some help with this problem.
I'm using Access 2002 with XP.

My problem is with a 2-column listbox.
My VBA code puts text data into the listbox.
The problem is that the text data ocasionally includes commas
(,) and full-stops (.) - and when the data is placed in a
column, Access confuses the punctuation within the text with
delimiters and splits the text across columns.

Here are some details:

There are 2 'source' listboxes - lstSelectedCompetencies and
lstQuestions. After the user has selected an item from each
listbox, my code tries to put the 2 selections into another
listbox called lstInterviewGuide which has 2 columns. Here is
the line of code that tries to put the first selection into
column 1 and second selection into column 2:

lstInterviewGuide.AddItem lstSelectedCompetencies.Value & ";"
& lstQuestions.Value

The problem occurs with data such as the following:
If lstSelectedCompetencies.Value = "Problem Solving"
and lstQuestions.Value = "Think of a time when you were under
time pressure, and had to solve a complex problem"
the result in lstInterviewGuide looks like this:

Column 1 Column 2
Item(0) Problem Solving Think of a time when you were
under time pressure
Item(1) and had to solve a complex problem

whereas the result I want is this:
Column 1 Column 2
Item(0) Problem Solving Think of a time when you were
under time pressure, and had to solve a complex problem

Can you tell me how to stop VBA interpreting punctuation
within data text as column delimiters?

Thanks for your ideas / advice

Peter

I don't know what version of Access you are using, but
in '97 and in 2K3:
"Problem solving";"Think, think again. then a third time" gets
handled properly.
Problem solving;Think, think again. then a third time
does not.

In other words, put quotes around your data.
--
Bob Quintal

PA is y I've altered my email address.

May 3 '06 #4

P: n/a
"Peter Gibbs" <pe******@paradise.net.nz> wrote in
news:44********@clear.net.nz:
Hi Bob

Thanks for the reply.
I'm using Access 2002 with WinXP Professional.

What you suggest doesn't work for me.
But I have discovered that if I use the Replace method and
replace every comma with Chr$(130) it works fine.
I'm happy to have found a solution, but still puzzled as to
why.

Peter
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
"Peter Gibbs" <pe******@paradise.net.nz> wrote in
news:44********@clear.net.nz:
Loss of formatting in my original message may have caused
confusion.... Just to clarify....

The result puts "Problem Solving" into column 1, and then
puts "Think of a time when you were under time pressure"
into column 2, and then interprets the comma (,) as a column
delimiter, so puts the rest of the text ("and had to solve a
complex problem") into the 1st column of the next item.

How do I stop it splitting the data like that?

NB - please reply to whole group, not to my email - it is
not displaying correctly in this post.

Thanks

Peter
----------------------------------------------------
"Peter Gibbs" <pe******@paradise.net.nz> wrote in message
news:44********@clear.net.nz...
I need some help with this problem.
I'm using Access 2002 with XP.

My problem is with a 2-column listbox.
My VBA code puts text data into the listbox.
The problem is that the text data ocasionally includes
commas (,) and full-stops (.) - and when the data is placed
in a column, Access confuses the punctuation within the
text with delimiters and splits the text across columns.

Here are some details:

There are 2 'source' listboxes - lstSelectedCompetencies
and lstQuestions. After the user has selected an item from
each listbox, my code tries to put the 2 selections into
another listbox called lstInterviewGuide which has 2
columns. Here is the line of code that tries to put the
first selection into column 1 and second selection into
column 2:

lstInterviewGuide.AddItem lstSelectedCompetencies.Value &
";" & lstQuestions.Value

The problem occurs with data such as the following:
If lstSelectedCompetencies.Value = "Problem Solving"
and lstQuestions.Value = "Think of a time when you were
under time pressure, and had to solve a complex problem"
the result in lstInterviewGuide looks like this:

Column 1 Column 2
Item(0) Problem Solving Think of a time when you
were under time pressure
Item(1) and had to solve a complex problem

whereas the result I want is this:
Column 1 Column 2
Item(0) Problem Solving Think of a time when you
were under time pressure, and had to solve a complex
problem

Can you tell me how to stop VBA interpreting punctuation
within data text as column delimiters?

Thanks for your ideas / advice

Peter

I don't know what version of Access you are using, but
in '97 and in 2K3:
"Problem solving";"Think, think again. then a third time"
gets handled properly.
Problem solving;Think, think again. then a third time
does not.

In other words, put quotes around your data.
--
Bob Quintal

PA is y I've altered my email address.

you are generating the valuelist in code. If you debug.print
your value list, I'm sure you will see
"John,Mary,Peter,Paul"
What you need is """John"",""Mary"",""Peter"",""Paul"""

--
Bob Quintal

PA is y I've altered my email address.
May 3 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.