Excel 2010 - possible to have a Pivot Table cell return more than 256 characters?

X

xyzer

I have some text/notes fields that I return with a pivottable and some of these notes are rather long .. And no it's not practical to set up a vlookupbecause the note is linked on several fields including ticket number and ticket keyer etc. in other words more than one note per ticket etc. i guess i could concatenate but dont feel like it. ithow do I get the pivottable toshow the whole note ?
 
X

xyzer

Thanks for your help Isabelle. I actually do have the field as a row field,but I have found out what the problem is. I am using Microsoft query to pull in data from an access database in which the note field is stored. When it pulls in the note field, even If I don't choose pivottable and merely choose table, the note fields are shortened to 256 characters. So my real issue then is figuring out if Microsoft query can ever pull more than 256 characters. Thanks.
 
I

isabelle

do you have the same problem by exporting the table with DAO ?

here is an exemple
the data is placed in column A.
do not forget to add the reference Microsoft DAO 3.x
attention, there will be unintended line breaks caused by the copy
(caracteres in red)

Table: AtmClient
Field: Name
Criteria: Field: TerminalID like '1N*'"

Sub FillTBL()
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim i As Long, sAccessFile As String

sAccessFile = "C:\Users\isabelle\Documents\db1.mdb" 'to adapt

Set db = DAO.OpenDatabase(sAccessFile, False, False)
Set rec = db.OpenRecordset("SELECT Name FROM AtmClient WHERE TerminalID
like '1N*'", DAO.dbOpenSnapshot)

Do While Not (rec.EOF)
i = i + 1
Range("A" & i) = rec.Fields(0).Value
rec.MoveNext
Loop

rec.Close
db.Close
Set rec = Nothing
Set db = Nothing
End Sub

--
isabelle



Le 2012-11-28 09:09, (e-mail address removed) a écrit :
Thanks for your help Isabelle. I actually do have the field as a row field,

but I have found out what the problem is. I am using Microsoft query

to pull in data from an access database in which the note field is
stored.

When it pulls in the note field, even If I don't choose pivottable and
merely

choose table, the note fields are shortened to 256 characters.

So my real issue then is figuring out if Microsoft query can ever pull
more than 256 characters. Thanks.
 
X

xyzer

actually what i ended up doing was instead of using microsoft query i exported straight from access and kept the formatting to a new spreadsheet. I can run this exporting process in a simple access macro and it does not truncate the memo field. Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top