Calendar Report

Thank Duane. This works in placing the formating in the query, but I can't
seem to get it to work to format in a report based upon that query. I have
tried putting this expression and several like it in the Format properties,
but without any effect.

Any other ideas? I am always game to learn and try something.

"Duane Hookom" wrote:

You can try:
Expr1: First([Description] & Chr(13) & Chr(10) & Format([Sales - Y1],"$0") &
Chr(13) &
Chr(10) & Left([Project Name],20))


--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in message
...
This is great and it works for me also. Just one more question -- can you
format the values within the crosstab? For my crosstab query, I use the
expression

Expr1: First([Description] & Chr(13) & Chr(10) & [Sales - Y1] & Chr(13) &
Chr(10) & Left([Project Name],20))

to find the value for each row/column intersection. Now, when I create
the
report, I want to format the [Sales - Y1] fied to currency with no decimal
places.

Any ideas?

thanks in advance
"Duane Hookom" wrote:

You don't have to use count or sum. You can use First or Max or Min with
a
Value expression like:
colhead:[Audit] & Chr(13) & chr(10) & [DealerNo]

--
Duane Hookom
MS Access MVP
--

"hughess7" wrote in message
...
As detailed in my previous message I don't think a crosstab query will
work
and wanted clarification of this and any advice on how I could maybe
achieve
it? A crosstab query does produce the format of names in rows and dates
as
column headings but the trouble is the text to be displayed is not a
'count'
or 'sum' of anything. I literally just want to display the contents of
a
few
fields as per my previous example, showing what activity each person is
doing
and a dealer number if they were on an audit, on a daily basis per week
range.

"Duane Hookom" wrote:

If the calendar reports don't work for you then try look at the
crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP
--


"hughess7" wrote in message
...
Hi, I would like a report which prints out a weekly itinerary plan
for
our
employees. I have looked at Duane Hookom's reports but they don't
quite
achieve what I want and not too sure how to replicate something
similar. I
want a layout like below:

Mon Tues Wed Thurs Frid
3 4 5 6
7
Fred Bloggs Audit Meeting Travel Audit Office
1234 4567
Jane Doe Audit Audit Audit Audit Audit
9876 9876 9876 9876 9876
Bilbo Baggins Holiday Holiday Holiday Holiday Holiday

I am new to crosstab queries and tried to do one of these to diplay
the
info
but couldn't get it to work as the details I want displayed (audit,
dealer
no, etc) are the values of fields rather than a calculation. Can
anyone
advise me how to produce a simple report displaying the data in this
format?
I will add a start and end date filter to a form to display which
week
I
want
printed, I am just not sure how to get a report in this layout?

Thanks in advance for any help.
Sue

Is [Sales - Y1] numeric? In the datasheet view of the report's record
source, is the field right or left aligned?

--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in message
...
Thank Duane. This works in placing the formating in the query, but I
can't
seem to get it to work to format in a report based upon that query. I
have
tried putting this expression and several like it in the Format
properties,
but without any effect.

Any other ideas? I am always game to learn and try something.

"Duane Hookom" wrote:

You can try:
Expr1: First([Description] & Chr(13) & Chr(10) & Format([Sales -
Y1],"$0") &
Chr(13) &
Chr(10) & Left([Project Name],20))


--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
This is great and it works for me also. Just one more question -- can
you
format the values within the crosstab? For my crosstab query, I use
the
expression

Expr1: First([Description] & Chr(13) & Chr(10) & [Sales - Y1] & Chr(13)
&
Chr(10) & Left([Project Name],20))

to find the value for each row/column intersection. Now, when I create
the
report, I want to format the [Sales - Y1] fied to currency with no
decimal
places.

Any ideas?

thanks in advance
"Duane Hookom" wrote:

You don't have to use count or sum. You can use First or Max or Min
with
a
Value expression like:
colhead:[Audit] & Chr(13) & chr(10) & [DealerNo]

--
Duane Hookom
MS Access MVP
--

"hughess7" wrote in message
...
As detailed in my previous message I don't think a crosstab query
will
work
and wanted clarification of this and any advice on how I could maybe
achieve
it? A crosstab query does produce the format of names in rows and
dates
as
column headings but the trouble is the text to be displayed is not a
'count'
or 'sum' of anything. I literally just want to display the contents
of
a
few
fields as per my previous example, showing what activity each person
is
doing
and a dealer number if they were on an audit, on a daily basis per
week
range.

"Duane Hookom" wrote:

If the calendar reports don't work for you then try look at the
crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP
--


