Extract years between two dates

[koolwaters]

Hi,

I need to extract the years between two dates.

For example:

Start Date 2010-09-07
End Date 2014-08-30

The number of years between the Start Date and End Date are four.

As part of my query result, I need to see only the years between these two dates. So I would see,

2011 2012 2013 2014

If I have
Start Date 2010-09-07
End Date 2013-08-30

I need to see 2011 2012 2013

I hope that someone can assist.

Thanks in advance.

 

[koolwaters]

Once again, thank you very much for your help.

It works perfectly now.

Cheers!

 

[koolwaters]

I actually still need it in the forecast because it lets us know what funds we need to request from Management to cover the disbursements for the year.

The budget is allocated at the beginning of each fiscal.

We have a cut-off dates for applications so that we do not run into problems of not having enough money to cover the requests that we have.

Thanks again!

 

[Kreszch68]

You're welcome.
Nice to hear it's all sorted then.

Cheers.

 

[koolwaters]

Hi,

Another query for you.

I have now been asked to provide a summary of this data by the employee's Department.

I have created a query based on my ttblForecat called qryForecast. Because the years will be dynamic, I have used the asterisk to use all of the fields from that table in my query.

The challenge comes in providing totals for each year. So basically, if 3 employees are form the IT Department, I need to see the Department in the 1st column and then the total Amount Approved and each year for those 3 employees as opposed to 3 separate records.

I have been at it since last night but I cannot find a way to get this done since the years are dynamic.

 

[Kreszch68]

Hello agian,

I'm not sure if I understand what your asking.

This is clear:
The challenge comes in providing totals for each year. So basically, if 3 employees are form the IT Department, I need to see the Department in the 1st column and then the total Amount Approved

from here don't follow:
and each year for those 3 employees as opposed to 3 separate records.

 

[koolwaters]

Ok. The Sheet below is the output generated by the code you provided.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Department	ApplicationID	EmployeeID	StartDate	EndDate	AmountApproved	2009 - Amt in (US$)	2010 - Amt in (US$)	2011 - Amt in (US$)	2012 - Amt in (US$)	2013 - Amt in (US$)
2	Human Resources	1	1	14-Sep-09	30-Aug-13	$ 8,000.00		2,000.00	2,000.00	2,000.00	2,000.00
3	Corporate	2	2	01-Mar-11	01-Apr-11	$ 6,000.00			6,000.00
4	Human Resources	3	1	01-Mar-11	01-Apr-11	$ 6,000.00			6,000.00
Sheet1

Excel 2007

This is the additional output which I also need.

Excel Workbook
	A	B	C	D	E	F	G
7	Department	AmountApproved	2009 - Amt in (US$)	2010 - Amt in (US$)	2011 - Amt in (US$)	2012 - Amt in (US$)	2013 - Amt in (US$)
8	Human Resources	$ 14,000.00		2,000.00	8,000.00	2,000.00	2,000.00
9	Corporate	$ 6,000.00			6,000.00
Sheet1

Excel 2007

So, I need to have summaries by Department.

I hope you can follow.

 

[koolwaters]

Since the years will always be dynamic, I am not sure how to get the summaries.

 

[Kreszch68]

Hi Michelle,

dynamic tables require dynamic queries,

next code creates a new querydef. Read comments to adjust before you run the code

Public Sub CreateDynamicQuery()
'Create a querydefinition
'call each time you want to query the data
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim qdf As DAO.QueryDef
Dim iFldCnt As Integer
Dim sSumFields() As String
Dim sSQL_Grouped As String
Const sGroupField As String = "EmployeeID" 'This should be Department, but I had this already in the table over here, change to correct field
Const sTblName As String = "ttblForeCast"
Const sTempQueryName As String = "qForeCast"
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(sTblName)
iFldCnt = 0
'Loop fields of forecast table
    For Each fld In tdf.Fields
 
        ReDim Preserve sSumFields(iFldCnt)
 
        Select Case fld.Name
                Case sGroupField
                    sSumFields(iFldCnt) = "[" & fld.Name & "]"
                     iFldCnt = iFldCnt + 1
                Case "ApplicationID", "StartDate", "EndDate" ' , "EmployeeID"     'these are the fields to skip, you need to add the EmployeeID
                'do nothing
                Case Else
                    sSumFields(iFldCnt) = "Sum([" & fld.Name & "]) as [Total " & fld.Name & "]"
                    iFldCnt = iFldCnt + 1
        End Select
 
    Next fld
'Construct the sql statement
sSQL_Grouped = "Select " & Join(sSumFields, ", ") & " from " & sTblName & " Group By [" & sGroupField & "]"
'create dynamic querydef
'first delete the query if exists
    For Each qdf In dbs.QueryDefs
        If qdf.Name = sTempQueryName Then dbs.QueryDefs.Delete qdf.Name
    Next qdf
'this creates the querydef in your database
Set qdf = dbs.CreateQueryDef(sTempQueryName, sSQL_Grouped)
 
End Sub

 

[Kreszch68]

You can add the call in the last line of the sub that creates the temp table

        dbs.Execute sSQL
    .MoveNext
    Loop
End With
[COLOR=red][B]CreateDynamicQuery
[/B][/COLOR]End Sub

This way the query always lines up with the table.

 

[koolwaters]

One little issue. My Employee IDs have leading zeroes so I changed the Data Type to text in the line of code below. However, when the table is created, the EmployeID field is a text field but the leading zeroes are no longer there.

sYearFlds = "ApplicationID Long, EmployeeID Text, StartDate DateTime, EndDate DateTime, AmountApproved Currency, " & sYearFlds