queries and dates

[jemar707311]

I have a table identifying termination dates of a contract. I need to identify which fiscal year the date falls in - (users can't seem to figure this out on their on)....So I have another table listing fiscal years and the corresponding periods.....
how do I query so that field is added to show the fiscal year?

 

[Chris RR]

It'll probably mean doing a Dlookup, but a lot depends on the structure of your fiscal year table. Years and periods alone won't help; do you have starting and ending dates in there?

 

[pbuethe]

This sounds like what I want to do on my reports. Given the report starting date, I want to look up the contract period starting and ending dates that this date falls between and print them on the report. The contract period is not always a year. e.g.

Contract period dates
1998-1999 7/1/98 - 6/30/99
1999-2000 7/1/99 - 3/31/00
2000-2001 4/1/00 - 3/31/01
2001-2002 4/1/01 - 3/31/02
2002-2003 4/1/02 - 9/30/03

If I have these starting and ending dates in a table, how can I accomplish this lookup? Thanks for your help.

 

[Jon K]

>
I have a table identifying termination dates of a contract. I need to identify which fiscal year the date falls in
<

>
If I have these starting and ending dates in a table, how can I accomplish this lookup?
<


You can add the two tables in a query and join them in the Criteria for the contract Termination date field with:-

Between [tblFiscalYears].[StartingDate] And [tblFiscalYears].[EndingDate]
as shown in the attached DB.


(To open the DB in Access 2000 or 2002, just choose Convert and save as a new name when it is opened for the first time.)

 

[realnine]

If I understand you question, your fiscal year is different than the calendar year. If your 2004 fiscal year starts 1 Oct 2003 then you can add 3 months to your calendar date to get a fiscal date. Then use the Year() function to get the fiscal year.

For example, The calendar date is November 1, 2003
Add three months to get February 1, 2004
Get the year 2004 and that is the fiscal year for calendar date of November 1, 2003

The code would look something like this

=Year(DateAdd("m",3,Date()))
Replace the Date() with your fieldname

HTH
RealNine

 

[pbuethe]

OK I did a query and a DLookup and it seems to work. Thanks Jon K!

 

[raskew]

Here's a self-contained method you might experiment with. After copying / pasting it
to a module, call it from the debug window
as shown below. Try with different dates
to see the variety of responses.

Function ConYear(PDate As Date, ParamArray varMyVals() As Variant) As String
'*******************************************
'Name:      ConYear (Function)
'Purpose:   Returns the user-defined FY date range
'           based on date input (PDate)
'In response to:  [url]http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=13051[/url]
'Inputs:  ? conyear(#4/4/02#, #7/1/98#, #6/30/99#, #7/1/99#, #3/31/00#, #4/1/00#, #3/31/01#, #4/1/01#, #3/31/02#,#4/1/02#,#9/30/02)
'Output:  See debug window
'Note:  1998-1999 7/1/98 - 6/30/99
'       1999-2000 7/1/99 - 3/31/00
'       2000-2001 4/1/00 - 3/31/01
'       2001-2002 4/1/01 - 3/31/02
'       2002-2003 4/1/02 - 9/30/03

'*******************************************
Dim idx As Long, i As Integer
i = UBound(varMyVals)
'is input date < lower limit or > upper limit?
If PDate < varMyVals(0) Or PDate > varMyVals(i) Then
   ConYear = "Date out of bounds"
   GoTo Exit_ConYear
Else
   'cycle thru until input date <= upper limit of a date range
   For idx = 1 To UBound(varMyVals()) Step 2
      If PDate <= varMyVals(idx) Then
         ConYear = Year(varMyVals(idx - 1)) & "-" & Year(varMyVals(idx))
         GoTo Exit_ConYear
      End If
   Next idx
End If
      
Exit_ConYear:
    Exit Function
   
End Function