473,324 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Formatting within Access report text controls - OR - sorting and grouping in Word

tblCourses one to many to tblEvents.

A course may have an intro workshop (a type of event), a mid course
workshop, a final exam. Or any combination. Or something different in the
future.

At the moment the printed output is usually going to Word. It's turning into
an unholy mess, because I'm having to prepare umpteen different Word
templates, and the queries that drive them, depending on what events a
course has.

So I'd rather use Access reports. Group on CourseID, detail is events
attached to that Course, Bob's your uncle.

The clients at the moment have a small amount of formatting within
paragraphs. I can't find a way of doing that in Access Report text controls.
e.g. 'Your student number is <bold>123/456</bold>, make sure you don't
forget it'. Anybody know how to do that in an Access report?

Or alternatively, how to duplicate Access's sorting and grouping in Word?
I've tried all sorts of Crosstab cleverness but it seems silly not to use
the report writer, it being so good an' all.

Cheers, Mike
Nov 13 '05 #1
8 3465
Whether you create the reports in Access or in Word, you still need to pull
the data from somewhere and stick it in the desired place in your customized
report.

You can automate Word from Access, or vice versa, so you can use features in
both apps.
If you are concerned with formatting, then you could generate the report
using Word and keep the data in an Access data base.

--
http://www.standards.com/; See Howard Kaikow's web site.
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:41***********************@news.aaisp.net.uk.. .
tblCourses one to many to tblEvents.

A course may have an intro workshop (a type of event), a mid course
workshop, a final exam. Or any combination. Or something different in the
future.

At the moment the printed output is usually going to Word. It's turning into an unholy mess, because I'm having to prepare umpteen different Word
templates, and the queries that drive them, depending on what events a
course has.

So I'd rather use Access reports. Group on CourseID, detail is events
attached to that Course, Bob's your uncle.

The clients at the moment have a small amount of formatting within
paragraphs. I can't find a way of doing that in Access Report text controls. e.g. 'Your student number is <bold>123/456</bold>, make sure you don't
forget it'. Anybody know how to do that in an Access report?

Or alternatively, how to duplicate Access's sorting and grouping in Word?
I've tried all sorts of Crosstab cleverness but it seems silly not to use
the report writer, it being so good an' all.

Cheers, Mike

Nov 13 '05 #2
Thanks, I'm aware of those things.

Let's see if you have any idea how to do this then.

The data structure is one to many. I assume you know what that means.

So a student will be on a course, which will have 0 to many events. The
letters I want will be something like this:

**************

Dear Student Name

Thanks for enrolling on Access For Dummies. The events for this course are:

20/4/4 - Introduction
21/4/4 - Conclusion

Many thanks

The administrator

***************

So the stuff in the middle of that is variable from course to course, yes?
Now, in a Access report that's easy to do using the sorting and grouping
functions, as you know. What I can't find a way to do it is in Word, if
there is an unknown (at design time, that is) number of events. But you seem
to be an expert on Word, no doubt you'll know.

Many thanks

Mike MacSween
"Howard Kaikow" <ka****@standards.com> wrote in message
news:ci**********@pyrite.mv.net...
Whether you create the reports in Access or in Word, you still need to pull the data from somewhere and stick it in the desired place in your customized report.

You can automate Word from Access, or vice versa, so you can use features in both apps.
If you are concerned with formatting, then you could generate the report
using Word and keep the data in an Access data base.

--
http://www.standards.com/; See Howard Kaikow's web site.
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:41***********************@news.aaisp.net.uk.. .
tblCourses one to many to tblEvents.

A course may have an intro workshop (a type of event), a mid course
workshop, a final exam. Or any combination. Or something different in the future.

At the moment the printed output is usually going to Word. It's turning

into
an unholy mess, because I'm having to prepare umpteen different Word
templates, and the queries that drive them, depending on what events a
course has.

So I'd rather use Access reports. Group on CourseID, detail is events
attached to that Course, Bob's your uncle.

The clients at the moment have a small amount of formatting within
paragraphs. I can't find a way of doing that in Access Report text

controls.
e.g. 'Your student number is <bold>123/456</bold>, make sure you don't
forget it'. Anybody know how to do that in an Access report?

Or alternatively, how to duplicate Access's sorting and grouping in Word? I've tried all sorts of Crosstab cleverness but it seems silly not to use the report writer, it being so good an' all.

Cheers, Mike


Nov 13 '05 #3
Chuck,

I'm curious!!!

<<Remember that you can link, and re-link the queries and tables together,
even to the same table over and over as many times as you need.>>>

