Comparing start and end dates

[JBurlison]

Trying to compare start and end dates using a query so this is what i have but nothing is comming up wrong way to go about this? i need to do this for every month till 2012

Expr1: [Start Date]=[Start Date]>1/1/2008
Show: No
Criteria: True

Expr2: [End Date]=[End Date]<1/1/2008
Show: No
Criteria: True

 

[boblarson]

Trying to compare start and end dates using a query so this is what i have but nothing is comming up wrong way to go about this? i need to do this for every month till 2012

Why? What are you trying to do. I have a feeling that there may be a better way of getting to your final desired outcome. What are you doing building queries for every month?

 

[JBurlison]

the nuts and bolts of it im trying to figure out turover rates for each month. i have List of employees and there start and end dates and i need to get the turnover rates for each month. this has proved to be more difficult for me that previously thought how is a better way of going about this?

 

[boblarson]

So, what is the formula for figuring out the turnover rate? I'm not sure about that one.

 

[JBurlison]

Turnover rate is

[number of employees that left that month]\[Number of current employees]

so it would have to work out as :

[number of employees that left that month]\[Number of employees the previous month]

 

[ajetrumpet]

A recordset would work fine here.

A function call would also work, by way of a previous month reference in VBA.


...Come to think of it, you can also do this in the SQL. Might you have to lookup value? Or do both values exist in the same table?

 

[JBurlison]

Humm what i did was, linked the data to excel because the data will be munipulated in meny diffrent ways and wrote this macro in excel dose not work tho, it loops fine, but i cannot get it to store vales in cells, or lookup values that calculated Via (counta)

Sub A()

Dim WB As Workbook
Dim ws As Worksheet
Dim StartDate As Date
Dim EndDate As Date
Dim CurrentDate As Date
Dim Rounds As Integer
Dim LastDate As Integer
Dim LastCount As Integer
Dim Turnover As Integer
Static OnDate As Integer
Static Count As Integer
Static CurrentRound As Integer

Set WB = ThisWorkbook
Set ws = WB.Worksheets("Employees")
OnDate = ws.Cells(12, 6) 'has "counta" counts the number of months so it knows how meny columns to go over in the loop
Rounds = ws.Cells(12, 5) 'has "counta" counts the number of users to know how meny rows to go down in the loop
CurrentRound = 0
Count = 0
Turnover = 0
LastDate = 0

GoTo Nax

Nax:

    CurrentDate = ws.Cells(1, (13 + LastDate))
    EndDate = ws.Cells((2 + CurrentRound), 8)
    StartDate = ws.Cells((2 + CurrentRound), 7)
    CurrentRound = CurrentRound + 1 ' Moves to the next row

If StartDate < CurrentDate And EndDate > CurrentDate Then 'Makes sure the date is within range

    Count = Count + 1

    Else
End If

If CurrentRound <> Rounds Then 'Checks to see if theres data here

    GoTo Nax

Else
LastDate = LastDate + 1 ' Moves to the next column
LastCount = Count

    If LastDate <> OnDate Then 'Checks to see if theres data here
        
        Turnover = (LastCount - Count) / Count
        ws.Cells(13, (1 + LastDate)) = Turnover
        ws.Cells(13, (2 + LastDate)) = Count
        CurrentRound = 0
        GoTo Nax

        Else
    End If


End If

End Sub

Not working =/