Re: Formatting date query Column by week.

From: Gary Walter (garylwpleasenospam_at_wamego.net)
Date: 09/23/04 

Date: Thu, 23 Sep 2004 07:05:45 -0500

Hi Raj,

I believe your Column Heading column
in design view would look like:

Field: Expr1: [research_completed]-(Weekday([research_completed])-6)
Table:
Total: Group By
Crosstab: Column Heading

If you go to SQL View of your query,
at the end of the stmt it would show:

PIVOT [research_completed]-(Weekday([research_completed])-6);

I thought you were grouping on the week of
[research_completed], but you wanted to
show the date for the Friday of each week
group as a column heading. I must have really
misunderstood if this was a criteria of sorts.

If I have misunderstood, please copy and paste
the SQL stmt from SQL View that you were using.
I will try to get it right.

Good luck,

Gary Walter

"Raj" wrote:
> Thank for the help but I am having a problem. I put the
> code in the Criteria and first I got an error: "The
> expression you entered has an invalid. (dot) or !
> operator or invalid parentheses. I and parentheses on
> both end of the formula and got this error "Undefined
> function 'PIVOT' in expression"
> Thanks.
> Raj
>
> >-----Original Message-----
> >Hi Raj,
> >
> >Is "last day of work week" = Friday?
> >
> >PIVOT [DateField]-(Weekday([DateField])-6)
> >
> >If Saturday
> >
> >PIVOT [DateField]-(Weekday([DateField])-7)
> >
> >Please respond back if I have misunderstood.
> >
> >Good luck,
> >
> >Gary Walter
> >
> >"Raj" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:196601c4a0c5$82051870$a601280a@phx.gbl...
> >>
> >> I am using this function in a Cross Tab Format
> >> ([research_completed],"ww") to pull the weekly status
> for
> >> employees. What I would like to do is have the last
> day
> >> of the work week displayed at the top of the columns
> >> instead of the number of the week. I hope this is
> >> possible.
> >> Thanks
> >>
> >
> >
> >.
> >

Relevant Pages

  • Re: Best practices for sortable columns
    ... when the user clicks on a column heading, ... There are two steps to displaying lists ... Item 1 can normally be done with one or two switch structures. ... What I personally have done is to create a class which takes a SQL statement to fetch X records and then displays them with all sorts of column variations and headings. ...
    (comp.lang.php)
  • Re: Using Columns in a Crosstab Query as fields in another query
    ... that column heading comes from the value of a show ID field. ... Here's the SQL for the crosstab query: ... And here's the SQL for the bonus points query: ...
    (microsoft.public.access.queries)
  • Re: Report based on Crosstab with dates changing daily
    ... The following is my SQL: ... "Duane Hookom" wrote: ... > Set the column heading expression to: ... > To create column labels in your report, use text boxes with control sources ...
    (microsoft.public.access.reports)
  • Re: Using Columns in a Crosstab Query as fields in another query
    ... that column heading comes from the value of a show ID field. ... > Here's the SQL for the crosstab query: ... > And here's the SQL for the bonus points query: ...
    (microsoft.public.access.queries)
  • Re: Formatting date query Column by week.
    ... >Hi Raj, ... >Crosstab: Column Heading ... >the SQL stmt from SQL View that you were using. ... >> operator or invalid parentheses. ...
    (microsoft.public.access.queries)