What can you achieve by doing this?

Thanks!

Ruth

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:pl********************************@4ax.com...

Don't link to a table, Mike. Link to a query that does the sorting
and grouping. Note that you may have to use a few "levels" of queries
to duplicate the sorting and grouping you need, but that's going to
depend upon exactly what you are doing and your data structure.
Remember that you can link, and re-link the queries and tables
together, even to the same table over and over as many times as you
need.

Of late, I've come to hate Word reports, although they do have their
uses. A lot of what can be done in word as to formatting can be done
with a lot less effort in HTML (although that may be just a subjective
thing). Word can read HTML files easily enough, but so does the
browser on everyone's computer these days. There are a couple of
tricks you can use to ensure that the file opens up in word, if that's
what you have to have.
On Mon, 13 Sep 2004 07:12:46 +0100, "Mike MacSween"
<mi******************@btinternet.com> wrote:
Thanks, I'm aware of those things.
Let's see if you have any idea how to do this then.
The data structure is one to many. I assume you know what that means.
So a student will be on a course, which will have 0 to many events.
Now, in a Access report that's easy to do using the sorting and grouping
functions, as you know. What I can't find a way to do it is in Word, if
there is an unknown (at design time, that is) number of events. But you seem
to be an expert on Word, no doubt you'll know.

"Howard Kaikow" <ka****@standards.com> wrote in message
news:ci**********@pyrite.mv.net...
Whether you create the reports in Access or in Word, you still need to
pull the data from somewhere and stick it in the desired place in your
customized report.
You can automate Word from Access, or vice versa, so you can use features in both apps.
If you are concerned with formatting, then you could generate the report using Word and keep the data in an Access data base.

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:41***********************@news.aaisp.net.uk.. .
> tblCourses one to many to tblEvents.
> A course may have an intro workshop (a type of event), a mid course
> workshop, a final exam. Or any combination. Or something different in
> the future.
> At the moment the printed output is usually going to Word. It's turning > into
> an unholy mess, because I'm having to prepare umpteen different Word
> templates, and the queries that drive them, depending on what events a > course has.
> So I'd rather use Access reports. Group on CourseID, detail is events
> attached to that Course, Bob's your uncle.
> The clients at the moment have a small amount of formatting within
> paragraphs. I can't find a way of doing that in Access Report text
> controls.
> e.g. 'Your student number is <bold>123/456</bold>, make sure you don't > forget it'. Anybody know how to do that in an Access report?
> Or alternatively, how to duplicate Access's sorting and grouping in
> Word?
> I've tried all sorts of Crosstab cleverness but it seems silly not to
> use the report writer, it being so good an' all.

--
A Proliferation Of New Laws Creates A Proliferation Of New Loopholes.

Nov 13 '05 #4
Thanks Chuck

Yes, it is a query that's driving this, with a few other queries in that
too.

The basic problem is that the many side of the one to many has an unknown
number of events.

So the very easy thing that we can do with access reports using sorting and
grouping -

group on student ID

in detail show each event they're on.

can't be done, at least not in a similar way, in Word. Well, I guess I could
set up a Word template with the 'maximum number of event field I think I
might need at this point in time' fields. But it's cludgy.

Cindy Meister has some good links to this. But a google in the Word groups
where many people have posed similar questions gives the basic answer, even
from Word MVPs, that Access is better than Word for this.

Well, it would be, wouldn't it? It's a database program!!

The formatting the clients need isn't that fancy, and I'm sure I can
persuade them away from the precise formatting they would like, at least
when I tell them it's going to cost a lot more. More of a problem is that
they often like to edit the output. Though that's a requirements problem I
think.

I'm sure there are ways round it, concatenating every value from the many
side into one huge text field and using that, for instance, then outputting
the results to Word.

I'll do a demo with an Access report I think, then remind them they can
always RTF to Word if they really want to edit it.

Cheers, Mike

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:pl********************************@4ax.com...

Don't link to a table, Mike. Link to a query that does the sorting
and grouping. Note that you may have to use a few "levels" of queries
to duplicate the sorting and grouping you need, but that's going to
depend upon exactly what you are doing and your data structure.
Remember that you can link, and re-link the queries and tables
together, even to the same table over and over as many times as you
need.

