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.

 

[Kreszch68]

To clarify a little I'll try to post the results.

 

[Kreszch68]

Ok forget last post.

You must not add this line

Const sTempTableName As String = "ttblApplicationsForEMCApproval"

The name for the temptable is passed by the maincaller.

Const sTblNameForeCast As String = "ttblApplicationsForEMCApproval"

The MainCaller calls all other routines. So you really only have to put together the correct variables and constants in there.

 

[koolwaters]

Ok. I recopied the modules and started over and it is now working correctly when I run the Main Caller.

I need to duplicate this effort for something else so I will try it and see if it works correctly.

Thanks again for your assistance and patience.

Cheers!

 

[Kreszch68]

The only thing you need to copy is the maincaller (and give it another name)
From this new instance you can then create a new temptable with a different name and using a different querydef with the desired fields.

 

[koolwaters]

I need this one with the summaries now where I can see the total by Department which we looked at yesterday so it is slightly different to this one.

 

[koolwaters]

Sighs!

I guess it was too good to be true. I tried adding aliases to the fields in the query I that contains the fields from the multiple tables and I get errors when running the Main Caller.

If I delete the aliases I no longer get errors.

 

[koolwaters]

I'll just go back to the tables and change the captions and hope no one wants it change later.

 

[Kreszch68]

I suspect you use spaces in your Aliases.

But no problem

Replace the sub BuiltTableWithQuery by this one

Public Sub BuiltTableWithQuery(ByVal sQueryToLinkName As String, _
                               ByVal sTblNameForeCast As String, _
                               ByVal sLinkFieldOne As String, _
                               ByVal sLinkFieldTwo As String, _
                               ByVal sTempQueryName As String, _
                               ByVal blnSumAmounts As Boolean, _
                               Optional ByVal sSumThisFlds As String = "")
 
'Routine that creates all entities
'1 create the forecast table
'2 create array with fieldnames
'3 create the querydef
Dim sAmtYFlds As String     'String to hold the dynamic fieldnames for comparison
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim iFldCnt As Integer
Dim sFields() As String
Dim sSumFields() As String
Dim sSQL As String
Dim s As String
Dim sSelFlds As String
Dim sGroupFlds As String
Dim sFlds As String
 
'Create the forecast table
CreateTempTable sTblNameForeCast
Set dbs = CurrentDb
'Get the fieldnames from the QueryToLink, ignore the link fields
Set qdf = dbs.QueryDefs(sQueryToLinkName)
iFldCnt = 0
For Each fld In qdf.Fields
 
        'Test if fld = sum field
        If blnSumAmounts And InStr(1, sSumThisFlds, fld.Name, vbTextCompare) <> 0 Then 'It's a sum field
            ReDim Preserve sFields(iFldCnt)
            sFields(iFldCnt) = "Sum([" & fld.Name & "]) as [Total " & fld.Name & "]"
            iFldCnt = iFldCnt + 1
        ElseIf fld.Name = sLinkFieldOne Or fld.Name = sLinkFieldTwo Then
            'Ignore field
        Else
            ReDim Preserve sFields(iFldCnt)
            sFields(iFldCnt) = [B][COLOR=darkred]"[" & fld.Name & "]"[/COLOR][/B]
            If blnSumAmounts Then sGroupFlds = sGroupFlds & ", " & [COLOR=darkred][B]"[" & fld.Name & "]"[/B][/COLOR]
 
            iFldCnt = iFldCnt + 1
        End If
Next fld
Set tdf = dbs.TableDefs(sTblNameForeCast)
'load all amtFlds from function
sAmtYFlds = sAmountYearFields
'test if current fieldname is in the sAmtYFlds string
'extract year amount fieldnames from tempforecast into array
'This is to eliminate all fields other than the dynamic fields
For Each fld In tdf.Fields
        If InStr(1, sAmtYFlds, fld.Name, vbTextCompare) > 0 Then
        ReDim Preserve sFields(iFldCnt)
            If blnSumAmounts Then
                sFields(iFldCnt) = "Sum([" & fld.Name & "]) as [Total " & fld.Name & "]"
                iFldCnt = iFldCnt + 1
            Else
                sFields(iFldCnt) = "[" & fld.Name & "]"
                iFldCnt = iFldCnt + 1
            End If
        End If
Next fld
sSelFlds = Join(sFields, ", ")
sSQL = SQL_JoinTwoTbls(sQueryToLinkName, sTblNameForeCast, sLinkFieldOne, sLinkFieldTwo, sSelFlds, Mid(sGroupFlds, 3))
'test if the query already exists, if so delete
    For Each qdf In dbs.QueryDefs
        If qdf.Name = sTempQueryName Then dbs.QueryDefs.Delete qdf.Name
    Next qdf
 
Set qdf = dbs.CreateQueryDef(sTempQueryName, sSQL)
End Sub

 

[koolwaters]

Thanks!

I do not have spaces in the field names but management stipulates that they want to see spaces so I normally just create aliases in the queries or change the Captions in the tables.

 

[Kreszch68]

So is everything working OK now?