"hughess7" wrote in message
...
Hi, I would like a report which prints out a weekly itinerary
plan
for
our
employees. I have looked at Duane Hookom's reports but they don't
quite
achieve what I want and not too sure how to replicate something
similar. I
want a layout like below:

Mon Tues Wed Thurs Frid
3 4 5 6
7
Fred Bloggs Audit Meeting Travel Audit Office
1234 4567
Jane Doe Audit Audit Audit Audit Audit
9876 9876 9876 9876 9876
Bilbo Baggins Holiday Holiday Holiday Holiday Holiday

I am new to crosstab queries and tried to do one of these to
diplay
the
info
but couldn't get it to work as the details I want displayed
(audit,
dealer
no, etc) are the values of fields rather than a calculation. Can
anyone
advise me how to produce a simple report displaying the data in
this
format?
I will add a start and end date filter to a form to display which
week
I
want
printed, I am just not sure how to get a report in this layout?

Thanks in advance for any help.
Sue

I have defined [Sales - Y1] as a number (Long Integer) and it is right
aligned in the original source table. However, in the crosstab query for the
report, it is left aligned just like the other two variables.

"Duane Hookom" wrote:

Is [Sales - Y1] numeric? In the datasheet view of the report's record
source, is the field right or left aligned?

--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in message
...
Thank Duane. This works in placing the formating in the query, but I
can't
seem to get it to work to format in a report based upon that query. I
have
tried putting this expression and several like it in the Format
properties,
but without any effect.

Any other ideas? I am always game to learn and try something.

"Duane Hookom" wrote:

You can try:
Expr1: First([Description] & Chr(13) & Chr(10) & Format([Sales -
Y1],"$0") &
Chr(13) &
Chr(10) & Left([Project Name],20))


--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
This is great and it works for me also. Just one more question -- can
you
format the values within the crosstab? For my crosstab query, I use
the
expression

Expr1: First([Description] & Chr(13) & Chr(10) & [Sales - Y1] & Chr(13)
&
Chr(10) & Left([Project Name],20))

to find the value for each row/column intersection. Now, when I create
the
report, I want to format the [Sales - Y1] fied to currency with no
decimal
places.

Any ideas?

thanks in advance
"Duane Hookom" wrote:

You don't have to use count or sum. You can use First or Max or Min
with
a
Value expression like:
colhead:[Audit] & Chr(13) & chr(10) & [DealerNo]

--
Duane Hookom
MS Access MVP
--

"hughess7" wrote in message
...
As detailed in my previous message I don't think a crosstab query
will
work
and wanted clarification of this and any advice on how I could maybe
achieve
it? A crosstab query does produce the format of names in rows and
dates
as
column headings but the trouble is the text to be displayed is not a
'count'
or 'sum' of anything. I literally just want to display the contents
of
a
few
fields as per my previous example, showing what activity each person
is
doing
and a dealer number if they were on an audit, on a daily basis per
week
range.

"Duane Hookom" wrote:

If the calendar reports don't work for you then try look at the
crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP
--


"hughess7" wrote in message
...
Hi, I would like a report which prints out a weekly itinerary
plan
for
our
employees. I have looked at Duane Hookom's reports but they don't
quite
achieve what I want and not too sure how to replicate something
similar. I
want a layout like below:

Mon Tues Wed Thurs Frid
3 4 5 6
7
Fred Bloggs Audit Meeting Travel Audit Office
1234 4567
Jane Doe Audit Audit Audit Audit Audit
9876 9876 9876 9876 9876
Bilbo Baggins Holiday Holiday Holiday Holiday Holiday

I am new to crosstab queries and tried to do one of these to
diplay
the
info
but couldn't get it to work as the details I want displayed
(audit,
dealer
no, etc) are the values of fields rather than a calculation. Can
anyone
advise me how to produce a simple report displaying the data in
this
format?
I will add a start and end date filter to a form to display which
week
I
want
printed, I am just not sure how to get a report in this layout?

Thanks in advance for any help.
Sue

Duane --

I tried it again and I got it to work this time. I am unsure of why the
initial idea didn't work -- I may have typed (and retyped) it incorrectly
last night.

I rearranged the order of the fields (for layout reasons only) and converted
the [Sales - Y1] to millions and placed characters in front and back.The
expression I used is

xpr1: First([Description] & Chr(13) & Chr(10) & [Project Name] & Chr(13) &
Chr(10) & "$ " & ([Sales - Y1]/1000000) & "MM")

And I get what I want. So, thanks your great.



"Duane Hookom" wrote:

Is [Sales - Y1] numeric? In the datasheet view of the report's record
source, is the field right or left aligned?