Of late, I've come to hate Word reports, although they do have their
uses. A lot of what can be done in word as to formatting can be done
with a lot less effort in HTML (although that may be just a subjective
thing). Word can read HTML files easily enough, but so does the
browser on everyone's computer these days. There are a couple of
tricks you can use to ensure that the file opens up in word, if that's
what you have to have.
On Mon, 13 Sep 2004 07:12:46 +0100, "Mike MacSween"
<mi******************@btinternet.com> wrote:
Thanks, I'm aware of those things.
Let's see if you have any idea how to do this then.
The data structure is one to many. I assume you know what that means.
So a student will be on a course, which will have 0 to many events.
Now, in a Access report that's easy to do using the sorting and grouping
functions, as you know. What I can't find a way to do it is in Word, if
there is an unknown (at design time, that is) number of events. But you seem
to be an expert on Word, no doubt you'll know.

"Howard Kaikow" <ka****@standards.com> wrote in message
news:ci**********@pyrite.mv.net...
Whether you create the reports in Access or in Word, you still need to
pull the data from somewhere and stick it in the desired place in your
customized report.
You can automate Word from Access, or vice versa, so you can use features in both apps.
If you are concerned with formatting, then you could generate the report using Word and keep the data in an Access data base.

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:41***********************@news.aaisp.net.uk.. .
> tblCourses one to many to tblEvents.
> A course may have an intro workshop (a type of event), a mid course
> workshop, a final exam. Or any combination. Or something different in
> the future.
> At the moment the printed output is usually going to Word. It's turning > into
> an unholy mess, because I'm having to prepare umpteen different Word
> templates, and the queries that drive them, depending on what events a > course has.
> So I'd rather use Access reports. Group on CourseID, detail is events
> attached to that Course, Bob's your uncle.
> The clients at the moment have a small amount of formatting within
> paragraphs. I can't find a way of doing that in Access Report text
> controls.
> e.g. 'Your student number is <bold>123/456</bold>, make sure you don't > forget it'. Anybody know how to do that in an Access report?
> Or alternatively, how to duplicate Access's sorting and grouping in
> Word?
> I've tried all sorts of Crosstab cleverness but it seems silly not to
> use the report writer, it being so good an' all.

--
A Proliferation Of New Laws Creates A Proliferation Of New Loopholes.

Nov 13 '05 #5
"Mike MacSween" <mi******************@btinternet.com> wrote in message news:<41***********************@news.aaisp.net.uk> ...
tblCourses one to many to tblEvents.

A course may have an intro workshop (a type of event), a mid course
workshop, a final exam. Or any combination. Or something different in the
future.

At the moment the printed output is usually going to Word. It's turning into
an unholy mess, because I'm having to prepare umpteen different Word
templates, and the queries that drive them, depending on what events a
course has.

So I'd rather use Access reports. Group on CourseID, detail is events
attached to that Course, Bob's your uncle.

The clients at the moment have a small amount of formatting within
paragraphs. I can't find a way of doing that in Access Report text controls.
e.g. 'Your student number is <bold>123/456</bold>, make sure you don't
forget it'. Anybody know how to do that in an Access report?

Or alternatively, how to duplicate Access's sorting and grouping in Word?
I've tried all sorts of Crosstab cleverness but it seems silly not to use
the report writer, it being so good an' all.

Cheers, Mike


Mike, I think I've done what your talking about. I did this by coding
an On Format event procedure in the Detail section of the report. The
report I produce lists a series of accounts and related info. If the
account end date has passed, I highlight the field in red. Here's the
code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If ACCTEnd < Date Then
ACTPer.BackStyle = 1
ACTPer.BackColor = 11974910 'Sets background color to red if ending
date is less than
'current date
Else
ACTPer.BackStyle = 0
End If

End Sub

I realize this is a list, and it may be more complex in a "paragraph"
type report.

Good luck.

-Steve-
Nov 13 '05 #6
I'll have to experiment a little!

How about multiple instances of a form - what can be achieved?

Thanks.
"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:n8********************************@4ax.com...

Actually, quite a bit!

It's a bit daunting to describe what can be done given such an open
ended question, but for one example, you can have a totals query of
some kind which is linked back to a select query to give you over all
totals for something without doing a grouping on a field that you
don't want grouped. An example of that might be a report which lists
all the bolts in a inventory table, and you need to show all the
different types, but you only need to know the over all count, not the
count for each separate type.

Does that make any sense?

