Cross Tab Query Convert Header to Dates

Question

Hi, 

I have a cross tab query where my headers are dates. When I link to the data in Excel all of the headers becomes string. This prevents me from looking up the headers unless I write an array formula. Is there a way to make it so that the headers are linked as a date format? Thanks in advance.

Answer 1

Post the SQL of your crosstab query.

---

Build a little, test a little

Answer 2

Thanks for reply Karl. 

TRANSFORM Sum(Tbl_011_CurrentActivity_All.Qty) AS SumOfQty
SELECT Tbl_011_CurrentActivity_All.Key, Tbl_011_CurrentActivity_All.BR, Tbl_011_CurrentActivity_All.Type
FROM Tbl_011_CurrentActivity_All
GROUP BY Tbl_011_CurrentActivity_All.Key, Tbl_011_CurrentActivity_All.BR, Tbl_011_CurrentActivity_All.Type
PIVOT Tbl_011_CurrentActivity_All.Month;

Answer 3

You can try --   PIVOT Str(Tbl_011_CurrentActivity_All.Month);

What is the datatype of Tbl_011_CurrentActivity_All.Month? 

---

Build a little, test a little

Answer 4

The Month field is a date. I'll try that formula.

Answer 5

Karl,

I looks like it links it as a string also. I tried datevalue after PIVOT and still no luck.

Answer 6

You can try --   PIVOT Format(Tbl_011_CurrentActivity_All.Month, "mmmm d  yyyy");

---

Build a little, test a little

Answer 7

Looks like that is also a string.

Answer 8

Yes it produces a string which is what I thought you wanted.   

Headers are always text so far as I have worked with as they are just labels.

What are you wanting to be in the Excel file?

---

Build a little, test a little

Answer 9

I would like it to be as a date. That way I can look up the dates when I write Index Match formulas. I can look them up but I would have to write array formulas. I have several hundred lines which extremely bogs down the spreadsheet.

Answer 10

Try this --

In the Excel file click on the row number that has the headers so it highlight the entire row.   Then right click the row number, select Format Fields, and format as date.

---

Build a little, test a little

Answer 11

I tried it and it did not change the format. 

Answer 12

I am at a loss.

---

Build a little, test a little

Answer 13

Sorry maybe I should explain this a little more in detail. I've built the cross tab query in Access which is a product of multiple queries and linked ODBC tables. When I link the cross tab query to Excel, it is a list object table that can be refreshed. Now the headers on this list object is only in text (string) format. I would like like these text headers to be date format on the spreadsheet.  

Answer 14

 When I link the cross tab query to Excel, it is a list object table that can be refreshed.

How are you linking the crosstab query to the Excel file?

My Access 2007 says you cannot 'push' data from Access to a linked Excel file.

---

Build a little, test a little

Answer 15

Through an ODBC DSN.

Answer 16

Sorry, it's been over 10 years since I worked with ODBC connections.

---

Build a little, test a little

Answer 17

I think you should start new thread with title something like this --

Access ODBC DSN to Excel changes crosstab date headers to text

---

Build a little, test a little

Answer 18

Hi Lo,

Based on my test with Access 2013 and Excel 2013, I can change the header format to Date.

The detail steps:

1. Create Cross Tab Query in access

TRANSFORM Sum(UserInfo.[Age]) AS AgeOfSum
SELECT UserInfo.[UserName], Sum(UserInfo.[Age]) AS [Total Of Age]
FROM UserInfo
GROUP BY UserInfo.[UserName]
PIVOT Format([CreateData],"Short Date");

1. Open a excel file
2. Click Data=>From Other Sources=>From Microsoft Query=>Select MS Access Database=>select access file=>select query=>create table
3. Select a header and change to short date.

Please provide your detail steps.

Regards

Starain

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 19

Starain, 

It looks like it is still a text of the date. 

Karl,

I'll start a new thread. 

Thanks for everyone's input. It may just be that this is not possible.

Answer 20

Hi,

When I link a cross tab query from Access to Excel, the headers are only in text format. I would like like these text headers to be date format on the spreadsheet for purposes of looking up the date. Is this possible? Thank you.

Answer 21

Hi Lo,

>>It looks like it is still a text of the date.

What do you mean it is still a text of the date? Do your steps are same to me?

Regards

Starain

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 22

>>>I would like like these text headers to be date format on the spreadsheet for purposes of looking up the date. Is this possible?

According to your description, based on my testing the date field is formatted as Date/Time in Excel, refer to below screenshot:

For more information, click here to refer about Make summary data easier to read by using a crosstab query

In addition could you provide more information about your issue, screenshot is best, that will help us reproduce and resolve it.

Thanks for your understanding.

Answer 23

Would you be able to look up those headers?

Answer 24

>>>Would you be able to look up those headers?

Sorry, I am not clear about what you mean that look up those headers, if I don't misunderstand, you could want to find table header name, you could refer to below code:

Dim tb As ListObject
    'assumes Table is the Table_Query_from_MS_Access_Database on the ActiveSheet
Set tb = ActiveSheet.ListObjects("Table_Query_from_MS_Access_Database")
    
For i = 1 To tb.ListColumns.Count
    Debug.Print tb.ListColumns(i).Name
    Debug.Print tb.ListColumns(i).Index
Next

In addition could you provide more information about your issue, for example sample code, screenshot etc., that will help us reproduce and resolve it.

Thanks for your understanding.

Answer 25

Hi Lo,

>> Would you be able to look up those headers?

Do you mean Data Validation? If so, you need to change the number format for it too, by default the format is general.

Regards

Starain

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 26

No not data validation. Just changing the headers to date format for look up purposes. It looks like there is no way to do this.

Answer 27

Hi Lo,

What do you mean “for look up purposes”? As I said that I can change the headers’ format in excel manually, what’s the difference between yours requirement?

Regards

Starain

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 28

Starain, 

I linked cross tab query from Access to Excel. When the table comes over, all the headers are in text format. Although it displays a date (i.e. 02/04/2016) it is formatted as a text. When we do an index match formula to look up the dates it will not find it unless we insert DATEVALUE prior to the header range. To get this to work it has to be an array formula. Having numerous array formulas severely bogs down the spread sheet. 

Answer 29

Hi Lo,

Could you change the number format manually in the excel?

Regards

Starain

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 30

Starain,

It's list object and will not change its format. Thanks.

Answer 31

Hi,

What’s wrong with my steps?

1. Open a excel file
2. Click Data=>From Other Sources=>From Microsoft Query=>Select MS Access Database=>select access file=>select query=>create table
3. Select a header and change to short date.

Regards

Starain

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 32

Starain,

Try to do an Index Match formula to find the desire date column on that table. 

Answer 33

Hi,

Could you provide the sample?

Regards

Starain

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.