Date Format = "Week Of"

[skilche1]

Trying to create a graph, but instead of having the date of results, I'd like to group the data by the "week of". For instance, the week of (May 29, 2006). What is the code for that format that i'd use in the query.

Thank you in advance.

sk

 

[namliam]

You are saying you have a date and want to revert it back to the date of Monday or Sunday?? Why??

Anyway
TheWeekOfMonday: [yourdatefield] - Weekday([yourdatefield],vbmonday) +1

TheWeekOfSunday: [yourdatefield] - Weekday([yourdatefield],vbSunday) +1

Greets & GL

 

[skilche1]

You are saying you have a date and want to revert it back to the date of Monday or Sunday?? Why??

Anyway
TheWeekOfMonday: [yourdatefield] - Weekday([yourdatefield],vbmonday) +1

TheWeekOfSunday: [yourdatefield] - Weekday([yourdatefield],vbSunday) +1

Greets & GL

Actually, I would like to see "April 29" in stead of "22 '06" for the week of....

Thanks.

Here is the sql:

TRANSFORM Count(qry_Sum_Returned.CountOfFab_Fab_DataId) AS CountOfCountOfFab_Fab_DataId
SELECT (Format([Fab_Date_Recd],"ww""  '""yy")) AS Expr1
FROM qry_Sum_Returned
GROUP BY (Year([Fab_Date_Recd])*CLng(54)+DatePart("ww",[Fab_Date_Recd],2)-1), (Format([Fab_Date_Recd],"ww""  '""yy"))
PIVOT qry_Sum_Returned.Fab_Location;

 

[namliam]

Aha, you were looking for the Weeknumber... you should have said so.

Do take note please that the default weeknumber is the American weeknumber. So if you are not in America, double check your settings and your format. There are 2 more options after the format text with which you can define your weeknumbers (and days if you like to)

Oh wait, re-reading your post... You are not looking for the weeknumber but the monday of that week. OK I just made a function for that the other day myself.

If you just need it a certain set of weeks ago or for this week then use the functions/expresions I posted above.

If you use below code, please pay respects in your code

Function FirstDayOfWeek(Year As Integer, WeekNR As Integer) As Date
' Function to refert a weeknumber back to the Monday of that week
    Dim FirstOfYear As Date
    Dim LastOfYear As Date
    Dim DayOfWeek As Integer
    Dim LastWeekOfYear As Integer
    FirstOfYear = DateSerial(Year, 1, 1)
    LastOfYear = DateSerial(Year, 12, 31)
    LastWeekOfYear = Format(LastOfYear, "WW", vbMonday, vbFirstFourDays)
    If LastWeekOfYear = 1 Then
        ' If december 31 is part of week 1 of next year
        LastWeekOfYear = Format(LastOfYear - 7, "WW", vbMonday, vbFirstFourDays)
    End If
    If WeekNR <= LastWeekOfYear Then
        DayOfWeek = Weekday(FirstOfYear, vbMonday)
        'The monday before the first of Jan
        FirstDayOfWeek = (FirstOfYear - DayOfWeek + 1) + WeekNR * 7
        If DayOfWeek <= 4 Then
            'Since weeknrs start on weeknr 1 Mathimaticaly we start on 0
            FirstDayOfWeek = FirstDayOfWeek - 7
        End If
    End If
End Function

I hope this helps.... someone...

 

[skilche1]

Thank you for your speady reply. I do reside on the US, Upstate NY to be exact.

Oh man, did I mention that I am working in the MS Access invironment? This looks like its in VB invironment. Sorry. I am looking for Access help. I am in the right location, aren't I?

Thanks again,

Steve

 

[namliam]

Geepers... Yes kind sir you are in the right place.

This kind of coding is properly named "VBA" or Visual Basic for Applications, you can put it in a "Module" and use it.

You can offcourse do the same thing with a bunch of IIfs in a query and stuff... But this is much easier.... Have you never used the Modules part? I use hardly anything else.... well actually I use it a lot, but offcourse use the rest of access to the same degree...

Oh and by the way, the same stuff (VBA) takes care of stuff that happens when you make a button on a form (assuming you ever did make a form)

 

[skilche1]

Geepers Creapers! LOL No, I have never used modules before. I will see if I can get your script to work. This should be interesting. I am not a professional of Access, so it'll take me some time to figure this one out.

Thanks for your help. I'll keep you informed on how I make out.

Steve

 

[namliam]

Well I am a professional... I know the code to work, as I am using it currently.

It is bound to be not perfect (yet) for example you can give 2006, -1 as the weeknumber without problems, you will simply get a date in 2005.

Just go to the modules tab, Click on New and paste the code. Close and save.

Then in your query simply put call the function
Alias: FirstDayOfWeek(Year, Week)
For 2006, 22 it will return May 29, 2006

Good luck....

 

[skilche1]

Thank you sooooo much mailman. Much appreciated.

I'll keep updated.

 

[skilche1]

Stupid question.... How do I call the funtion in the query. I never had to call a module in the past.

Thanks

 

[namliam]

Without repeating myself...

Then in your query simply put call the function
Alias: FirstDayOfWeek(Year, Week)
For 2006, 22 it will return May 29, 2006

Alias will be your column name in the query

Hope you get it working...

 

[skilche1]

OK, can't seen to get it to work. I have attached the db if you have time to check it out. I am working on a graph in. I have tried this in both the query and the SQL Statement: Query Builder and am having issues.

graph name is "Report2"

Also, if there is no data for a week, can the show that information on the graph as well?

Thanks for your time.

 

[namliam]

For the No data thing:
Open your report, Right-Click on the little square in the top left corner of your design view just below the title bar.
=> Properties
=> Event tab
=> Click in the box behind the No Data "event", this will automaticaly get triggered if there is no data to show
=> Click on the "..." button next to the box
=> Select Code builder in the popup, you will be send to the VBA again.
=> Now type this into the event
MsgBox "Sorry no data", vbCritical ' Display a message
Cancel = True ' Stop the opening of the report

Your expr1 of the query, it shows WW YY. I asume you want to change this to the date?
OK...

Alias: FirstDayOfWeek(Year, Week)

Seeing as the function requires a seperate week and year...
Expr1: FirstDayOfWeek(Format([Fab_Date_Recd],"yyyy"), Format([Fab_Date_Recd],"ww"))

Or what is much more simple (and faster), since you are working with a real date.
Dont go Date => Weeknumber => Date, but just go Date => Date

TheWeekOfMonday: [yourdatefield] - Weekday([yourdatefield],vbmonday) +1

TheWeekOfSunday: [yourdatefield] - Weekday([yourdatefield],vbSunday) +1

Simply replace the Expr1 with either of above that I gave before...

I hope I was clear enough in explaining....

Good Luck

 

[skilche1]

Your expr1 of the query, it shows WW YY. I asume you want to change this to the date?
OK...

Seeing as the function requires a seperate week and year...
Expr1: FirstDayOfWeek(Format([Fab_Date_Recd],"yyyy"), Format([Fab_Date_Recd],"ww"))

Or what is much more simple (and faster), since you are working with a real date.
Dont go Date => Weeknumber => Date, but just go Date => Date

Simply replace the Expr1 with either of above that I gave before...

I hope I was clear enough in explaining....

Good Luck

BINGO. Thanks for the help. That did it.

Looks great.

Steve