Carriage returns in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2002, and whenever I design my query I
enter in carriage returns(CR) and line feeds(LF) where
they are needed. However, after I save the query, the
(CR) and (LF) disappear. I was wondering if there was a
way to prevent this from happening.
 
Are you referring to the SQL statement in the SQL view of the query? Or are
you referring to putting CR and LF into text strings? More info, please.
Show an example.
 
Here is what I have entered:
SELECT [ID] & "." AS Expr1, [question] AS Expr2, IIf
([Graph1Title]<>"",[Graph1Title],"") AS Expr11, IIf
([Graph2Title]<>"",[Graph2Title],"") AS Expr12, IIf(([Ques
form]="MC" or [Ques form]="MA") And [dist1]<>"","(CR)
A. " & [dist1],"") AS Expr5, IIf(([Ques form]="MC" Or
[Ques form]="MA") And [dist2]<>"","(CR)
B. " & [dist2],"") AS Expr6, IIf(([Ques form]="MC" or
[Ques form]="MA") And [dist3]<>"","(CR)
C. " & [dist3],"") AS Expr7, IIf(([Ques form]="MC" or
[Ques form]="MA") And [dist4]<>"","(CR)
D. " & [dist4],"") AS Expr8, IIf(([Ques form]="MC" or
[Ques form]="MA") And [dist5]<>"","(CR)
E. " & [dist5],"") AS Expr9, IIf(([Ques form]="MC" or
[Ques form]="MA") And [dist6]<>"","(CR)
F. " & [dist6],"") AS Expr10, IIf([Ques form]="ES","
(CR) essay","(CR)
ans: " & [answer]) AS Expr3, "(CR)
category:" & [template] AS Expr4
FROM TempTest;
(NOTE: The (CR) in the above are not actually included in
my SQL, I put them in to make sure that it would be
understood correctly.

After saving the queryall the (CR) are no longer present.
I have also tried manipulating the entries in the
expression editor and the same thing happens.
 
I would be interested in how to maintain the carriage
return in a SQL statement in Access. THe format is
maintained on a pass-through query but not the SQL view of
a regular query. Thanks!
 
Work exclusively in the SQLView and DasheetView.

As soon as you switch to DesignView (and back to SQLView),
Access will re-arrange your SQL String.

HTH
Van T. Dinh
MVP (Access)
 
The Query Grid ignores these when it pass the String to
JET for processing. You need to use Chr(13) & Chr(10)
like:

SELECT [ID] & "." AS Expr1, [question] AS Expr2,
IIf([Graph1Title]<>"",[Graph1Title],"") AS Expr11,
IIf([Graph2Title]<>"",[Graph2Title],"") AS Expr12,
IIf(([Ques form]="MC" or [Ques form]="MA")
And [dist1]<>"", Chr(13) & Chr(10) & "A. " & [dist1],"")
AS Expr5, ...

HTH
Van T. Dinh
MVP (Access)
 
I am using Access 2002, and whenever I design my query I
enter in carriage returns(CR) and line feeds(LF) where
they are needed. However, after I save the query, the
(CR) and (LF) disappear. I was wondering if there was a
way to prevent this from happening.
Click to expand...

Alas, no. The query optimizer will rearrange your SQL whenever you
close the query design window, not only removing newlines but often
adding or removing parentheses and rearranging the order of operands.

USUALLY - and always, in cases I can document - the logic of the query
is preserved, but its appearance is uniformly devastated.
 
Thank you everyone for your input
-----Original Message-----
The Query Grid ignores these when it pass the String to
JET for processing. You need to use Chr(13) & Chr(10)
like:

SELECT [ID] & "." AS Expr1, [question] AS Expr2,
IIf([Graph1Title]<>"",[Graph1Title],"") AS Expr11,
IIf([Graph2Title]<>"",[Graph2Title],"") AS Expr12,
IIf(([Ques form]="MC" or [Ques form]="MA")
And [dist1]<>"", Chr(13) & Chr(10) & "A. " & [dist1],"")
AS Expr5, ...

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Here is what I have entered:
SELECT [ID] & "." AS Expr1, [question] AS Expr2, IIf
([Graph1Title]<>"",[Graph1Title],"") AS Expr11, IIf
([Graph2Title]<>"",[Graph2Title],"") AS Expr12, IIf (([Ques
form]="MC" or [Ques form]="MA") And [dist1]<>"","(CR)
A. " & [dist1],"") AS Expr5, IIf(([Ques form]="MC" Or
[Ques form]="MA") And [dist2]<>"","(CR)
B. " & [dist2],"") AS Expr6, IIf(([Ques form]="MC" or
[Ques form]="MA") And [dist3]<>"","(CR)
C. " & [dist3],"") AS Expr7, IIf(([Ques form]="MC" or
[Ques form]="MA") And [dist4]<>"","(CR)
D. " & [dist4],"") AS Expr8, IIf(([Ques form]="MC" or
[Ques form]="MA") And [dist5]<>"","(CR)
E. " & [dist5],"") AS Expr9, IIf(([Ques form]="MC" or
[Ques form]="MA") And [dist6]<>"","(CR)
F. " & [dist6],"") AS Expr10, IIf([Ques form]="ES","
(CR) essay","(CR)
ans: " & [answer]) AS Expr3, "(CR)
category:" & [template] AS Expr4
FROM TempTest;
(NOTE: The (CR) in the above are not actually included in
my SQL, I put them in to make sure that it would be
understood correctly.

After saving the queryall the (CR) are no longer present.
I have also tried manipulating the entries in the
expression editor and the same thing happens.
Click to expand...

.
Click to expand...
 
Back
Top