Developer Forum

Hi,

I have month names (coming from a field in a table) as the column
heading in an Access 97 crosstab query. It is being sorted
alphabetically. This will not do. The only way that I know to get
around it is to use month numbers instead, but I'd rather have the
names appear.

Any ideas?

Thanks,



-pw
please use "pw at williamsonenterprises dot com" for e-mail
thanks


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.yqcomputer.com/ The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

1) Open your crosstab query in design mode.
2) Switch to SQL View
3) Find the PIVOT statement in the SQL statement.
4) After the field the PIVOT statement is working on, add in:

IN
("January","February","March","April","May","June","July","August","September","October","November","December")

(watch the word wrap!)

Change the order of the month names to get whatever order you
want/need. By manipulating the IN Clause of the PIVOT statement, you
also control the left-to-right order of the columns. This is a fairly
common "trick" to do (in code) to create reports that appear to always
have the last month as the first column, the month before that as the
2nd column... (aka "rolling columns")

Note: after making this change, and then switching back to "Design
mode", you won't see the in clause. Access doesn't show it, but it's
there, and it persists. (If memory serves, they do appear under the
"ColumnHeadings" property of the field you have selected for a column
heading.)



On Tue, 16 Dec 2003 15:32:38 -0700, pw



--
Better A Bottle In Front Of Me Than A Frontal Lobotomy...

pw,

Since you generally use the query as the datasource for a form or report,
reorder the columns in the bound fields of the form or report object.

If you use the form wizard and create a datasheet default form, it will look
just like the query results anyway, but you can move the columns around and
save the ordering.






News==----
Newsgroups
=---

>

Hi Chuck!

It worked! Except putting in the month name blanks out the zeroes
that were in columns(months) that didn't have any records for that
month. I need the zeroes in any cell that doesn't have any data for
it. The user is going to copy and paste this pivot table into Excel
(I offered to try and do it automatically through Office Automation
but he doesn't mind just copy and pasting it).

Any ideas?

Thanks!

-paul






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.yqcomputer.com/ The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

>pw,

I haven't thought what I am going to do with this query yet :-) Well,
I guess I have. It needs to be somewhere where the user can copy and
paste it into Excel.

I'll mess around with a form and try reordering the columns. Chuck's
suggestion worked though, except adding the month names in code
blanked out any cells that had zeroes in them (unless the column had a
value > 1 somewhere). I think I need zeroes, not blanks.

Thanks!!

-pw








----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.yqcomputer.com/ The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

On Wed, 17 Dec 2003 11:01:17 -0700, pw



Unfortunately, there's really no way around that. Well, there are a
couple of ways, neither of which do I use unless I have to, and that's
pretty darn rare. Personally, I just do my own export to excel when I
have to do this, and use the NZ function as a part of that code to
translate the Nulls. (If you haven't seen my "ExportToExcel"
function, which I've posted here a couple of times, I suggest getting
it. You can put it behind any form and just let the user click on a
button to run the query and create a Excel spreadsheet. I can post it
again, if needed, but....)

The other (non-recommended methods) involve using a query (or a set of
queries) or a table that will ensure that you won't have any "missing"
information for the cross-tab.


--
Drive C: Error. (A)bort (R)etry (I)gnore (K)ick (S)cream

>On Wed, 17 Dec 2003 11:01:17 -0700, pw


Learning lots from you Chuck. I searched this group in Google and I
see a bunch of posts of "ExportToExcel" but I didn't see you name on
them. I want to make sure I get the original :-)

Thanks again,

-paul


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.yqcomputer.com/ The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

n Wed, 17 Dec 2003 20:39:37 -0700, pw
< XXXX@XXXXX.COM > wrote:


Function ExportToExcel(strFileName As String, _
strSheetName As String, _
strSourceName As String, _
Optional bolMsgBoxWhenDone _
As Boolean = False) _
As Long

' strFileName is the Excel File to Create (or use)
' strSheetName is the sheet within the Excel file to create
' strSourceName is the table, query, or SQL string
' to use as the source
' bolMsgBoxWhenDone: Want a msgbox saying "Done"?

Dim myXLDB As DAO.Database
Dim myXLTDF As DAO.TableDef
Dim myXLRst As DAO.Recordset
Dim myDB As DAO.Database
Dim myRst As DAO.Recordset
Dim i As Long
Dim lngRC As Long
Dim lngStatus As Long
Dim varStatus As Variant

Set myXLDB = DBEngine.OpenDatabase(strFileName, _
dbDriverNoPrompt, _
False, _
"Excel 8.0")

Set myDB = CurrentDb
Set myRst = myDB.OpenRecordset(strSourceName)

Set myXLTDF = myXLDB.CreateTableDef(strSheetName)
For i = 0 To myRst.Fields.Count - 1
With myXLTDF
Select Case myRst.Fields(i).Properties("Type")
Case 1
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbBoolean)
Case 2
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbByte)
Case 3
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbInteger)
Case 4
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbLong)
Case 5
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbCurrency)
Case 6
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbSingle)
Case 7
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbDouble)
Case 8
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbDate)
Case 9
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbBinary)
Case 10
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbText)
Case 11
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbLongBinary)
Case 12
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbMemo)
Case 13, 14
' unknown field types.
' No idea what these are!
Case 15
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbGUID)
Case 16
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbBigInt)
Case 17
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbVarBinary)
Case 18
.Fields.Append .CreateField( _
myRst.Fields(i).Name, _
dbChar)
Case 19
.Fi