Finding number of slots used per month 1

[Tower03]

Hi I am currently trying to work on a system. This system is a booking system. I have a table with the fields slot and date. Every month I would like to produce a statistical report to show how often each slot has been used (e.g. Monday Morning 75% of the time) however I cannot find a way to specify the day that it is on. Would be much appreciated if somebody could help. Thanks very much.

 

[Golom]

You can extract the day of the week from a date field with

WeekDay([SomeDateField])

it will return 1=Sunday, 2=Monday, etc.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Tower03]

THanks a lot where would I input this code?Into a criteria on a query?

 

[Melagan]

SQL:

SELECT WeekDay([YourDateField]) as MyWeekDay
FROM yourTable
WHERE MyWeekDay = 2

That should return all records where YourDateField = Monday
1 = Sunday
2 = Monday
..
7 = Saturday

Ofcourse you'll need to add more fields to your SELECT statement to get all of the data you want - you can do it by typing in SQL or by adding them to your QBE (query by example) window.


~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

This might be fun (I had some extra time today)!

Put this code in a new module: (really simple code, I'm sure there is a more elegant way to do this but I'm not that good yet!)

Public Function MyWeekDay(MyDateField As Date) As String
Dim intDay As Integer
On Error GoTo Err_Handler

intDay = Weekday(MyDateField)

    Select Case intDay
        Case 1:
            MyWeekDay = "Sunday"
        Case 2:
            MyWeekDay = "Monday"
        Case 3:
            MyWeekDay = "Tuesday"
        Case 4:
            MyWeekDay = "Wednesday"
        Case 5:
            MyWeekDay = "Thursday"
        Case 6:
            MyWeekDay = "Friday"
        Case 7:
            MyWeekDay = "Saturday"
    End Select

ExitHandler:
    Exit Function

Err_Handler:
    MsgBox Err.Description
    Resume ExitHandler

End Function

Now instead of using Weekday() in your query, you can use something like this:

SELECT MyWeekDay([YourDateField]) as ActualDay
FROM yourTable
WHERE ActualDay = "Tuesday"

Now then - that will return all records where YourDateField is Tuesday! (instead of using the integer values for the day of the week, you just use the actual string of the day)



~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

Hi Melagan !
Why reinvent the wheel ?
SELECT WeekdayName(Weekday([YourDateField])) AS ActualDay

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

Oh Scandless =) There are all kinds of functions in VBA that I don't know about - it was fun to write a custom module anyway LOL



~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

Yet another way ;-)
SELECT Format([YourDateField], 'dddd') AS ActualDay

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886