--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in message
...
Thank Duane. This works in placing the formating in the query, but I
can't
seem to get it to work to format in a report based upon that query. I
have
tried putting this expression and several like it in the Format
properties,
but without any effect.

Any other ideas? I am always game to learn and try something.

"Duane Hookom" wrote:

You can try:
Expr1: First([Description] & Chr(13) & Chr(10) & Format([Sales -
Y1],"$0") &
Chr(13) &
Chr(10) & Left([Project Name],20))


--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
This is great and it works for me also. Just one more question -- can
you
format the values within the crosstab? For my crosstab query, I use
the
expression

Expr1: First([Description] & Chr(13) & Chr(10) & [Sales - Y1] & Chr(13)
&
Chr(10) & Left([Project Name],20))

to find the value for each row/column intersection. Now, when I create
the
report, I want to format the [Sales - Y1] fied to currency with no
decimal
places.

Any ideas?

thanks in advance
"Duane Hookom" wrote:

You don't have to use count or sum. You can use First or Max or Min
with
a
Value expression like:
colhead:[Audit] & Chr(13) & chr(10) & [DealerNo]

--
Duane Hookom
MS Access MVP
--

"hughess7" wrote in message
...
As detailed in my previous message I don't think a crosstab query
will
work
and wanted clarification of this and any advice on how I could maybe
achieve
it? A crosstab query does produce the format of names in rows and
dates
as
column headings but the trouble is the text to be displayed is not a
'count'
or 'sum' of anything. I literally just want to display the contents
of
a
few
fields as per my previous example, showing what activity each person
is
doing
and a dealer number if they were on an audit, on a daily basis per
week
range.

"Duane Hookom" wrote:

If the calendar reports don't work for you then try look at the
crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP
--


"hughess7" wrote in message
...
Hi, I would like a report which prints out a weekly itinerary
plan
for
our
employees. I have looked at Duane Hookom's reports but they don't
quite
achieve what I want and not too sure how to replicate something
similar. I
want a layout like below:

Mon Tues Wed Thurs Frid
3 4 5 6
7
Fred Bloggs Audit Meeting Travel Audit Office
1234 4567
Jane Doe Audit Audit Audit Audit Audit
9876 9876 9876 9876 9876
Bilbo Baggins Holiday Holiday Holiday Holiday Holiday

I am new to crosstab queries and tried to do one of these to
diplay
the
info
but couldn't get it to work as the details I want displayed
(audit,
dealer
no, etc) are the values of fields rather than a calculation. Can
anyone
advise me how to produce a simple report displaying the data in
this
format?
I will add a start and end date filter to a form to display which
week
I
want
printed, I am just not sure how to get a report in this layout?

Thanks in advance for any help.
Sue

Now I discovered another wrinkle -- some of the crosstabs actually can
contain 4 to 5 items -- not a single item. Obviously using the First or Last
functions will only get one. I am not even sure how to approach pulling
multiple items in a cross tab. How do you even think about this type of
issue?

"Duane Hookom" wrote:

Is [Sales - Y1] numeric? In the datasheet view of the report's record
source, is the field right or left aligned?

--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in message
...
Thank Duane. This works in placing the formating in the query, but I
can't
seem to get it to work to format in a report based upon that query. I
have
tried putting this expression and several like it in the Format
properties,
but without any effect.

Any other ideas? I am always game to learn and try something.

"Duane Hookom" wrote:

You can try:
Expr1: First([Description] & Chr(13) & Chr(10) & Format([Sales -
Y1],"$0") &
Chr(13) &
Chr(10) & Left([Project Name],20))


--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
This is great and it works for me also. Just one more question -- can
you
format the values within the crosstab? For my crosstab query, I use
the
expression

Expr1: First([Description] & Chr(13) & Chr(10) & [Sales - Y1] & Chr(13)
&
Chr(10) & Left([Project Name],20))

to find the value for each row/column intersection. Now, when I create
the
report, I want to format the [Sales - Y1] fied to currency with no
decimal
places.

Any ideas?

thanks in advance
"Duane Hookom" wrote:

You don't have to use count or sum. You can use First or Max or Min
with
a
Value expression like:
colhead:[Audit] & Chr(13) & chr(10) & [DealerNo]

--
Duane Hookom
MS Access MVP
--

"hughess7" wrote in message
...
As detailed in my previous message I don't think a crosstab query
will
work
and wanted clarification of this and any advice on how I could maybe
achieve
it? A crosstab query does produce the format of names in rows and
dates
as
column headings but the trouble is the text to be displayed is not a
'count'
or 'sum' of anything. I literally just want to display the contents
of
a
few
fields as per my previous example, showing what activity each person
is
doing
and a dealer number if they were on an audit, on a daily basis per
week
range.

