Weekly report 1

[hyrogen]

Hi,

I have a report which currently displays information over any given month. What I want to do is to be able to limit it to any one single week with in that month. For example, Month:May, week: 3.

I really have no idea where to start on this, any help would be fantastic!, thanks

 

[scriverb]

Here is an example of a query SQL that will prompt for a Month entry(numeric) and a Week entry(numeric) and select records and display all fields for the table:

Select A.* 
FROM [i]yourtablename[/i] as A 
WHERE Month(A.[i]datefield[/i] = [Enter Month: ] and 
DatePart("ww", A.[i]datefield[/i]) = [Enter Week#: ];

Let me know if this is what you were looking for.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[hyrogen]

Well I think I've tried doing that already. The datepart ww returns the week of the year which could be 48. What I need is a value of 1 to 5, to select which week of the month the report needs to display.

 

[scriverb]

You are correct about that. Here is a modified SQL:

Select A.*
FROM [i]yourtablename[/i] as A
WHERE Month(A.[datefield] = [Enter Month: ] and
Switch(Day(A.[datefield])< 8,1,Day(A.[datefield])< 15,2,Day(A.[datefield])< 22,3,Day(A.[datefield])< 29,4,True,5) = [Enter Week#: ];

This should do it.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[hyrogen]

Thanks for your help, but when I try to run your query, I just get a syntax error?

 

[scriverb]

What does it say?

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[PaulBricker]

Bob, if your week breakdown is correct then you could use this type of expression in place of the switch.

Int(DatePart("d",[datefield])/7)+1

It would look like this

Select A.*
FROM yourtablename as A
WHERE Month(A.[datefield] = [Enter Month: ] and
Int(DatePart("d",[datefield])/7)+1 = [Enter Week#: ];

Paul

 

[PaulBricker]

Sorry, I should preview these things. It should be divided by 7.1 not 7

Int(DatePart("d",[datefield])/7.1)+1

Paul

 

[scriverb]

Thanks for the addition Paul. That will work also. I still don't know what the Syntax error is in the original SQL. You code for the Week is probably more effecient as calling for less function calls.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[cdavisdccb]

Scriverb,

Your code was what I've been looking for.
I had to modify it because my week starts on Sunday.

Week#: Switch(Day([Datefield])<7,1,Day([Datefield])<14,2,Day([Datefield])<21,3,Day([Datefield])<28,4,True,5)

I just dropped the <'s down one.

Paul's code

Int(DatePart("d",[datefield])/7.1)+1

dividing by 7.1 doesn't return correct monthly week numbers for every month, but was pretty darn close. The beginning of each month seemed ok, but halfway through, you would gradually get off one day.
I didn't have the time but I'll bet if you played with the numbers a bit, you could get it down to a tee.

Anyway, Thanks again.
Chris

"It doesn't matter how idiot-proof you make it, someone makes a better idiot!"
- Bazooka Joe