Access Export to CSV

Hi All,

I have got a application and export query to csv. It is running all
right except one problem. The query is a crosstab query and the
columns headers are the monday of 12 weeks since today. The problme
is that if there is no data for the last a couple of weeks, it does
not export the columns header at all. So the data looks like less than
12 weeks.

Is there a way to get around that?

Cheers
Daniel

Hi Daniel

Do those columns appear when you open the query view?

If not, then the problem is you need to define the column headings. You can
do this from the query properties window, or by including an IN() section in
the PIVOT clause of your SQL.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

wrote in message
...
Hi All,

I have got a application and export query to csv. It is running all
right except one problem. The query is a crosstab query and the
columns headers are the monday of 12 weeks since today. The problme
is that if there is no data for the last a couple of weeks, it does
not export the columns header at all. So the data looks like less than
12 weeks.

Is there a way to get around that?

Cheers
Daniel

On May 6, 12:01*pm, "Graham Mandeno"
wrote:
Hi Daniel

Do those columns appear when you open the query view?

If not, then the problem is you need to define the column headings. *You can
do this from the query properties window, or by including an IN() section in
the PIVOT clause of your SQL.

--
Good Luck *:-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

wrote in message

...



Hi All,

I have got a application and export query to csv. It is running all
right except one problem. The query is a crosstab query and the
columns headers are the monday of 12 weeks since today. *The problme
is that if there is no data for the last a couple of weeks, it does
not export the columns header at all. So the data looks like less than
12 weeks.

Is there a way to get around that?

Cheers
Daniel- Hide quoted text -

- Show quoted text -

Hi Graham,

Thank you for you reply, I can not do it in proerpties window because
the headings are dynamic based on the date the qurey is running. Can
you please explain more about the other option? What do you mean IN
section in PIVOT clause in SQL.

Cheers
Daniel

Hi Daniel

The SQL syntax for a crosstab query is:

TRANSFORM value expression
SELECT row header fields
PIVOT column header field

In the PIVOT clause, you can add an IN subclause to specify the column
headers:

PIVOT 'Week' & WeekNumber IN ('Week1', 'Week2', 'Week3')

Data will be omitted if the corresponding header is not in the list (for
example, Week4, Week5, etc would not appear) and the specified columns will
be present even if no data exists for them.

If you want your headings to be dynamic, then you can modify the SQL of your
saved query before exporting it.

First, create a string containing all your headings:

Dim dt as Date, i as Integer, sHeadings as String
dt = calculated start date
For i = 1 to 12
if dt 1 then sHeadings = sHeadings & ", "
sHeadings = sHeadings & "'" & Format(dt, "dd/mm/yy") & "'"
dt = dt + 7
Next

You not have a string like this:
'04/05/09', '11/05/09', '18/05/09', ...

Of course, you must modify the Format function string to match the date
format of your column headers.

Now, modify your query to add some dummy headers - say A,B,C. This will
ensure that the the last thing in your query SQL is:

PIVOT something IN (something);

All we have to do is find the last "(" in the SQL string and replace
everything after it with our sHeadings string, plus ");".

Dim qd as QueryDef, sSQL as string
Set qd = CurrentDb.QueryDefs("query name")
sSQL = qd.SQL
i = InstrRev( sSQL, "(" )
sSQL = Left(sSQL, i) & sHeadings & ");"
qd.SQL = sSQL
qd.Close

Now do your export!

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand



wrote in message
...
On May 6, 12:01 pm, "Graham Mandeno"
wrote:
Hi Daniel

Do those columns appear when you open the query view?

If not, then the problem is you need to define the column headings. You
can
do this from the query properties window, or by including an IN() section
in
the PIVOT clause of your SQL.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

wrote in message

...



Hi All,

I have got a application and export query to csv. It is running all
right except one problem. The query is a crosstab query and the
columns headers are the monday of 12 weeks since today. The problme
is that if there is no data for the last a couple of weeks, it does
not export the columns header at all. So the data looks like less than
12 weeks.

Is there a way to get around that?

Cheers
Daniel- Hide quoted text -

- Show quoted text -

Hi Graham,

Thank you for you reply, I can not do it in proerpties window because
the headings are dynamic based on the date the qurey is running. Can
you please explain more about the other option? What do you mean IN
section in PIVOT clause in SQL.

Cheers
Daniel