"Duane Hookom" wrote:

If the calendar reports don't work for you then try look at the
crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP
--


"hughess7" wrote in message
...
Hi, I would like a report which prints out a weekly itinerary
plan
for
our
employees. I have looked at Duane Hookom's reports but they don't
quite
achieve what I want and not too sure how to replicate something
similar. I
want a layout like below:

Mon Tues Wed Thurs Frid
3 4 5 6
7
Fred Bloggs Audit Meeting Travel Audit Office
1234 4567
Jane Doe Audit Audit Audit Audit Audit
9876 9876 9876 9876 9876
Bilbo Baggins Holiday Holiday Holiday Holiday Holiday

I am new to crosstab queries and tried to do one of these to
diplay
the
info
but couldn't get it to work as the details I want displayed
(audit,
dealer
no, etc) are the values of fields rather than a calculation. Can
anyone
advise me how to produce a simple report displaying the data in
this
format?
I will add a start and end date filter to a form to display which
week
I
want
printed, I am just not sure how to get a report in this layout?

Thanks in advance for any help.
Sue

You can find a generic concatenate function that should work at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane.

Did you ever look at my Crosstab or Calendar reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4?

--
Duane Hookom
MS Access MVP
--

"tom at arundel" wrote in message
news
Now I discovered another wrinkle -- some of the crosstabs actually can
contain 4 to 5 items -- not a single item. Obviously using the First or
Last
functions will only get one. I am not even sure how to approach pulling
multiple items in a cross tab. How do you even think about this type of
issue?

"Duane Hookom" wrote:

Is [Sales - Y1] numeric? In the datasheet view of the report's record
source, is the field right or left aligned?

--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
Thank Duane. This works in placing the formating in the query, but I
can't
seem to get it to work to format in a report based upon that query. I
have
tried putting this expression and several like it in the Format
properties,
but without any effect.

Any other ideas? I am always game to learn and try something.

"Duane Hookom" wrote:

You can try:
Expr1: First([Description] & Chr(13) & Chr(10) & Format([Sales -
Y1],"$0") &
Chr(13) &
Chr(10) & Left([Project Name],20))


--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
This is great and it works for me also. Just one more question --
can
you
format the values within the crosstab? For my crosstab query, I use
the
expression

Expr1: First([Description] & Chr(13) & Chr(10) & [Sales - Y1] &
Chr(13)
&
Chr(10) & Left([Project Name],20))

to find the value for each row/column intersection. Now, when I
create
the
report, I want to format the [Sales - Y1] fied to currency with no
decimal
places.

Any ideas?

thanks in advance
"Duane Hookom" wrote:

You don't have to use count or sum. You can use First or Max or Min
with
a
Value expression like:
colhead:[Audit] & Chr(13) & chr(10) & [DealerNo]

--
Duane Hookom
MS Access MVP
--

"hughess7" wrote in message
...
As detailed in my previous message I don't think a crosstab query
will
work
and wanted clarification of this and any advice on how I could
maybe
achieve
it? A crosstab query does produce the format of names in rows and
dates
as
column headings but the trouble is the text to be displayed is
not a
'count'
or 'sum' of anything. I literally just want to display the
contents
of
a
few
fields as per my previous example, showing what activity each
person
is
doing
and a dealer number if they were on an audit, on a daily basis
per
week
range.

"Duane Hookom" wrote:

If the calendar reports don't work for you then try look at the
crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP
--


"hughess7" wrote in message
...
Hi, I would like a report which prints out a weekly itinerary
plan
for
our
employees. I have looked at Duane Hookom's reports but they
don't
quite
achieve what I want and not too sure how to replicate
something
similar. I
want a layout like below:

Mon Tues Wed Thurs Frid
3 4 5 6
7
Fred Bloggs Audit Meeting Travel Audit Office
1234 4567
Jane Doe Audit Audit Audit Audit Audit
9876 9876 9876 9876 9876
Bilbo Baggins Holiday Holiday Holiday Holiday Holiday

I am new to crosstab queries and tried to do one of these to
diplay
the
info
but couldn't get it to work as the details I want displayed
(audit,
dealer
no, etc) are the values of fields rather than a calculation.
Can
anyone
advise me how to produce a simple report displaying the data
in
this
format?
I will add a start and end date filter to a form to display
which
week
I
want
printed, I am just not sure how to get a report in this
layout?

Thanks in advance for any help.
Sue

I have looked at your crosstab and calendar reports and now at the
concatenate function. They help -- but then I need lots of help. Thanks and
I will try the concetanate functions.

"Duane Hookom" wrote:

You can find a generic concatenate function that should work at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane.

