SQL Query is making me tear what little hair I have left out.

[icezebra]

Afternoon all.

I'm running my own report on the database. This is basically a SQL query constructed depending upon options on the form. All works great and I have it saving to the person's desktop and opening in Excel automatically. I'm recreating the Querydef each time;

So creating this way:

Set QD = Db.CreateQueryDef("QueryResults3", strSQL)

And running this way:

DoCmd.OutputTo acOutputQuery, "QueryResults3", acFormatXLS, strFileName, -1

My problem lies in the fact that some of the fields that the query fetches are Memo fields and thus contain over 256 characters. When the query dumps everything into the spreadsheet, all fields (or now cells) contain no more than 256 characters.

How do I make it output everything! Help!

P.S. Sql code ends up looking something like this:

[SIZE="1"]SELECT MainTable.ID, MainTable.Division, MainTable.Status, MainTable.TPI, MainTable.Pin, MainTable.Scheme, MainTable.CoIssue, MainTable.CoAction, MainTable.CoDate, MainTable.CoPerformance, MainTable.CoTM, MainTable.CoStatus, MainTable.TIssue, MainTable.TAction, MainTable.TDate, MainTable.TPerformance, MainTable.TTM, MainTable.TStatus, MainTable.EIssue, MainTable.EAction, MainTable.EDate, MainTable.EPerformance, MainTable.ETM, MainTable.EStatus, MainTable.StIssue, MainTable.StAction, MainTable.StDate, MainTable.StPerformance, MainTable.StTM, MainTable.StStatus, MainTable.GIssue, MainTable.GAction, MainTable.GDate, MainTable.GPerformance, MainTable.GTM, MainTable.GStatus, MainTable.SaIssue, MainTable.SaAction, MainTable.SaDate, MainTable.SaPerformance, MainTable.SaTM, MainTable.SaStatus, MainTable.OvrallStat, MainTable.MPComment, MainTable.MPPL
FROM MainTable
WHERE (((MainTable.Division) Like '*') AND ((MainTable.CoStatus) Like '*') AND ((MainTable.TStatus) Like '*') AND ((MainTable.EStatus) Like '*') AND ((MainTable.StStatus) Like '*') AND ((MainTable.GStatus) Like '*') AND ((MainTable.SaStatus) Like '*') AND ((MainTable.OvrallStat) Like '*'));[/SIZE]

 

[Dennisk]

I don't think that assumption is correct as I've just entered 1000 charactes into an excel 97 cell. If you memo contains cr/lf that may your problem in that you may not be seeing all the data.

try using the len function

ie in cell a2
=LEN(a1)

 

[icezebra]

Cheers, but it's not that unfortunately. I've tested it by entering in a continuous stream of characters into a memo field in the table "MainTable"... Approximately 1000 characters.

When running the query and viewing the output in Excel, the corresponding cell only contained 255 characters. This is a constant, and none of the 166 records subsequently contains more than 255 characters in any of the fields that have >255 characters in the database, regardless of any cr/lf that may exist.

iz

 

[icezebra]

P.s. If you're suggesting that Excel is truncating the field, then yes, I'm aware that it doesn't... I'm thinking it's something to do with the Query, how I'm executing it or some hidden option in the database.

iz

 

[icezebra]

Anybody? Help!

 

[stopher]

I think I've seen a few posts on this in the past (although I don't use Excel much so I don't take much notice).

Here's a recent comment.

A bit of searching might dig up more help.

hth
Stopher

 

[icezebra]

Fantastic. I did go searching, but did not come across that link...

Works perfectly. Thanks.