On Mon, 13 Sep 2004 22:40:58 GMT, "Ruth" <rm****@earthlink.net> wrote:
I'm curious!!!
<<Remember that you can link, and re-link the queries and tables together,even to the same table over and over as many times as you need.>>>
What can you achieve by doing this?

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:pl********************************@4ax.com.. .
Don't link to a table, Mike. Link to a query that does the sorting
and grouping. Note that you may have to use a few "levels" of queries
to duplicate the sorting and grouping you need, but that's going to
depend upon exactly what you are doing and your data structure.
Remember that you can link, and re-link the queries and tables
together, even to the same table over and over as many times as you
need.

--
A Programmers Is A Bug's Way Of Making More Bugs...

Nov 13 '05 #7
Thanks Steve

Yes, you're right. Separate controls are easy to format, but within a
control it seems to be impossible.

Mike

"Steve" <hb***@uiuc.edu> wrote in message
news:bb**************************@posting.google.c om...
"Mike MacSween" <mi******************@btinternet.com> wrote in message

news:<41***********************@news.aaisp.net.uk> ...
tblCourses one to many to tblEvents.

A course may have an intro workshop (a type of event), a mid course
workshop, a final exam. Or any combination. Or something different in the future.

At the moment the printed output is usually going to Word. It's turning into an unholy mess, because I'm having to prepare umpteen different Word
templates, and the queries that drive them, depending on what events a
course has.

So I'd rather use Access reports. Group on CourseID, detail is events
attached to that Course, Bob's your uncle.

The clients at the moment have a small amount of formatting within
paragraphs. I can't find a way of doing that in Access Report text controls. e.g. 'Your student number is <bold>123/456</bold>, make sure you don't
forget it'. Anybody know how to do that in an Access report?

Or alternatively, how to duplicate Access's sorting and grouping in Word? I've tried all sorts of Crosstab cleverness but it seems silly not to use the report writer, it being so good an' all.

Cheers, Mike


Mike, I think I've done what your talking about. I did this by coding
an On Format event procedure in the Detail section of the report. The
report I produce lists a series of accounts and related info. If the
account end date has passed, I highlight the field in red. Here's the
code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If ACCTEnd < Date Then
ACTPer.BackStyle = 1
ACTPer.BackColor = 11974910 'Sets background color to red if ending
date is less than
'current date
Else
ACTPer.BackStyle = 0
End If

End Sub

I realize this is a list, and it may be more complex in a "paragraph"
type report.

Good luck.

-Steve-

Nov 13 '05 #8
Thanks Chuck

If they insist on this then probably some sort of office automation is
probably the only way. Constructing the Word document piece by piece at
runtime.

At the moment I run an Access query, which actually sends the data to an rtf
and that is used as the source for a Word document based on one of about 50
Word templates.

It sounds more complicated that it is. I actually got it from The Access
Cookbook. It's nice because whenever I call it I can just specify from
Access - use qryX and templateY.dot and it runs. And because at run time the
data are actually held outside of Access it doesn't slow Access atall.

It's just going to be a PITA not to be able to use the
built-in-and-perfect-for-the-job tool that Access provides.

Cheers, Mike

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:r5********************************@4ax.com...

Ah! I think I'm getting it now! Sorry for the confusion.

You're correct in thinking that you're not going to be able to use the
Merge function in Access to get that information to Word in a
different format, but you can do it if you use Office Automation (VBA)
to do the task.

Personally, I'm far more familiar with automating Word from Access
rather then the other way around (pushing to Word, rather then pulling
from Word), so I won't be much help there.

Using VBA from Access, it's just a matter of using a variable to
"monitor" a trigger field (in your case, the "student ID" field) to
figure out when to start a new form as your code loops through the
records pushing the data out to word.

On Tue, 14 Sep 2004 12:18:36 +0100, "Mike MacSween"
<mi******************@btinternet.com> wrote:
Yes, it is a query that's driving this, with a few other queries in that
too.
The basic problem is that the many side of the one to many has an unknown
number of events.
So the very easy thing that we can do with access reports using sorting and
grouping -
group on student ID
in detail show each event they're on.
can't be done, at least not in a similar way, in Word. Well, I guess I couldset up a Word template with the 'maximum number of event field I think I
might need at this point in time' fields. But it's cludgy.
Cindy Meister has some good links to this. But a google in the Word groupswhere many people have posed similar questions gives the basic answer, evenfrom Word MVPs, that Access is better than Word for this.
Well, it would be, wouldn't it? It's a database program!!
The formatting the clients need isn't that fancy, and I'm sure I can
persuade them away from the precise formatting they would like, at least
when I tell them it's going to cost a lot more. More of a problem is that
they often like to edit the output. Though that's a requirements problem Ithink.
I'm sure there are ways round it, concatenating every value from the many
side into one huge text field and using that, for instance, then outputtingthe results to Word.
I'll do a demo with an Access report I think, then remind them they can
always RTF to Word if they really want to edit it.

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:pl********************************@4ax.com.. .
Don't link to a table, Mike. Link to a query that does the sorting
and grouping. Note that you may have to use a few "levels" of queries
to duplicate the sorting and grouping you need, but that's going to
depend upon exactly what you are doing and your data structure.
Remember that you can link, and re-link the queries and tables
together, even to the same table over and over as many times as you
need.
Of late, I've come to hate Word reports, although they do have their
uses. A lot of what can be done in word as to formatting can be done
with a lot less effort in HTML (although that may be just a subjective
thing). Word can read HTML files easily enough, but so does the
browser on everyone's computer these days. There are a couple of
tricks you can use to ensure that the file opens up in word, if that's
what you have to have.