Did you ever look at my Crosstab or Calendar reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4?

--
Duane Hookom
MS Access MVP
--

"tom at arundel" wrote in message
news
Now I discovered another wrinkle -- some of the crosstabs actually can
contain 4 to 5 items -- not a single item. Obviously using the First or
Last
functions will only get one. I am not even sure how to approach pulling
multiple items in a cross tab. How do you even think about this type of
issue?

"Duane Hookom" wrote:

Is [Sales - Y1] numeric? In the datasheet view of the report's record
source, is the field right or left aligned?

--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
Thank Duane. This works in placing the formating in the query, but I
can't
seem to get it to work to format in a report based upon that query. I
have
tried putting this expression and several like it in the Format
properties,
but without any effect.

Any other ideas? I am always game to learn and try something.

"Duane Hookom" wrote:

You can try:
Expr1: First([Description] & Chr(13) & Chr(10) & Format([Sales -
Y1],"$0") &
Chr(13) &
Chr(10) & Left([Project Name],20))


--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
This is great and it works for me also. Just one more question --
can
you
format the values within the crosstab? For my crosstab query, I use
the
expression

Expr1: First([Description] & Chr(13) & Chr(10) & [Sales - Y1] &
Chr(13)
&
Chr(10) & Left([Project Name],20))

to find the value for each row/column intersection. Now, when I
create
the
report, I want to format the [Sales - Y1] fied to currency with no
decimal
places.

Any ideas?

thanks in advance
"Duane Hookom" wrote:

You don't have to use count or sum. You can use First or Max or Min
with
a
Value expression like:
colhead:[Audit] & Chr(13) & chr(10) & [DealerNo]

--
Duane Hookom
MS Access MVP
--

"hughess7" wrote in message
...
As detailed in my previous message I don't think a crosstab query
will
work
and wanted clarification of this and any advice on how I could
maybe
achieve
it? A crosstab query does produce the format of names in rows and
dates
as
column headings but the trouble is the text to be displayed is
not a
'count'
or 'sum' of anything. I literally just want to display the
contents
of
a
few
fields as per my previous example, showing what activity each
person
is
doing
and a dealer number if they were on an audit, on a daily basis
per
week
range.

"Duane Hookom" wrote:

If the calendar reports don't work for you then try look at the
crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP
--


"hughess7" wrote in message
...
Hi, I would like a report which prints out a weekly itinerary
plan
for
our
employees. I have looked at Duane Hookom's reports but they
don't
quite
achieve what I want and not too sure how to replicate
something
similar. I
want a layout like below:

Mon Tues Wed Thurs Frid
3 4 5 6
7
Fred Bloggs Audit Meeting Travel Audit Office
1234 4567
Jane Doe Audit Audit Audit Audit Audit
9876 9876 9876 9876 9876
Bilbo Baggins Holiday Holiday Holiday Holiday Holiday

I am new to crosstab queries and tried to do one of these to
diplay
the
info
but couldn't get it to work as the details I want displayed
(audit,
dealer
no, etc) are the values of fields rather than a calculation.
Can
anyone
advise me how to produce a simple report displaying the data
in
this
format?
I will add a start and end date filter to a form to display
which
week
I
want
printed, I am just not sure how to get a report in this
layout?

Thanks in advance for any help.
Sue

Duane --

Well, I am still trying to make this work to create the calendar report.
After reading your Crosstab and Calendar reports, I went back and stuck my
nose in more books to learn some more. I still come up with an error I can't
figure out.

In my crosstab query, I have the following entry for the field of the value.

Entry: Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=" & [ActionTeam])

The fields for the rows are

Year: Format([Date:NextGate],"yyyy")

and

ActionTeam


The column heading is

Format([Date:NextGate],"mmm")

Each time I run it, I get a run-time error with this message: Syntax error
(missing operator) in query expression 'ActionTeam='.

The expression:

Entry: Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=" & 3)

Runs just fine. 3 is a value for ActionTeam. (ActionTeam is declared as a
long integer.)

So, I feel like this is probably an "Oh Dah" problem, but I can't seem to
spot anything.

Any ideas?


"Duane Hookom" wrote:

You can find a generic concatenate function that should work at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane.

Did you ever look at my Crosstab or Calendar reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4?

--
Duane Hookom
MS Access MVP
--

"tom at arundel" wrote in message
news
Now I discovered another wrinkle -- some of the crosstabs actually can
contain 4 to 5 items -- not a single item. Obviously using the First or
Last
functions will only get one. I am not even sure how to approach pulling
multiple items in a cross tab. How do you even think about this type of
issue?

"Duane Hookom" wrote:

