Query result causing excel to crash?? 3

[gregmosu]

I posted a while back about my sql query producing empy results. I ended up going in and changing all the TEXT field to VARCHAR field, and it was working, until I came up
on this error today...

runtime 1004 error
Application-defined or object-defined error

This was the value from teh database that it crashed on: 4/1 Tony was accepted by client and will start on 4/25/05.

I thought that the numbers in the message where throwing it off, so in the query before, I appended text to the begining and end of the string.. but still didnt work. The cell's format is general. Any ideas?

Thanks,
Greg

SELECT Distinct((c.candidateName + ' ')) as exp1, instat.status as exp2, instat.comment as exp3, s.state as exp4, ('NOTE: ' + n.note + ' END') as exp5, (' ' + avail.description + ' ') as exp6, (tl.description + ' ') as exp7, c.id as exp8 FROM  Candidate c LEFT OUTER JOIN InterviewStatus instat ON c.id = instat.candidateId LEFT OUTER JOIN States s ON s.id = c.stateId LEFT OUTER JOIN Note n ON n.candId = c.id LEFT OUTER JOIN Availability avail ON avail.id = c.availabilityId LEFT OUTER JOIN Title tl ON tl.id = c.titleId LEFT OUTER JOIN CandidateSkill cs ON cs.candidateId = c.id LEFT OUTER JOIN CandidateRecruiter cr ON cr.candidateId = c.id

 

[rarogersonkf]

More a comment then a solution...
I was advised to use then & (ampersand) character when concatenating string as this is more of a standard.

Looking at your SQL I see joins on tables CandidateSkill and CandidateRecruiter which have no used fields. These may be eliminated if no fields are to be used.

Rob

 

[rarogersonkf]

I just noticed there is no WHERE clause. Was this omitted on purpose? Could this be causing too many records to be selected or q query timeout?

Rob

 

[gregmosu]

Hi Rob,

I acutally do include a Where clause. It is built depending on what the user selects from the userform. Unfortunately, I cant really eliminate any tables from the join. This runs perfectly in the query analyzer window in Ent. Manager, and runs through a number of records before bombing out. Upon printing out the field in a Message Box, the string is a lot longer than I thought it was.. and I was able to fit the whole thing in the message box. How big can a text field be before it wont fit in a cell?

Thanks,
Greg

 

[rarogersonkf]

Hi Greg, 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar. Is this for the Notes field (exp5)?
You mention that it crashes on the record containing "4/1 Tony was accepted by client and will start on 4/25/05". Is this the notes (exp5) field?

Rob

 

[xlbo]

Memo / Note type fields are not really liked by excel

Sounds like there may be a lot of white space on the record - can you not TRIM ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[gregmosu]

Hi guys, thanks for the replies. I put a LEN() around that field, and it finally produced all 129 records.. and the lenght of the note field causing it to crash is 1274. Also, I can get the field to print out on a Message Box, it only crashes when I write the data to the cell. The cell's format is General.

 

[PHV]

Cell value shouldn't exceed 1024 char I think.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rarogersonkf]

PHV, if you're sugguesting a maximum of 1024 characters per cell if you merged two cells from 2 consequetive rows (merge C3, C4) would the new max be 2048? Would this provide a solution?

Rob

 

[gregmosu]

just ran a test, and it took 1117 characters. are there any characters that would cause excel to crash?

 

[gregmosu]

According to an online book, a cell can contain 32,000 characters. A single column cant be more than 255 char wide, so you must use word wrap to get around that.

The size of the string that's crashing excel is 1258 characters. If I reduce the size, it works just fine. Also, I can paste the string into the cell thru the formula bar, and that works on too... but doing it thru code makes it crash:

        If Not IsNull(rs("exp5")) Then
        'MsgBox "CELL J: " & rs("exp5")
            'Range("J2:M2").Select
            Range("J" & cellCount & ":Z" & cellCount).Select
            Selection.NumberFormat = "General"
            With Selection
                .HorizontalAlignment = xlGeneral
                .VerticalAlignment = xlBottom
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = True
            End With
            ActiveCell.FormulaR1C1 = rs("exp5")
        End If

Tried spanning it across multiple cells, and that didtn work either. Is there any way around this?

 

[rarogersonkf]

Would .WrapText = True help?

Rob

 

[gregmosu]

Nope, bombed out on the same record.. same error message.