On Mon, 13 Sep 2004 07:12:46 +0100, "Mike MacSween"
<mi******************@btinternet.com> wrote:
>Thanks, I'm aware of those things.
>Let's see if you have any idea how to do this then.
>The data structure is one to many. I assume you know what that means.
>So a student will be on a course, which will have 0 to many events.
>Now, in a Access report that's easy to do using the sorting and grouping >functions, as you know. What I can't find a way to do it is in Word, if >there is an unknown (at design time, that is) number of events. But you >seem to be an expert on Word, no doubt you'll know."Howard Kaikow" <ka****@standards.com> wrote in message
>news:ci**********@pyrite.mv.net...
>> Whether you create the reports in Access or in Word, you still need to >> pull the data from somewhere and stick it in the desired place in your >> customized report.
>> You can automate Word from Access, or vice versa, so you can use
>> features in both apps.
>> If you are concerned with formatting, then you could generate the
>> report using Word and keep the data in an Access data base.> "Mike MacSween" <mi******************@btinternet.com> wrote in message >> news:41***********************@news.aaisp.net.uk.. .
>> > tblCourses one to many to tblEvents.
>> > A course may have an intro workshop (a type of event), a mid course >> > workshop, a final exam. Or any combination. Or something different in >> > the future.
>> > At the moment the printed output is usually going to Word. It's
>> > turning into
>> > an unholy mess, because I'm having to prepare umpteen different Word >> > templates, and the queries that drive them, depending on what events >> > a course has.
>> > So I'd rather use Access reports. Group on CourseID, detail is events >> > attached to that Course, Bob's your uncle.
>> > The clients at the moment have a small amount of formatting within
>> > paragraphs. I can't find a way of doing that in Access Report text
>> > controls.
>> > e.g. 'Your student number is <bold>123/456</bold>, make sure you
>> > don't forget it'. Anybody know how to do that in an Access report?
>> > Or alternatively, how to duplicate Access's sorting and grouping in >> > Word?
>> > I've tried all sorts of Crosstab cleverness but it seems silly not to >> > use the report writer, it being so good an' all.

--
A Programmers Is A Bug's Way Of Making More Bugs...

Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Tom Weddell | last post by:
Can I call an Access report from VB.Net? (I'm using access as the backend.) Thanks in advance.
0
by: P K | last post by:
Hi, I have an MS Access report which consists of Multiple subreports, when i export the report to MS RTF format in few cases it leaves a Big blank space on the sheet which is like 6 inches...
3
by: (Pete Cresswell) | last post by:
I'm doing a booklet for a class reunion. Got all the data and everybody's life history in a little MS Access app and now it's time to get the booklet ready to go to the printer's. I have a...
1
by: Mike | last post by:
I have a report that is called from a form. I can noarrow the report to the correct data, however I receive two sets of detail sections. I beleive this because I have a payment table that records...
8
by: nn0410 | last post by:
I have a report whose record source is a query. The query includes an ORDER BY clause that sorts on a particular set of columns. I would like to be able to run the same report with the same input...
3
by: Jimmy | last post by:
Is there a way to sort/group a report based on the second column of a combo box, i.e. the text associated with the primary key number?
2
by: Dee Brooks | last post by:
Hello, I'm using Access 2002 to create a text box that will contain the following information in this format: Name Title Company City, State email
0
by: Rich | last post by:
I am trying to find out how I can produce a report in Access 2000, which takes a web link and displays the web page in the report. The report will display Streetmap and the information displayed...
3
by: blees10 | last post by:
Hi Everyone, I am working on an Access report and the client is asking for nitpicky things so I am working on a way to make this work. I have a couple questions and they are not related. 1. I...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.