Is [Sales - Y1] numeric? In the datasheet view of the report's record
source, is the field right or left aligned?

--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
Thank Duane. This works in placing the formating in the query, but I
can't
seem to get it to work to format in a report based upon that query. I
have
tried putting this expression and several like it in the Format
properties,
but without any effect.

Any other ideas? I am always game to learn and try something.

"Duane Hookom" wrote:

You can try:
Expr1: First([Description] & Chr(13) & Chr(10) & Format([Sales -
Y1],"$0") &
Chr(13) &
Chr(10) & Left([Project Name],20))


--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
This is great and it works for me also. Just one more question --
can
you
format the values within the crosstab? For my crosstab query, I use
the
expression

Expr1: First([Description] & Chr(13) & Chr(10) & [Sales - Y1] &
Chr(13)
&
Chr(10) & Left([Project Name],20))

to find the value for each row/column intersection. Now, when I
create
the
report, I want to format the [Sales - Y1] fied to currency with no
decimal
places.

Any ideas?

thanks in advance
"Duane Hookom" wrote:

You don't have to use count or sum. You can use First or Max or Min
with
a
Value expression like:
colhead:[Audit] & Chr(13) & chr(10) & [DealerNo]

--
Duane Hookom
MS Access MVP
--

"hughess7" wrote in message
...
As detailed in my previous message I don't think a crosstab query
will
work
and wanted clarification of this and any advice on how I could
maybe
achieve
it? A crosstab query does produce the format of names in rows and
dates
as
column headings but the trouble is the text to be displayed is
not a
'count'
or 'sum' of anything. I literally just want to display the
contents
of
a
few
fields as per my previous example, showing what activity each
person
is
doing
and a dealer number if they were on an audit, on a daily basis
per
week
range.

"Duane Hookom" wrote:

If the calendar reports don't work for you then try look at the
crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP
--


"hughess7" wrote in message
...
Hi, I would like a report which prints out a weekly itinerary
plan
for
our
employees. I have looked at Duane Hookom's reports but they
don't
quite
achieve what I want and not too sure how to replicate
something
similar. I
want a layout like below:

Mon Tues Wed Thurs Frid
3 4 5 6
7
Fred Bloggs Audit Meeting Travel Audit Office
1234 4567
Jane Doe Audit Audit Audit Audit Audit
9876 9876 9876 9876 9876
Bilbo Baggins Holiday Holiday Holiday Holiday Holiday

I am new to crosstab queries and tried to do one of these to
diplay
the
info
but couldn't get it to work as the details I want displayed
(audit,
dealer
no, etc) are the values of fields rather than a calculation.
Can
anyone
advise me how to produce a simple report displaying the data
in
this
format?
I will add a start and end date filter to a form to display
which
week
I
want
printed, I am just not sure how to get a report in this
layout?

Thanks in advance for any help.
Sue

Is ActionTeam a numeric field in both your crosstab query and in
qryGateReviewSchedule?

What do you get if you open the debug window (press ctrl+g) and enter

? Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=3")

What is your complete SQL view of the crosstab query?
--
Duane Hookom
MS Access MVP
--

"tom at arundel" wrote in message
...
Duane --

Well, I am still trying to make this work to create the calendar report.
After reading your Crosstab and Calendar reports, I went back and stuck my
nose in more books to learn some more. I still come up with an error I
can't
figure out.

In my crosstab query, I have the following entry for the field of the
value.

Entry: Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=" & [ActionTeam])

The fields for the rows are

Year: Format([Date:NextGate],"yyyy")

and

ActionTeam


The column heading is

Format([Date:NextGate],"mmm")

Each time I run it, I get a run-time error with this message: Syntax error
(missing operator) in query expression 'ActionTeam='.

The expression:

Entry: Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=" & 3)

Runs just fine. 3 is a value for ActionTeam. (ActionTeam is declared as
a
long integer.)

So, I feel like this is probably an "Oh Dah" problem, but I can't seem to
spot anything.

Any ideas?


"Duane Hookom" wrote:

You can find a generic concatenate function that should work at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane.

Did you ever look at my Crosstab or Calendar reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4?

--
Duane Hookom
MS Access MVP
--

"tom at arundel" wrote in
message
news
Now I discovered another wrinkle -- some of the crosstabs actually can
contain 4 to 5 items -- not a single item. Obviously using the First
or
Last
functions will only get one. I am not even sure how to approach
pulling
multiple items in a cross tab. How do you even think about this type
of
issue?

"Duane Hookom" wrote:

Is [Sales - Y1] numeric? In the datasheet view of the report's record
source, is the field right or left aligned?

