Access Reports and SQL Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Access Reports and SQL Tables

Author

Topic

Auric
Yak Posting Veteran

70 Posts

Posted - 2004-11-10 : 00:35:54

Hello

Has anybody had any trouble in regards using large text fields from an sql table in an access report? my data keeps getting cut off despite having the 'can grow' property set to true... Im sort of desperate here...

Josh

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 10:17:19

are you using a linked table to SQL Server?

When you say large text fields, do you mean a TEXT datatype in SQL Server, or a varchar() datatypes of a large length (i.e., greater than 255) ?

How is Access defining the linked table to SQL for that particular column? i.e., does it consider the column an Access "memo" datatype or a "text" datatype (which has a max size of 255 in Access if I recall?)

After that information, let me know what the SQL statement your report is based off of -- specifically if any manipulation is being done in JET SQL to that particular column. Even if it is successfully being linked as a memo datatype, if you are using that column in a formula and basing your textbox in your report off of that formula, Access may be truncating the result to 255 characters.

Hook me up with as much info as you can and I'll help you out.

- Jeff

Auric
Yak Posting Veteran

70 Posts

Posted - 2004-11-10 : 12:12:49

Here is the SQL Statement

SELECT Incidents.*
FROM Incidents
WHERE (((Incidents.IncidentNo)=[Forms]![Incidents]![IncidentNo]));

If I look at the 'design view' of the linked table in Access it is defining it as a memo field.

I think it is truncating the text somewhere, but it is wierd, because if i open the same record in the Incidents form all the data is there. It is only in the print where it is getting truncated.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 12:33:17

do you have "can grow" set to True for the report's sections as well? i.e., if the textbox is in the DETAIL section, is that section's CanGrow property TRUE as well? that would need to be set to allow for the section to grow to accomodate the text box.

if that isn't it, try drawing a HUGE textbox on the page and seeing if everything is in there. that way you can determine just what is causing the truncation -- the data or the report's text box.

- Jeff

Auric
Yak Posting Veteran

70 Posts

Posted - 2004-11-10 : 13:47:59

Yes

Can Grow is set to true.. I tried making a HUGE text box as well and it still truncates at the same point.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 13:50:58

what version of Access are you using? do you have all the service packs and everything installed?

- Jeff

Auric
Yak Posting Veteran

70 Posts

Posted - 2004-11-10 : 13:56:16

We are using Access 2000

I believe all the updates are installed, but that I would have to verify later.

It is a very strange problem because the data is all there? like if i open the incident report in a form everything is there. That is what is confusing the hell out of me.

Truth be told, my Access skills are good, but i know very little about SQL, and it has been nothing but a headache since i started this little job. Kind of doing it as a favour for a friend, but i would really like to get this last piece of the puzzle working.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-11-10 : 14:11:53

I've got a feeling that this is to do with the text box that you are using to display the text. I seem to recall that I may have come across this many moons ago and I think I had to get an updated version of the text box from somewhere, think is was one of the updated MS ones.

What version of MDAC do you have does that make a difference? I think they're up to about 2.8 or so now

steve

To alcohol ! The cause of - and solution to - all of life's problems

Auric
Yak Posting Veteran

70 Posts

Posted - 2004-11-10 : 16:13:42

I have the latest MDAC

Auric
Yak Posting Veteran

70 Posts

Posted - 2004-11-11 : 11:09:11

Any other ideas guys? :D

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-11 : 11:43:23

Question: how big is the text in this column? what is the maximum length?

you can run something like this to check:

select max(datalength(YourTextColumn))
from YourTable

to find out ...

I couldn't recreate this behavior, but I used a size of < 8000 characters which might be why. are you sure the text isn't just wrapping to the next page ?? That's what was happening to me at first.

- Jeff

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-11-11 : 11:46:41

Remove the formatting properties from the text field (control) on the report
The reason you can see all the data in the form is because it isnt formatted

[url]http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=369079&Search=true&Forum=acc&Words=memo&Match=And&Searchpage=8&Limit=20&Old=allposts&Main=369079[/url]

[url]http://support.microsoft.com/?kbid=259893[/url]

Andy

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-11 : 11:48:45

now i just tried it with 24,000 characters -- worked fine. it wrapped the single text box to 3 pages and displayed everything.

Try recreating a brand new MDB file from scratch, relinking the table, and writing a dummy report and see if it works. your MDB might be corrupted somehow ...

- Jeff

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-11-11 : 11:53:29

Jeff if you want to recreate the behaviour

Create a table in access with 1 memo field & paste this in - i have highlighted in bold the approx 255th character
Asredtgiorwygtrewreogjjjjjjjjjjjjjjjjjjjjjretopareoujtreoov9qytevbu7retq9w7e0vq7ert7ter907vret9reyhgeahrghreyhgreyhgiorehgahreogheaiohghgaklredhgareyhgahregaeiohghhahrearyhgareytgre90yurtyuyuutrytryutryuyuy90utryutr0wuutryutrytryudarfgareggttttttt255255255255retyyyyyyyeaqtrvjutriwuresopredjgiovwreiytureEND

Then create a report or a form with a text field/control linked to this memo field
Notice that all the data is displayed go into design view and add > to the format of the text field. Should now display only 255 characters in uppercase

Well today hasnt been such a waste of time as ive certainly learnt something today!

Andy

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-11 : 12:06:54

ah -- I didn't consider that perhaps formatting was being applied. that makes sense.

hopefully this will solve the problem.

- Jeff

Auric
Yak Posting Veteran

70 Posts

Posted - 2004-11-11 : 13:09:48

Wow :)

Thanks guys... It all works now!!!!! YAY Client will be happy and so will I

Josh

EDIT: Installed updated client on site this afternoon, client is happy because the db is critical for day to day operations of the office. Thanks for all your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources