Using the pipe character in a T-SQL statement

  • Thread starter Thread starter Margaret Bartley
  • Start date Start date
M

Margaret Bartley

Two questions:

I'm trying to append a record, and put "'text | Text1 |
test2' " in a text field., but something
doesn't like the pipe character.

When I run the following code:
strSQL="INSERT INTO Folders ( FullName ) SELECT 'text |
Text1 | test2' ;"
DoCmd.RunSQL strSQL,

I get the prompt asking me what Text1 is.

When I create that query, in the query builder, and put
"text | Text1 | test2"
in the first row of the grid, (the Field row), when I leave
the cell, the querybuilder changes the text value to
"text|[Text1]|test2", and again
I cannot run the query, I get the same prompt, asking me
where Text1 is.

I remember reading something a long time ago about how to
use a character that
has a reserved meaning, but I can't find it in the Help
files, and I don't know what
to look for when searching the different KBs and websites..

Question 1: How do I compose the string to append a record
with a value with the pipe character in it?

Question 2: Where and how is that information documented?

Many thanks,
Margaret
 
Margaret,

What makes you think the pipe character is a legal character?? It's not!

Exactly what are you trying to do? I can see you want to append a new record
to a table called "Folders", however, I don't understand what you're trying
to do with text, Text1 and test2 (in fact, I don't even know what they're
supposed to be).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
I don't *need* to use the pipe character, I'm listing out
the
folders in my InBox in Outlook, they are four levels deep in
some
places, and I was hoping to use something other than the
back-slash,
to differentiate them from file-system folders.

The "Test", "test1", and "Junk" were obviously-phony
subfolders
in my Outlook Delete folder.

so I would get a recordset like:
"Delete | Test | Test1 | Junk"
"Delete | Test | Test2"
etc.
where that is put in one field.


Since the pipe character is on the keyboard, and I can type
it
(as I did above), I should be able to include it in a
string,
and put that string into a Text field.

I remember reading, several years ago, how to do that - to
tell
the Query Builder that a character was a string literal -
but I forget,
and I can't find it in the Help system. I thought it was
called
Special Characters, but Special Characters is for
non-keyboard characters.




Graham R Seach said:
Margaret,

What makes you think the pipe character is a legal character?? It's not!

Exactly what are you trying to do? I can see you want to append a new record
to a table called "Folders", however, I don't understand what you're trying
to do with text, Text1 and test2 (in fact, I don't even know what they're
supposed to be).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-076455903
6.html


Two questions:

I'm trying to append a record, and put "'text | Text1 |
test2' " in a text field., but something
doesn't like the pipe character.

When I run the following code:
strSQL="INSERT INTO Folders ( FullName ) SELECT 'text |
Text1 | test2' ;"
DoCmd.RunSQL strSQL,

I get the prompt asking me what Text1 is.

When I create that query, in the query builder, and put
"text | Text1 | test2"
in the first row of the grid, (the Field row), when I leave
the cell, the querybuilder changes the text value to
"text|[Text1]|test2", and again
I cannot run the query, I get the same prompt, asking me
where Text1 is.

I remember reading something a long time ago about how to
use a character that
has a reserved meaning, but I can't find it in the Help
files, and I don't know what
to look for when searching the different KBs and websites..

Question 1: How do I compose the string to append a record
with a value with the pipe character in it?

Question 2: Where and how is that information documented?

Many thanks,
Margaret
Click to expand...
Click to expand...
 
Margaret,

I'm still confused, but is this what you're trying to do?
strSQL="INSERT INTO Folders ( FullName ) VALUES ('text | Text1 |
test2')"
CurrentDb.Execute strSQL, dbFailOnError

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Margaret Bartley said:
I don't *need* to use the pipe character, I'm listing out
the
folders in my InBox in Outlook, they are four levels deep in
some
places, and I was hoping to use something other than the
back-slash,
to differentiate them from file-system folders.

The "Test", "test1", and "Junk" were obviously-phony
subfolders
in my Outlook Delete folder.

so I would get a recordset like:
"Delete | Test | Test1 | Junk"
"Delete | Test | Test2"
etc.
where that is put in one field.


Since the pipe character is on the keyboard, and I can type
it
(as I did above), I should be able to include it in a
string,
and put that string into a Text field.