--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
Thank Duane. This works in placing the formating in the query, but
I
can't
seem to get it to work to format in a report based upon that query.
I
have
tried putting this expression and several like it in the Format
properties,
but without any effect.

Any other ideas? I am always game to learn and try something.

"Duane Hookom" wrote:

You can try:
Expr1: First([Description] & Chr(13) & Chr(10) & Format([Sales -
Y1],"$0") &
Chr(13) &
Chr(10) & Left([Project Name],20))


--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
This is great and it works for me also. Just one more
question --
can
you
format the values within the crosstab? For my crosstab query, I
use
the
expression

Expr1: First([Description] & Chr(13) & Chr(10) & [Sales - Y1] &
Chr(13)
&
Chr(10) & Left([Project Name],20))

to find the value for each row/column intersection. Now, when I
create
the
report, I want to format the [Sales - Y1] fied to currency with
no
decimal
places.

Any ideas?

thanks in advance
"Duane Hookom" wrote:

You don't have to use count or sum. You can use First or Max or
Min
with
a
Value expression like:
colhead:[Audit] & Chr(13) & chr(10) & [DealerNo]

--
Duane Hookom
MS Access MVP
--

"hughess7" wrote in message
...
As detailed in my previous message I don't think a crosstab
query
will
work
and wanted clarification of this and any advice on how I could
maybe
achieve
it? A crosstab query does produce the format of names in rows
and
dates
as
column headings but the trouble is the text to be displayed is
not a
'count'
or 'sum' of anything. I literally just want to display the
contents
of
a
few
fields as per my previous example, showing what activity each
person
is
doing
and a dealer number if they were on an audit, on a daily basis
per
week
range.

"Duane Hookom" wrote:

If the calendar reports don't work for you then try look at
the
crosstab
reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP
--


"hughess7" wrote in
message
...
Hi, I would like a report which prints out a weekly
itinerary
plan
for
our
employees. I have looked at Duane Hookom's reports but they
don't
quite
achieve what I want and not too sure how to replicate
something
similar. I
want a layout like below:

Mon Tues Wed Thurs Frid
3 4 5
6
7
Fred Bloggs Audit Meeting Travel Audit Office
1234 4567
Jane Doe Audit Audit Audit Audit Audit
9876 9876 9876 9876 9876
Bilbo Baggins Holiday Holiday Holiday Holiday
Holiday

I am new to crosstab queries and tried to do one of these
to
diplay
the
info
but couldn't get it to work as the details I want displayed
(audit,
dealer
no, etc) are the values of fields rather than a
calculation.
Can
anyone
advise me how to produce a simple report displaying the
data
in
this
format?
I will add a start and end date filter to a form to display
which
week
I
want
printed, I am just not sure how to get a report in this
layout?

Thanks in advance for any help.
Sue

Is ActionTeam a numeric field in both your crosstab query and in
qryGateReviewSchedule?

-- I think it is -- but I am not sure how to check. I only dimension it
once in the original table then don't change it anywhere else that I am aware
of.

What do you get if you open the debug window (press ctrl+g) and enter

? Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=3")

I get just what I expect, a list from ProjectName for ActionTeam 3 separated
by commas -- Bulk Belts, Project 25, test project 5a

What is your complete SQL view of the crosstab query?

