How do I add a line break inside an sql field

  • Thread starter Thread starter M_
  • Start date Start date
M

M_

I am creating text that I am going to add into an sql query, but I can't
remember how to add a hard line break. I know that I have to use 10 and 13,
but can't remember how to separate these numbers from the actual text.
 
Hi M,

I like to do this somewhat different that perhaps others would suggest.
In a module place the following function:

Public Function CrLf() As String

CrLf = vbCrLf

End Function

Then in your SQL you can use the function.

select [SomeTextColumn] & CrLf() & [SomeOtherTextColumn] & CrLf() &
"Some Text" as [TwoColumnsAndSomeText] ....

That way, no need to remember 10 and 13 or which order they should go.
Plus it is slightly shorter to type and it is more obvious to someone reading
it what is happening.

Clifford Bass
 
I am creating text that I am going to add into an sql query, but I can't
remember how to add a hard line break. I know that I have to use 10 and 13,
but can't remember how to separate these numbers from the actual text.
Click to expand...

"First Line Text" & Chr(13) & Chr(10) & "Second Line Text"
 
I'm curious... why bother with the custom function? Why not just concatenate
the vbCrLf constant directly, as in:

select [SomeTextColumn] & vbCrLf & [SomeOtherTextColumn] & vbCrLf &
"Some Text" as [TwoColumnsAndSomeText] ....

I just want to make sure that I am not doing anything fundamentally
incorrect by using the VB constant directly.

- Curis

Clifford Bass said:
Hi M,

I like to do this somewhat different that perhaps others would suggest.
In a module place the following function:

Public Function CrLf() As String

CrLf = vbCrLf

End Function

Then in your SQL you can use the function.

select [SomeTextColumn] & CrLf() & [SomeOtherTextColumn] & CrLf() &
"Some Text" as [TwoColumnsAndSomeText] ....

That way, no need to remember 10 and 13 or which order they should go.
Plus it is slightly shorter to type and it is more obvious to someone reading
it what is happening.

Clifford Bass

M_ said:
I am creating text that I am going to add into an sql query, but I can't
remember how to add a hard line break. I know that I have to use 10 and 13,
but can't remember how to separate these numbers from the actual text.
Click to expand...
Click to expand...
 
Hi Curis,

The vbCrLf constant is not available in Access query; hence the need to
wrap it in a function.

Clifford Bass

Curis said:
I'm curious... why bother with the custom function? Why not just concatenate
the vbCrLf constant directly, as in:

select [SomeTextColumn] & vbCrLf & [SomeOtherTextColumn] & vbCrLf &
"Some Text" as [TwoColumnsAndSomeText] ....

I just want to make sure that I am not doing anything fundamentally
incorrect by using the VB constant directly.

- Curis

Clifford Bass said:
Hi M,

I like to do this somewhat different that perhaps others would suggest.
In a module place the following function:

Public Function CrLf() As String

CrLf = vbCrLf

End Function

Then in your SQL you can use the function.

select [SomeTextColumn] & CrLf() & [SomeOtherTextColumn] & CrLf() &
"Some Text" as [TwoColumnsAndSomeText] ....

That way, no need to remember 10 and 13 or which order they should go.
Plus it is slightly shorter to type and it is more obvious to someone reading
it what is happening.

Clifford Bass

M_ said:
I am creating text that I am going to add into an sql query, but I can't
remember how to add a hard line break. I know that I have to use 10 and 13,
but can't remember how to separate these numbers from the actual text.
Click to expand...
Click to expand...
Click to expand...
 
I see.

I misunderstood the request of the OP. I thought the SQL string was getting
pieced together and run via VBA. Apparently, I need to work on my reading
comprehension.

- Curis
 
Hi Curis,

Or maybe you did understand. He/She was not entirely clear. Be that
as it may, even in VBA it still would not work. Give a try and see if you
wish.

Clifford Bass
 
Thanks to all.

John: Thanks! I was having a senior moment.

Clifford, I'm going to try yours. Thanks again for the input.
 
Thanks to all.

John: Thanks! I was having a senior moment.

Clifford, I'm going to try yours. Thanks again for the input.
 
Hi Clifford

I tried the CrLf function and it worked until I quit Access, it then asked me to save the new module (I did, naming it LineBreak). When I opened the db again the I got a "undefined function CrLf in expression" error. And even after deleting the module and making a new one I can't get it to work (it did work the very first time).

Do you know what might be happening? Do I have to save the function in a specific module/place to make it available to the query?

Thanks,

Alex
I am creating text that I am going to add into an sql query, but I can't
remember how to add a hard line break. I know that I have to use 10 and 13,
but can't remember how to separate these numbers from the actual text.
On Thursday, November 13, 2008 12:37 PM CliffordBas wrote:
Hi M,

I like to do this somewhat different that perhaps others would suggest.
In a module place the following function:

Public Function CrLf() As String

CrLf = vbCrLf

End Function

Then in your SQL you can use the function.

select [SomeTextColumn] & CrLf() & [SomeOtherTextColumn] & CrLf() &
"Some Text" as [TwoColumnsAndSomeText] ....

That way, no need to remember 10 and 13 or which order they should go.
Plus it is slightly shorter to type and it is more obvious to someone reading
it what is happening.

Clifford Bass

"M_" wrote:
Click to expand...
why bother with the custom function? Why not just concatenate
the vbCrLf constant directly, as in:

select [SomeTextColumn] & vbCrLf & [SomeOtherTextColumn] & vbCrLf &
"Some Text" as [TwoColumnsAndSomeText] ....

I just want to make sure that I am not doing anything fundamentally
incorrect by using the VB constant directly.

- Curis

"Clifford Bass" wrote:
Click to expand...
Click to expand...
Click to expand...
 
Back
Top