Report By Defined Dates

[Guest]

I want to create what I thought was a simple report but after hours I am no
closer...

I want it to look like:
Wave 1 (September 1, 2004 to January 6, 2005)
Session End Date1 Facilitator1
Session End Date2 Facilitator2
(with ascending sort on the dates)

Such as:
Wave 1 (September 1, 2004 to January 6, 2005)
October 10, 2004 Betty Smith
November 24, 2004 Frank John

BUT the trick is I have 3 Waves:
- Wave 1 (September 1, 2004 to January 6, 2005)
- Wave 2 (January 7, 2005 to August 31, 2005)
- Wave 3 (August 31, 2005 onwards)

When I try using "=([Session End Date]>#1/06/05# And #9/01/05#)" in Sorting
and Grouping, I get an "invalid syntax error".

In Sorting and Grouping I've tried Grouping on Interval for:
=([Session End Date]<#1/07/05#)
but the report just lists the dates willy-nilly.

I have checked that the Sesssion End Date is in Date/Time format.

Using Access2000 on XP.

I'm very new to Access and although the reference book I bought has helped
me out a lot, I can't figure this one out. Any assistance greatly
appreciated.

cheers,
karen

 

[strive4peace]

Hi Karen,

make a calculated field in the underlying recordset

field --> Wave: IIF([datefield]>=#9/1/04# AND [datefield] <=
#1/6/05#,1,IIF([datefield]>=#1/7/05# AND [datefield] <=
#8/31/05#,2,3)

Then you can simply Group On Wave

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

 

[Guest]

Hi Crystal,

Thx a mint for your reply. I will test it and see how it works.

cheers,
karen

Hi Karen,

make a calculated field in the underlying recordset

field --> Wave: IIF([datefield]>=#9/1/04# AND [datefield] <=
#1/6/05#,1,IIF([datefield]>=#1/7/05# AND [datefield] <=
#8/31/05#,2,3)

Then you can simply Group On Wave

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

I want to create what I thought was a simple report but after hours I am no
closer...

I want it to look like:
Wave 1 (September 1, 2004 to January 6, 2005)
Session End Date1 Facilitator1
Session End Date2 Facilitator2
(with ascending sort on the dates)

Such as:
Wave 1 (September 1, 2004 to January 6, 2005)
October 10, 2004 Betty Smith
November 24, 2004 Frank John

BUT the trick is I have 3 Waves:
- Wave 1 (September 1, 2004 to January 6, 2005)
- Wave 2 (January 7, 2005 to August 31, 2005)
- Wave 3 (August 31, 2005 onwards)

When I try using "=([Session End Date]>#1/06/05# And #9/01/05#)" in Sorting
and Grouping, I get an "invalid syntax error".

In Sorting and Grouping I've tried Grouping on Interval for:
=([Session End Date]<#1/07/05#)
but the report just lists the dates willy-nilly.

I have checked that the Sesssion End Date is in Date/Time format.

Using Access2000 on XP.

I'm very new to Access and although the reference book I bought has helped
me out a lot, I can't figure this one out. Any assistance greatly
appreciated.

cheers,
karen

 

[strive4peace]

you're welcome, Karen

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Hi Crystal,

Thx a mint for your reply. I will test it and see how it works.

cheers,
karen

:

Hi Karen,

make a calculated field in the underlying recordset

field --> Wave: IIF([datefield]>=#9/1/04# AND [datefield] <=
#1/6/05#,1,IIF([datefield]>=#1/7/05# AND [datefield] <=
#8/31/05#,2,3)

Then you can simply Group On Wave

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

I want to create what I thought was a simple report but after hours I am no
closer...

I want it to look like:
Wave 1 (September 1, 2004 to January 6, 2005)
Session End Date1 Facilitator1
Session End Date2 Facilitator2
(with ascending sort on the dates)

Such as:
Wave 1 (September 1, 2004 to January 6, 2005)
October 10, 2004 Betty Smith
November 24, 2004 Frank John

BUT the trick is I have 3 Waves:
- Wave 1 (September 1, 2004 to January 6, 2005)
- Wave 2 (January 7, 2005 to August 31, 2005)
- Wave 3 (August 31, 2005 onwards)

When I try using "=([Session End Date]>#1/06/05# And #9/01/05#)" in Sorting
and Grouping, I get an "invalid syntax error".

In Sorting and Grouping I've tried Grouping on Interval for:
=([Session End Date]<#1/07/05#)
but the report just lists the dates willy-nilly.

I have checked that the Sesssion End Date is in Date/Time format.

Using Access2000 on XP.

I'm very new to Access and although the reference book I bought has helped
me out a lot, I can't figure this one out. Any assistance greatly
appreciated.

cheers,
karen

 

[Guest]

Hi Crystal,

Actually, I've been trying this for the last couple of hours for my latest
report and it almost works...

I added your formula to a Field in my Query and it renamed itself "Expr1".
When I run the query, the numbers (i.e., Wave 1, Wave 2, Wave 3 assignments)
are correct in that column.

I selected Group By but when I run the Report, it only lists everything as
the Wave 1.

I'd like the "spreadsheet" report to look like:
Group1 | Group2 | Group 3
Wave 1
Wave 2
Wave 3

The Group1 categories are adding up correctly, it's just that it is not
breaking it out by Wave. I have the "Expr1" in my report.

Maybe I got it wrong because when you say "underlying recordset" do you mean
in the table as a column? I tried to put it in as an expression and it
didn't do anything, and when I tried to change its format to "Number" (from
"Text") it gave me a 'going to wipe out a lot of data error'. So if I'm to
add it into the table, how do I do that as an Expression?

Any suggestions...is this a report problem?

cheers,
karen

you're welcome, Karen

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Hi Crystal,

Thx a mint for your reply. I will test it and see how it works.

cheers,
karen

:

Hi Karen,

make a calculated field in the underlying recordset

field --> Wave: IIF([datefield]>=#9/1/04# AND [datefield] <=
#1/6/05#,1,IIF([datefield]>=#1/7/05# AND [datefield] <=
#8/31/05#,2,3)

Then you can simply Group On Wave

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com


klam wrote:

I want to create what I thought was a simple report but after hours I am no
closer...

I want it to look like:
Wave 1 (September 1, 2004 to January 6, 2005)
Session End Date1 Facilitator1
Session End Date2 Facilitator2
(with ascending sort on the dates)

Such as:
Wave 1 (September 1, 2004 to January 6, 2005)
October 10, 2004 Betty Smith
November 24, 2004 Frank John

BUT the trick is I have 3 Waves:
- Wave 1 (September 1, 2004 to January 6, 2005)
- Wave 2 (January 7, 2005 to August 31, 2005)
- Wave 3 (August 31, 2005 onwards)

When I try using "=([Session End Date]>#1/06/05# And #9/01/05#)" in Sorting
and Grouping, I get an "invalid syntax error".

In Sorting and Grouping I've tried Grouping on Interval for:
=([Session End Date]<#1/07/05#)
but the report just lists the dates willy-nilly.

I have checked that the Sesssion End Date is in Date/Time format.

Using Access2000 on XP.

I'm very new to Access and although the reference book I bought has helped
me out a lot, I can't figure this one out. Any assistance greatly
appreciated.

cheers,
karen

 

[strive4peace]

Hi Karen,

in your SQL statement, what comes after AS will be the name
of the column. If you want to include "Wave " before the
number, you can do this:

"Wave " & IIF([datefield]>=#9/1/04# AND [datefield] <=
#1/6/05#,1,IIF([datefield]>=#1/7/05# AND [datefield] <=
#8/31/05#,2,3) AS WaveNumber

if you are modifying the query on the grid, anything before
the colon is the column name

change

Expr1: IIF([datefield]>=#9/1/04# AND ...

to

WaveNumber: "Wave " & IIF([datefield]>=#9/1/04# AND ...

sounds like what you want to do is make a query to gather
the information, then make a crosstab query using your first
query.

Click the NEW button when you are in the database window
listing queries

choose the Crosstab Query wizard and follow the instructions

then, base your report on the crosstab query

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Hi Crystal,

Actually, I've been trying this for the last couple of hours for my latest
report and it almost works...

I added your formula to a Field in my Query and it renamed itself "Expr1".
When I run the query, the numbers (i.e., Wave 1, Wave 2, Wave 3 assignments)
are correct in that column.

I selected Group By but when I run the Report, it only lists everything as
the Wave 1.

I'd like the "spreadsheet" report to look like:
Group1 | Group2 | Group 3
Wave 1
Wave 2
Wave 3

The Group1 categories are adding up correctly, it's just that it is not
breaking it out by Wave. I have the "Expr1" in my report.

Maybe I got it wrong because when you say "underlying recordset" do you mean
in the table as a column? I tried to put it in as an expression and it
didn't do anything, and when I tried to change its format to "Number" (from
"Text") it gave me a 'going to wipe out a lot of data error'. So if I'm to
add it into the table, how do I do that as an Expression?

Any suggestions...is this a report problem?

cheers,
karen

:

you're welcome, Karen

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Hi Crystal,

Thx a mint for your reply. I will test it and see how it works.

cheers,
karen

:



Hi Karen,

make a calculated field in the underlying recordset

field --> Wave: IIF([datefield]>=#9/1/04# AND [datefield] <=
#1/6/05#,1,IIF([datefield]>=#1/7/05# AND [datefield] <=
#8/31/05#,2,3)

Then you can simply Group On Wave

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com


klam wrote:


I want to create what I thought was a simple report but after hours I am no
closer...

I want it to look like:
Wave 1 (September 1, 2004 to January 6, 2005)
Session End Date1 Facilitator1
Session End Date2 Facilitator2
(with ascending sort on the dates)

Such as:
Wave 1 (September 1, 2004 to January 6, 2005)
October 10, 2004 Betty Smith
November 24, 2004 Frank John

BUT the trick is I have 3 Waves:
- Wave 1 (September 1, 2004 to January 6, 2005)
- Wave 2 (January 7, 2005 to August 31, 2005)
- Wave 3 (August 31, 2005 onwards)

When I try using "=([Session End Date]>#1/06/05# And #9/01/05#)" in Sorting
and Grouping, I get an "invalid syntax error".

In Sorting and Grouping I've tried Grouping on Interval for:
=([Session End Date]<#1/07/05#)
but the report just lists the dates willy-nilly.

I have checked that the Sesssion End Date is in Date/Time format.

Using Access2000 on XP.

I'm very new to Access and although the reference book I bought has helped
me out a lot, I can't figure this one out. Any assistance greatly
appreciated.

cheers,
karen

 

[Guest]

Hi Crystal,

Thx a mint again for your easy-to-follow recommendations. As you can tell,
I'm a complete newbie - in your last post I learnt a lot that I can apply to
most of my queries now. Taking away the "mysticism" of Access makes it a lot
easier to learn as I don't get caught up in all side issues.

Your post made me think "straight" about what I was doing and I realized
later that I hadn't Sorted and Grouped on WaveNumber. When I changed it,
TA-DA!

I'm going to try out your cross tab instructions too as I haven't done that
before - I'm sure I'll need it before too long...LOL!

Thx again, I really appreciate it...hope you have a wonderful weekend!!

cheers,
karen

Hi Karen,

in your SQL statement, what comes after AS will be the name
of the column. If you want to include "Wave " before the
number, you can do this:

"Wave " & IIF([datefield]>=#9/1/04# AND [datefield] <=
#1/6/05#,1,IIF([datefield]>=#1/7/05# AND [datefield] <=
#8/31/05#,2,3) AS WaveNumber

if you are modifying the query on the grid, anything before
the colon is the column name

change

Expr1: IIF([datefield]>=#9/1/04# AND ...

to

WaveNumber: "Wave " & IIF([datefield]>=#9/1/04# AND ...

sounds like what you want to do is make a query to gather
the information, then make a crosstab query using your first
query.

Click the NEW button when you are in the database window
listing queries

choose the Crosstab Query wizard and follow the instructions

then, base your report on the crosstab query

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Hi Crystal,

Actually, I've been trying this for the last couple of hours for my latest
report and it almost works...

I added your formula to a Field in my Query and it renamed itself "Expr1".
When I run the query, the numbers (i.e., Wave 1, Wave 2, Wave 3 assignments)
are correct in that column.

I selected Group By but when I run the Report, it only lists everything as
the Wave 1.

I'd like the "spreadsheet" report to look like:
Group1 | Group2 | Group 3
Wave 1
Wave 2
Wave 3

The Group1 categories are adding up correctly, it's just that it is not
breaking it out by Wave. I have the "Expr1" in my report.

Maybe I got it wrong because when you say "underlying recordset" do you mean
in the table as a column? I tried to put it in as an expression and it
didn't do anything, and when I tried to change its format to "Number" (from
"Text") it gave me a 'going to wipe out a lot of data error'. So if I'm to
add it into the table, how do I do that as an Expression?

Any suggestions...is this a report problem?

cheers,
karen

:

you're welcome, Karen

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com


klam wrote:

Hi Crystal,

Thx a mint for your reply. I will test it and see how it works.

cheers,
karen

:



Hi Karen,

make a calculated field in the underlying recordset

field --> Wave: IIF([datefield]>=#9/1/04# AND [datefield] <=
#1/6/05#,1,IIF([datefield]>=#1/7/05# AND [datefield] <=
#8/31/05#,2,3)

Then you can simply Group On Wave

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com


klam wrote:


I want to create what I thought was a simple report but after hours I am no
closer...

I want it to look like:
Wave 1 (September 1, 2004 to January 6, 2005)
Session End Date1 Facilitator1
Session End Date2 Facilitator2
(with ascending sort on the dates)

Such as:
Wave 1 (September 1, 2004 to January 6, 2005)
October 10, 2004 Betty Smith
November 24, 2004 Frank John

BUT the trick is I have 3 Waves:
- Wave 1 (September 1, 2004 to January 6, 2005)
- Wave 2 (January 7, 2005 to August 31, 2005)
- Wave 3 (August 31, 2005 onwards)

When I try using "=([Session End Date]>#1/06/05# And #9/01/05#)" in Sorting
and Grouping, I get an "invalid syntax error".

In Sorting and Grouping I've tried Grouping on Interval for:
=([Session End Date]<#1/07/05#)
but the report just lists the dates willy-nilly.

I have checked that the Sesssion End Date is in Date/Time format.

Using Access2000 on XP.

I'm very new to Access and although the reference book I bought has helped
me out a lot, I can't figure this one out. Any assistance greatly
appreciated.

cheers,
karen

 

[strive4peace]

you're welcome, Karen happy to help

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Hi Crystal,

Thx a mint again for your easy-to-follow recommendations. As you can tell,
I'm a complete newbie - in your last post I learnt a lot that I can apply to
most of my queries now. Taking away the "mysticism" of Access makes it a lot
easier to learn as I don't get caught up in all side issues.

Your post made me think "straight" about what I was doing and I realized
later that I hadn't Sorted and Grouped on WaveNumber. When I changed it,
TA-DA!

I'm going to try out your cross tab instructions too as I haven't done that
before - I'm sure I'll need it before too long...LOL!

Thx again, I really appreciate it...hope you have a wonderful weekend!!

cheers,
karen

:

Hi Karen,

in your SQL statement, what comes after AS will be the name
of the column. If you want to include "Wave " before the
number, you can do this:

"Wave " & IIF([datefield]>=#9/1/04# AND [datefield] <=
#1/6/05#,1,IIF([datefield]>=#1/7/05# AND [datefield] <=
#8/31/05#,2,3) AS WaveNumber

if you are modifying the query on the grid, anything before
the colon is the column name

change

Expr1: IIF([datefield]>=#9/1/04# AND ...

to

WaveNumber: "Wave " & IIF([datefield]>=#9/1/04# AND ...

sounds like what you want to do is make a query to gather
the information, then make a crosstab query using your first
query.

Click the NEW button when you are in the database window
listing queries

choose the Crosstab Query wizard and follow the instructions

then, base your report on the crosstab query

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Hi Crystal,

Actually, I've been trying this for the last couple of hours for my latest
report and it almost works...

I added your formula to a Field in my Query and it renamed itself "Expr1".
When I run the query, the numbers (i.e., Wave 1, Wave 2, Wave 3 assignments)
are correct in that column.

I selected Group By but when I run the Report, it only lists everything as
the Wave 1.

I'd like the "spreadsheet" report to look like:
Group1 | Group2 | Group 3
Wave 1
Wave 2
Wave 3

The Group1 categories are adding up correctly, it's just that it is not
breaking it out by Wave. I have the "Expr1" in my report.

Maybe I got it wrong because when you say "underlying recordset" do you mean
in the table as a column? I tried to put it in as an expression and it
didn't do anything, and when I tried to change its format to "Number" (from
"Text") it gave me a 'going to wipe out a lot of data error'. So if I'm to
add it into the table, how do I do that as an Expression?

Any suggestions...is this a report problem?

cheers,
karen

:



you're welcome, Karen

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com


klam wrote:


Hi Crystal,

Thx a mint for your reply. I will test it and see how it works.

cheers,
karen

:




Hi Karen,

make a calculated field in the underlying recordset

field --> Wave: IIF([datefield]>=#9/1/04# AND [datefield] <=
#1/6/05#,1,IIF([datefield]>=#1/7/05# AND [datefield] <=
#8/31/05#,2,3)

Then you can simply Group On Wave

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com


klam wrote:



I want to create what I thought was a simple report but after hours I am no
closer...

I want it to look like:
Wave 1 (September 1, 2004 to January 6, 2005)
Session End Date1 Facilitator1
Session End Date2 Facilitator2
(with ascending sort on the dates)

Such as:
Wave 1 (September 1, 2004 to January 6, 2005)
October 10, 2004 Betty Smith
November 24, 2004 Frank John

BUT the trick is I have 3 Waves:
- Wave 1 (September 1, 2004 to January 6, 2005)
- Wave 2 (January 7, 2005 to August 31, 2005)
- Wave 3 (August 31, 2005 onwards)

When I try using "=([Session End Date]>#1/06/05# And #9/01/05#)" in Sorting
and Grouping, I get an "invalid syntax error".

In Sorting and Grouping I've tried Grouping on Interval for:
=([Session End Date]<#1/07/05#)
but the report just lists the dates willy-nilly.

I have checked that the Sesssion End Date is in Date/Time format.

Using Access2000 on XP.

I'm very new to Access and although the reference book I bought has helped
me out a lot, I can't figure this one out. Any assistance greatly
appreciated.

cheers,
karen