TRANSFORM Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=3") AS Entry
SELECT Format([Date:NextGate],"yyyy") AS [Year],
qryGateReviewSchedule.ActionTeam
FROM qryGateReviewSchedule
GROUP BY Format([Date:NextGate],"yyyy"), qryGateReviewSchedule.ActionTeam
PIVOT Format([Date:NextGate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

and, just in case it helps, the SQL view of qryGateReviewSchedule

SELECT tblActionTeams.ActionTeamName, tblProjects.ProjectName,
tblSalesProjections.Y1Sales, tblProjects.NextGate,
tblProjects.[Date:NextGate], tblGates.Description, tblProjects.ProjectType,
tblProjects.ActionTeam
FROM (tblGates RIGHT JOIN (tblActionTeams RIGHT JOIN tblProjects ON
tblActionTeams.ActionTeamID = tblProjects.ActionTeam) ON tblGates.GatesID =
tblProjects.NextGate) INNER JOIN tblSalesProjections ON
tblProjects.ProjectNumber = tblSalesProjections.SalesID
WHERE (((tblProjects.ProjectType)=2 Or (tblProjects.ProjectType)=3 Or
(tblProjects.ProjectType)=4 Or (tblProjects.ProjectType)=7));


Duane -- thanks a lot.

"Duane Hookom" wrote:

Is ActionTeam a numeric field in both your crosstab query and in
qryGateReviewSchedule?

What do you get if you open the debug window (press ctrl+g) and enter

? Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=3")

What is your complete SQL view of the crosstab query?
--
Duane Hookom
MS Access MVP
--

"tom at arundel" wrote in message
...
Duane --

Well, I am still trying to make this work to create the calendar report.
After reading your Crosstab and Calendar reports, I went back and stuck my
nose in more books to learn some more. I still come up with an error I
can't
figure out.

In my crosstab query, I have the following entry for the field of the
value.

Entry: Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=" & [ActionTeam])

The fields for the rows are

Year: Format([Date:NextGate],"yyyy")

and

ActionTeam


The column heading is

Format([Date:NextGate],"mmm")

Each time I run it, I get a run-time error with this message: Syntax error
(missing operator) in query expression 'ActionTeam='.

The expression:

Entry: Concatenate("SELECT ProjectName FROM qryGateReviewSchedule WHERE
ActionTeam=" & 3)

Runs just fine. 3 is a value for ActionTeam. (ActionTeam is declared as
a
long integer.)

So, I feel like this is probably an "Oh Dah" problem, but I can't seem to
spot anything.

Any ideas?


"Duane Hookom" wrote:

You can find a generic concatenate function that should work at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane.

Did you ever look at my Crosstab or Calendar reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4?

--
Duane Hookom
MS Access MVP
--

"tom at arundel" wrote in
message
news Now I discovered another wrinkle -- some of the crosstabs actually can
contain 4 to 5 items -- not a single item. Obviously using the First
or
Last
functions will only get one. I am not even sure how to approach
pulling
multiple items in a cross tab. How do you even think about this type
of
issue?

"Duane Hookom" wrote:

Is [Sales - Y1] numeric? In the datasheet view of the report's record
source, is the field right or left aligned?

--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
Thank Duane. This works in placing the formating in the query, but
I
can't
seem to get it to work to format in a report based upon that query.
I
have
tried putting this expression and several like it in the Format
properties,
but without any effect.

Any other ideas? I am always game to learn and try something.

"Duane Hookom" wrote:

You can try:
Expr1: First([Description] & Chr(13) & Chr(10) & Format([Sales -
Y1],"$0") &
Chr(13) &
Chr(10) & Left([Project Name],20))


--
Duane Hookom
MS Access MVP


"tom at arundel" wrote in
message
...
This is great and it works for me also. Just one more
question --
can
you
format the values within the crosstab? For my crosstab query, I
use
the
expression

Expr1: First([Description] & Chr(13) & Chr(10) & [Sales - Y1] &
Chr(13)
&
Chr(10) & Left([Project Name],20))

to find the value for each row/column intersection. Now, when I
create
the
report, I want to format the [Sales - Y1] fied to currency with
no
decimal
places.

Any ideas?

thanks in advance
"Duane Hookom" wrote:

You don't have to use count or sum. You can use First or Max or
Min
with
a
Value expression like:
colhead:[Audit] & Chr(13) & chr(10) & [DealerNo]

--
Duane Hookom
MS Access MVP
--

"hughess7" wrote in message
...
As detailed in my previous message I don't think a crosstab
query
will
work
and wanted clarification of this and any advice on how I could
maybe
achieve
it? A crosstab query does produce the format of names in rows
and
dates
as
column headings but the trouble is the text to be displayed is
not a
'count'
or 'sum' of anything. I literally just want to display the
contents
of
a
few
fields as per my previous example, showing what activity each
person
is
doing
and a dealer number if they were on an audit, on a daily basis
per
week
range.

"Duane Hookom" wrote:

If the calendar reports don't work for you then try look at
the
crosstab
reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP
--


"hughess7" wrote in
message
...
Hi, I would like a report which prints out a weekly
itinerary
plan
for
our
employees. I have looked at Duane Hookom's reports but they
don't
quite
achieve what I want and not too sure how to replicate
something
similar. I
want a layout like below:

Mon Tues Wed Thurs Frid
3 4 5
6
7
Fred Bloggs Audit Meeting Travel Audit Office
1234 4567
Jane Doe Audit Audit Audit Audit Audit
9876 9876 9876 9876 9876
Bilbo Baggins Holiday Holiday Holiday Holiday
Holiday

I am new to crosstab queries and tried to do one of these
to
diplay
the
info
but couldn't get it to work as the details I want displayed
(audit,
dealer
no, etc) are the values of fields rather than a
calculation.
Can
anyone
advise me how to produce a simple report displaying the
data
in
this
format?
I will add a start and end date filter to a form to display
which
week
I
want
printed, I am just not sure how to get a report in this
layout?

Thanks in advance for any help.
Sue