formatting slq statement to include linefeeds in value returned

  • Thread starter Thread starter Krista H
  • Start date Start date
K

Krista H

Using Office 2007/Vista

trying to get multiple data fields from a table into one field on a report.
It works great if I want all the data concatenated together, however I would
like add linefeeds/carriage returns between the values. I searched the forum
and online and haven't found a solution that works.

Here is the code without the line feeds: (I would like linefeeds between the
Size, Color and ProductName data)

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
Dim sqlDetail As String

sqlDetail = "Select *, ""Size: "" & Size & "" Color: "" & Color & " & _
" "" Product Name: "" & ProductName AS Description " & _
"from tbl_OrderDetails " & _
"WHERE OrderId=" & glbPrintOrderId & " ;"

Me.RecordSource = sqlDetail

Exit_Report_Open:
Exit Sub

Err_Report_Open:
MsgBox Err.Description
Resume Exit_Report_Open
End Sub
 
Using Office 2007/Vista

trying to get multiple data fields from a table into one field on a report..
It works great if I want all the data concatenated together, however I would
like add linefeeds/carriage returns between the values. I searched the forum
and online and haven't found a solution that works.

Here is the code without the line feeds: (I would like linefeeds between the
Size, Color and ProductName data)

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
    Dim sqlDetail As String

    sqlDetail = "Select  *, ""Size: "" & Size & "" Color: "" & Color & " & _
                " "" Product Name: "" & ProductName  AS Description " & _
                "from tbl_OrderDetails " & _
                "WHERE OrderId=" & glbPrintOrderId & " ;"

    Me.RecordSource = sqlDetail

Exit_Report_Open:
    Exit Sub

Err_Report_Open:
    MsgBox Err.Description
    Resume Exit_Report_Open
End Sub
Click to expand...

did you try something like

Size & vbcrlf & Color
in your SQL statement?
 
yes I tried adding vbcrlf to the sql statement, Access popped up a message
asking me to enter in the value of vbcrlf.
 
While vbCrLf works in VBA, you can't use it in queries. You must use Chr(13)
& Chr(10) (and it must be in that order)
 
I tried that as well already, maybe I am not entering it correctly.

Here is how i modified the select statement: (Access says it is an invalid
procedure call or argument)

sqlDetail = "Select *, ""Size: "" & Size & chr(13) & chr(10) & "" Color: ""
& Color & " & _
" ""Product Name: "" & ProductName & ""Notes: "" & Notes
AS Description " & _
"from tbl_OrderDetails " & _
"WHERE OrderId=" & glbPrintOrderId & " ;"
 
I did also try it with Chr(13) & Chr(10) to make sure that it wasn't case
sensitive and I got the same error.
 
What's actually assigned to sqlDetail? After that line of code, put
Debug.Print, then go to the immediate window (Ctrl-G) and see.
 
Using Office 2007/Vista

trying to get multiple data fields from a table into one field on a report.
It works great if I want all the data concatenated together, however I would
like add linefeeds/carriage returns between the values. I searched the forum
and online and haven't found a solution that works.
Click to expand...

A linefeed consists of two characters in sequence, a Carriage Return (ASCII
value 13) and a Linefeed (ASCII 10) in that order. In VBA there is a constant
vbCrLf returning that pair; it's not available in SQL but you can use the
Chr() function:

sqlDetail = "Select ""Size: "" & Size & vbCrLf _
& "" Color: "" & Color & vbCrLf _
& "" Product Name: "" & ProductName & vbCrLf AS Description " & _
"from tbl_OrderDetails " & _
"WHERE OrderId=" & glbPrintOrderId & " ;"
 
You could just as easily put two double quotes in a row inside the string
where you want a double quote to be:

DIM MySql as STRING

MySql = " " _
& "SELECT FAQ.fID, FAQ.fText " _
& "FROM FAQ " _
& "WHERE (((FAQ.fText) Like ""*"" & [Search] & ""*"")) " _
& "WITH OWNERACCESS OPTION;"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Vsn said:
if i do understand the discussion well; double quotes don't work either,
you could use " & chr(34) & " at each location were you need double
quotes.

original sql statment where [Search] is a variable:-
SELECT FAQ.fID, FAQ.fText
FROM FAQ
WHERE (((FAQ.fText) Like "*" & [Search] & "*"))
WITH OWNERACCESS OPTION;

vb code for the same statment:-
DIM MySql as STRING
MySql = " " _
& "SELECT FAQ.fID, FAQ.fText " _
& "FROM FAQ " _
& "WHERE (((FAQ.fText) Like " & chr(34) & "*" & chr(34) & " & [Search] & "
& chr(34) & "*" & chr(34) & ")) " _
& "WITH OWNERACCESS OPTION;"

hope this helps you toward a solution.

ludovic

Krista H said:
I did also try it with Chr(13) & Chr(10) to make sure that it wasn't case
sensitive and I got the same error.
Click to expand...
Click to expand...
 
