formatting slq statement to include linefeeds in value returned

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
http://I.Am/DougSteele
(no private e-mails, please)


"Vsn" <vsn at hotmail> wrote in message
news:%

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" wrote in message
news:

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.

"Krista H" wrote:

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 & " ;"

"Douglas J. Steele" wrote:

> 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
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Krista H" wrote in message
> news:
> > yes I tried adding vbcrlf to the sql statement, Access popped up a
> > message
> > asking me to enter in the value of vbcrlf.
> >
> > "Piet Linden" wrote:
> >
> >> On May 9, 6:03 pm, Krista H
> >> 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?
> >>
>
>
>