how to display outputted data with a space between each row?

[andrewvranjes]

hi there!

does anyone know how to force a blank line in between rows in data outputted by a query ?

thanks !

 

[raskew]

To jump to a new line, use Carriage Return + Line-Feed (vbCrLf), but not in a query.
Example from the debug window:

myString = "The quick brown fox " & vbCrLf & "jumped over the " _
& vbCrLf & "the lazy dog!"

? mystring
The quick brown fox
jumped over the
the lazy dog!

If you need double-spacing, use & vbCrLf &vbCrLf &

 

[andrewvranjes]

i dont follow you ?

thanks for the info, but i still dont understand. basically i have a query that outputs data. i would like to format it to have a blank row between each row of data it outputs, can this be done? where is the debugger window?

thanks mate

 

[Pat Hartman]

If you want a formatted output, use a report.

When you are viewing the query, you can drag and size the row height but you cannot insert a blank row.

 

[Travis]

make the last field of your query

CHR(13) & CHR(10)


Pat I expected a little more imagination on your part

 

[Pat Hartman]

Why do you think that works? Did you test it?

 

[Travis]

Pat,

I'm insulted . Of course I tested it. by sending these characters as the last field and not using Quotes as text deliminators cause an extra return to appear at the end of the line.

 

[Jon K]

When I tried your method, I got only a field containing the two invisible characters CHR(13) and CHR(10). No extra rows, i.e. records, were added.

 

[Travis]

"AAAH now I see" says the blind man.

While what I understood was the desire to add the extra line when the Query is Exported to a Text file, others perceved as wanting to show the extra lines simply when the query was opened.

I appoligize for this lack of understanding of the paradigm.

I do not usually think of using Queries and Tables as input/viewing areas of data. This is what reports and forms are for.

But nevertheless you can still do this (Well somewhat)
Using the "Union All" Query, adding a fraction to the Primary Sort Field (or Unique ID) and then sorting it on that field.

Of course I would be the first to admit that this is inefficient and could never in all my years of coding know where I would want to use this but it can be done.

Just a note: You can Hide the field mySort in the below Example making it look like a blank record inbetween.

Example SQL:

SELECT ID as mySort,ID, Field1, Field2, Field3 FROM 2019AG
Union All
SELECT ID+.1, "","", "" FROM 2019AG

Order by mySort

 

[andrewvranjes]

still cant make it work

hi guys,

im still not fillowing you, i put the expression into the last field in my query, "CHR(13) & CHR(10)" it just processed and displayed as usual, am i doing something wrong?

cheers mates!

 

[andrewvranjes]

travis! here it is

hi travis,

so what i understand is that i should attach your sql code to the end of my sql for this query, here is my sql code

SELECT Companies.CompanyName AS SITE, LogHeader.LogDate AS [CALL DATE], LogHeader.LogHeaderID AS LOGNO, Format([LogTime],'hh:nn:ss') AS [CALL TIME], Left([ARAccount],6) AS [SITE IDASS], LogDetail.Comments AS DESCRIPTION, Format([ArriveTime],'hh:nn:ss') AS [START TIME], Format([CompletedTime],'hh:nn:ss') AS [FINISH TIME], Format([ArriveDate],'Short Date') AS [RESPONSE DATE], ([logheader].[completeddate]+[completedtime])-([arrivedate]+[arrivetime]) AS DURATION, LogHeader.Parent AS PARENT, LogHeader.Child AS CHILD, LogHeader.Status AS STATUS, " " AS IN_OUT, " " AS [IN_OUT ELAPSED], Chr(13) & Chr(10) AS Expr1
FROM ((((LogHeader INNER JOIN LogDetail ON LogHeader.LogHeaderID = LogDetail.LogHeaderID) INNER JOIN Companies ON LogHeader.CompanyID = Companies.CompanyID) INNER JOIN Jobs ON LogHeader.JobCode = Jobs.JobCode) INNER JOIN Addresses ON Companies.CompanyID = Addresses.CompanyID) INNER JOIN LogServiceType ON LogHeader.ServiceType = LogServiceType.ServiceType
WHERE (((Companies.CompanyName) Like "*MOBIL*") AND ((LogHeader.LogDate) Between [Enter Start Date] And [Enter End Date]) AND ((Addresses.Address4) Is Null Or (Addresses.Address4)=[Enter State eg VIC]) AND ((Jobs.Description) Like "*CARWASH*") AND ((LogDetail.DetailType)="O")) OR (((Companies.CompanyName) Like "*MOBIL*") AND ((Format([ArriveDate],'Short Date')) Between [Enter Start Date] And [Enter End Date]) AND ((Addresses.Address4) Is Null Or (Addresses.Address4)=[Enter State eg VIC]) AND ((Jobs.Description) Like "*CARWASH*") AND ((LogDetail.DetailType)="O") AND ((LogHeader.CompletedDate) Between [Enter Start Date] And [Enter End Date]))
ORDER BY Companies.CompanyName, LogHeader.LogDate, Addresses.Address4, [logdate]+[LogTime];

 

[Pat Hartman]

Travis, your method didn't work when I printed the query either. I did not try exporting to a .txt file and printing that although I suspect that might work.

 

[Travis]

Pat,

I did the Export to a txt file and then print of that.

I dealt alot with Main Frame Dumps. That is why I knew about this trick.

 

[Travis]

What I would do is this.

One take the CHR(13) & CHR(10) out of your query. This will work if you were exporting the Query to a Text file.


Create another query based on this Query.

SELECT [SITE] as mySort,"List all Field Names" FROM [QUERY NAME]
Union All
SELECT [SITE] & ".",[Put one "", for each field in your Query] FROM [QUERY NAME]

Order by mySort, [Add additional sort criteria here]

 

[andrewvranjes]

ah! looks easy now!

ah! looks easy now
cheers trabis , i will give this a go, it looks easy once you put in sql like that.

by the way, i have not been able to export the ouputted data as text, normally i just go to file->export->

but its greyed out, any reason why its greyec out this time?

thanks again mate!

so whats the weather like over in USA?

been watching the commonwealth games in Manchester?

Aussie's are kicking Englands butts all over the shop!

later!

 

[Travis]

Its possible that you edited the query and have not saved it. It will not allow you to export an unsaved query.

Where I'm at the weather is in one word "HOT"