You need to create the Column Headings field as
ColHead:"D" & DateDiff("D",[Day],[Forms]![frmReportSelect]![txtEndDate])
This does not go in your criteria.
--
Duane Hookom
MS Access MVP
--
Duane,
I did as you said. I have set up a new form called frmReportSelect to
compare the date fields. The fields are called txtStartDate and
txtEndDate.
I actually did some research and found
http://allenbrowne.com/tips.html/
website which I found very useful. The textboxes are unbound in the new
form
that I created. I am having a better understanding on what you were
telling
me earlier. After I set up the form and textfields I went back to my
crosstab query. Before I changed or added anything to the query I did a
preview of the data. Everything looks good. The dates are going across
the
page with correct information under the dates. The next step was to be
able
to change the date field column heading to D0.D1,D2,D3,D4 in the
properties
section of the date field in the query. I then continued to add the
expression to the crietiera section
"D" & DateDiff("D",[Day],[Forms]![frmReportSelect]![txtEndDate])
I then went to the query/Parameter and added this
Forms!frmReportSelect!txtEndDate Date/Time
After I made all these changes I did a preview of my data and came up with
nothing. If I go back and delete what I add to the column headings in the
query I get the data back to the original way with the dates going across
the
page with the correct information under the dates. Now my question is why
does the query not work when I add the D0,D1,D2,,,to the column heading
properties? My dates will always be changing so when I do a report I will
need to reflect the change on my dates.
Thanks
Robin
Thanks
Robin
:
My solution depends on two things,
1) your table with the appropriate fields
2) your text box on a form which is used to compare with a date field
from
your table
I would suggest that you don't any further with this solution until you
have
a form with a text box to enter the ending or beginning date.
--
Duane Hookom
MS Access MVP
Duane,
I have not added a filter for the beginning date and ending date. I
was
going to add Between [StartDate] And [EndDate] to the query design view
in
the Day field after I had finished the report layout. But it sounds
like
I
need to something a little different.
When creating the query I combined two tables. That query was called
Student Roster for Lunch form(I should have used a different name).
When
I
created the crosstab query I used the query that I just had set up and
the
crosstab query is named Student Roster for Lunch form_Crosstab1. SQL
statement is from this crosstab query.
The Form name (input form ) is called Lunch. It is a very basic input
form.
It consist of the following fields: Day, LunchID, Student Name,
LunchType
Did I confuse you more?
Sorry for all the mess, please get me out of it.
Robin
:
This doesn't look at all like the original table structure you
provided.
Do
you actually use the word "Form" in a table or query name. This is so
confusing I can't begin to understand this (at my age).
What is your form name and ending date text box that controls/filters
your
crosstab?
--
Duane Hookom
MS Access MVP
Hello,
This is my second time posting this message. It gave me errors
during
the
first posting and was worried that it did not go through.
Here is the SQL you requested I hope this helps.
TRANSFORM Count([Student Roster for Lunch form].LunchType) AS [The
Value]
SELECT [Student Roster for Lunch form].Grade, [Student Roster for
Lunch
form].[Student Name], [Student Roster for Lunch form].LunchType,
Count([Student Roster for Lunch form].LunchType) AS [Total Of
LunchType]
FROM [Student Roster for Lunch form]
GROUP BY [Student Roster for Lunch form].Grade, [Student Roster for
Lunch
form].[Student Name], [Student Roster for Lunch form].LunchType
PIVOT [Student Roster for Lunch form].Day;
The input form name is Lunch. I enter a date in the field called
Day
in
the
input form. Did I set this up wrong?
Thanks,
Robin
:
Do you have a form open named frmDates with a text box named
txtEndDate?
Can
you paste the SQL of your current query into a reply?
--
Duane Hookom
MS Access MVP
Duane,
I am sorry, but I am still having trouble. I went to the
crosstab
query
and
clicked on the field Date and typed the expression in the
column
heading
space. This is what I typed: ColHead: "D" &
DateDiff("D",[Date],[Forms]![frmDates]![txtEndDate]).
After I type that in, I try to open the query to check the
data
before I
go
any further. I get an error msg. Syntax error (missing
operator)
in
query
expression. What am I'm doing wrong. My input form name is
called
Lunch.
I
have listed previously the field names that are in the table.
Am
I'm
using
the right names in the above expression for the columning
headings
and
also
did I type it in the right place in the qurey?
Thanks,
Robin
:
Use my previous response but change month stuff to date/day
stuff.
If
you
always want 5 days then use only the ending date since the
beginning
date
can be calculated. You column headings would use and
expression
like
ColHead: "D" &
DateDiff("D",[Date],[Forms]![frmDates]![txtEndDate])
Set the column headings property to
D0,D1,D2,..D4
Change some of the other stuff from months to days.
--
Duane Hookom
MS Access MVP
Hello,
Table: Lunch
Fields: LunchID AutoNumbering
StudentName Text
LunchType Text
Date Number
Grade Number
Records:
LunchID Student Name Grade Date
LunchType
1 Joe Black 1
10/11/04
H
2 Joe Black 1
10/12/04
H
3 Joe Black 1
10/13/04
H
4 Joe Black 1
10/14/04
H
5 Joe Black 1
10/15/04
H
6 Joe Black 1
10/18/04
S
7 Joe Black 1
10/19/04
S
8 Kyle Hampton 2 10/11/04
H
9 Kyle Hampton 2 10/13/04
H
10 Kyle Hampton 2 10/14/04
S
11 Kyle Hampton 2 10/18/04
H
I will be using 5 date columns (M T W TH F). I want to
sort
the
report by
grade and then list the students in alpha order with the
dates
going
across
the page along with the students name. I will put in a
beginning
date
and
ending date. That way I will be able to print a weekly
report
based
on
the
days I entered. At the end of the report I will have a
total
of
how
many
H
or S I have per date. A total for each column.
If this is not enough information please let me know.
Thanks,
Robin
:
Could you share your current table structure with actual
table
and
field
names as well as some records? I also need to know how
many
date
columns
you
expect to display and if these are always based on the
current
date.
--
Duane Hookom
MS Access MVP
--
message
Duane,
I am still a little lost on how to do the report. Here
is
my
goal.
The
report is to print students who are ordering Hot
lunches(H) or
Salads
(S).
The students order their lunches everyday. Now I need
to
produce
a
report
that will print out the students name in one column and
the
order
date
in
the
other columns. Under the order date field it will
print
what
ever
the
student ordered (H or S). I will total the number of H
or
S
for
each
date.
I'm sorry if I made this confusing. The first report
that
I
designed
listed
the students in date order going down the page. I need
it
to
look
more
like
a spreadsheet report with dates going across the page.
Thanks again for all your help
Robin
:
Copying my response from your similar question in
another
thread...
Substitute dates for months in this solution.
Try not to use "absolute" column headings for dates.
You
could
possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a
crosstab
report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time
Use this expression for your Column Headings:
ColHead:"Mth" &
DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where
Mth0
is
sales
from
the
same month as the ending date on your form. Mth1 is
the
previous
month
etc.
Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If
you
need
column
labels in your report, use text boxes with control
sources
of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly
quickly.
--
Duane Hookom
MS Access MVP
message
I need to print a report showing the following:
Name 8/26 8/27 8/28 8/29 8/30
Jane H S H H
Bob H H H
S
Don S H H H
Total H 2 2 3 2
Total S 1 1
1
I can print the report sorting by date and giving me
a
count on
my
variables
but How do I print this sorting the dates going
across
the
page
and
keeping
the data with the Name field. I will be using a
Beginning
date
and
Ending
date for the report.
Thanks for all your help
Robin