I remember reading, several years ago, how to do that - to
tell
the Query Builder that a character was a string literal -
but I forget,
and I can't find it in the Help system. I thought it was
called
Special Characters, but Special Characters is for
non-keyboard characters.




Graham R Seach said:
Margaret,

What makes you think the pipe character is a legal character?? It's not!

Exactly what are you trying to do? I can see you want to append a new record
to a table called "Folders", however, I don't understand what you're trying
to do with text, Text1 and test2 (in fact, I don't even know what they're
supposed to be).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-076455903
6.html


Two questions:

I'm trying to append a record, and put "'text | Text1 |
test2' " in a text field., but something
doesn't like the pipe character.

When I run the following code:
strSQL="INSERT INTO Folders ( FullName ) SELECT 'text |
Text1 | test2' ;"
DoCmd.RunSQL strSQL,

I get the prompt asking me what Text1 is.

When I create that query, in the query builder, and put
"text | Text1 | test2"
in the first row of the grid, (the Field row), when I leave
the cell, the querybuilder changes the text value to
"text|[Text1]|test2", and again
I cannot run the query, I get the same prompt, asking me
where Text1 is.

I remember reading something a long time ago about how to
use a character that
has a reserved meaning, but I can't find it in the Help
files, and I don't know what
to look for when searching the different KBs and websites..

Question 1: How do I compose the string to append a record
with a value with the pipe character in it?

Question 2: Where and how is that information documented?

Many thanks,
Margaret
Click to expand...
Click to expand...
Click to expand...
 
Yes.

Graham R Seach said:
Margaret,

I'm still confused, but is this what you're trying to do?
strSQL="INSERT INTO Folders ( FullName ) VALUES ('text | Text1 |
test2')"
CurrentDb.Execute strSQL, dbFailOnError

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-076455903
6.html


I don't *need* to use the pipe character, I'm listing out
the
folders in my InBox in Outlook, they are four levels deep in
some
places, and I was hoping to use something other than the
back-slash,
to differentiate them from file-system folders.

The "Test", "test1", and "Junk" were obviously-phony
subfolders
in my Outlook Delete folder.

so I would get a recordset like:
"Delete | Test | Test1 | Junk"
"Delete | Test | Test2"
etc.
where that is put in one field.


Since the pipe character is on the keyboard, and I can type
it
(as I did above), I should be able to include it in a
string,
and put that string into a Text field.

I remember reading, several years ago, how to do that - to
tell
the Query Builder that a character was a string literal -
but I forget,
and I can't find it in the Help system. I thought it was
called
Special Characters, but Special Characters is for
non-keyboard characters.




message news:#[email protected]...
Margaret,

What makes you think the pipe character is a legal character?? It's not!

Exactly what are you trying to do? I can see you want
Click to expand...
to
append a new record
to a table called "Folders", however, I don't
Click to expand...
understand
what you're trying
to do with text, Text1 and test2 (in fact, I don't
Click to expand...
even
know what they're
supposed to be).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
Click to expand...
http://www.wiley.com/WileyCDA/WileyTitle/productCd-076455903
6.html
"Margaret Bartley" <[email protected]>
Click to expand...
wrote
in message
Two questions:

I'm trying to append a record, and put "'text | Text1 |
test2' " in a text field., but something
doesn't like the pipe character.

When I run the following code:
strSQL="INSERT INTO Folders ( FullName ) SELECT 'text |
Text1 | test2' ;"
DoCmd.RunSQL strSQL,

I get the prompt asking me what Text1 is.

When I create that query, in the query builder, and put
"text | Text1 | test2"
in the first row of the grid, (the Field row), when
Click to expand...
I
leave
the cell, the querybuilder changes the text value to
"text|[Text1]|test2", and again
I cannot run the query, I get the same prompt, asking me
where Text1 is.

I remember reading something a long time ago about
Click to expand...
how
to
use a character that
has a reserved meaning, but I can't find it in the Help
files, and I don't know what
to look for when searching the different KBs and websites..

Question 1: How do I compose the string to append a record
with a value with the pipe character in it?

Question 2: Where and how is that information documented?

Many thanks,
Margaret
Click to expand...
Click to expand...
Click to expand...
 
Back
Top