if i do understand the discussion well; double quotes don't work either, you
could use " & chr(34) & " at each location were you need double quotes.

original sql statment where [Search] is a variable:-
SELECT FAQ.fID, FAQ.fText
FROM FAQ
WHERE (((FAQ.fText) Like "*" & [Search] & "*"))
WITH OWNERACCESS OPTION;

vb code for the same statment:-
DIM MySql as STRING
MySql = " " _
& "SELECT FAQ.fID, FAQ.fText " _
& "FROM FAQ " _
& "WHERE (((FAQ.fText) Like " & chr(34) & "*" & chr(34) & " & [Search] & " &
chr(34) & "*" & chr(34) & ")) " _
& "WITH OWNERACCESS OPTION;"

hope this helps you toward a solution.

ludovic
 
I was unable to get any of the suggestions to work. I ended up writing a
standard query (Select * From Table ....) and then formating the data with
line feeds in the field on the report.

Thanks anyway.

K
 
Krista H said:
I was unable to get any of the suggestions to work. I ended up writing a
standard query (Select * From Table ....) and then formating the data with
line feeds in the field on the report.
Click to expand...

Perahps 3 seperate text boxes could work here? If you set the text boxes
property "can shrink" to yes, then the report will pull the data up for
blank lines. Certain aspects of the report layout can affect if this, but
for the most part you can usually just drag and drop the text boxes on to
the report, and set the text boxes can shrink property to yes. Make sure the
text boxes don't overlap, or the "can shrink property" will not work
correctly.

The above is just a general suggestion, and of course it's very possible
that your current report layout is more complex than your "example" you gave
here to make your question more easy to understand.
 
did not know that double quotes where possible as well, i will surly try to
use this shorter coding as of now.
thx.
Ludovic

Douglas J. Steele said:
You could just as easily put two double quotes in a row inside the string
where you want a double quote to be:

DIM MySql as STRING

MySql = " " _
& "SELECT FAQ.fID, FAQ.fText " _
& "FROM FAQ " _
& "WHERE (((FAQ.fText) Like ""*"" & [Search] & ""*"")) " _
& "WITH OWNERACCESS OPTION;"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Vsn said:
if i do understand the discussion well; double quotes don't work either,
you could use " & chr(34) & " at each location were you need double
quotes.

original sql statment where [Search] is a variable:-
SELECT FAQ.fID, FAQ.fText
FROM FAQ
WHERE (((FAQ.fText) Like "*" & [Search] & "*"))
WITH OWNERACCESS OPTION;

vb code for the same statment:-
DIM MySql as STRING
MySql = " " _
& "SELECT FAQ.fID, FAQ.fText " _
& "FROM FAQ " _
& "WHERE (((FAQ.fText) Like " & chr(34) & "*" & chr(34) & " & [Search] &
" & chr(34) & "*" & chr(34) & ")) " _
& "WITH OWNERACCESS OPTION;"

hope this helps you toward a solution.

ludovic

Krista H said:
I did also try it with Chr(13) & Chr(10) to make sure that it wasn't case
sensitive and I got the same error.

:

I tried that as well already, maybe I am not entering it correctly.

Here is how i modified the select statement: (Access says it is an
invalid
procedure call or argument)

sqlDetail = "Select *, ""Size: "" & Size & chr(13) & chr(10) & ""
Color: ""
& Color & " & _
" ""Product Name: "" & ProductName & ""Notes: "" &
Notes
AS Description " & _
"from tbl_OrderDetails " & _
"WHERE OrderId=" & glbPrintOrderId & " ;"

:

While vbCrLf works in VBA, you can't use it in queries. You must use
Chr(13)
& Chr(10) (and it must be in that order)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


yes I tried adding vbcrlf to the sql statement, Access popped up a
message
asking me to enter in the value of vbcrlf.

:

On May 9, 6:03 pm, Krista H <[email protected]>
wrote:
Using Office 2007/Vista

trying to get multiple data fields from a table into one field
on a
report..
It works great if I want all the data concatenated together,
however I
would
like add linefeeds/carriage returns between the values. I
searched the
forum
and online and haven't found a solution that works.

Here is the code without the line feeds: (I would like linefeeds
between the
Size, Color and ProductName data)

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
Dim sqlDetail As String

sqlDetail = "Select *, ""Size: "" & Size & "" Color: "" &
Color &
" & _
" "" Product Name: "" & ProductName AS
Description " &
_
"from tbl_OrderDetails " & _
"WHERE OrderId=" & glbPrintOrderId & " ;"

Me.RecordSource = sqlDetail

Exit_Report_Open:
Exit Sub

Err_Report_Open:
MsgBox Err.Description
Resume Exit_Report_Open
End Sub

did you try something like

Size & vbcrlf & Color
in your SQL statement?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top