How do I add a line break inside an sql field

[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.

 

[Clifford Bass]

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

 

[John W. Vinson]

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.

"First Line Text" & Chr(13) & Chr(10) & "Second Line Text"

 

[Curis]

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

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.

 

[Clifford Bass]

Hi Curis,

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

Clifford Bass

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

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.

 

[Curis]

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

 

[Clifford Bass]

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

 

[M_]

Thanks to all.

John: Thanks! I was having a senior moment.

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

 

[M_]

Thanks to all.

John: Thanks! I was having a senior moment.

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

 

[Clifford Bass]

Hi M,

You are welcome!

Clifford Bass

 

[Alex Vol]

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:

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: