Financial Year Format

[Tansar]

Hi,
I am creating a field that requires the following type of data:

2005/06
2006/07
2007/08

and so on. What format can I apply to get my dates like this, or do i use the text format? Jus worried if a search is performed

Any advice?

 

[boblarson]

If you have a date field you can display it in a query like:

MyDate:Format([YourDateFieldNameHere],"yyyy/mm")

 

[raskew]

Hi -

2007/08 is definitely a string (text). No way is it going to be misinterpreted as a date or a number.

If you have it stored in a text field, see no reason why you couldn't search on it.

HTH - Bob

Added: I take that back about a number. Performing this from the immediate window:

? 2007/08
250.875

....definitely not what you want. Stick with a string.

Bob Larson:

If you have a date field you can display it in a query like:
MyDate:Format([YourDateFieldNameHere],"yyyy/mm")

True, but not applicable. Based on the OP's original post, 2007/08
doesn't represent Aug 2007 but rather years 2007 & 2008.

 

[DCrake]

Simple Software Solutions

You don't say how you are calculating the financial year. Here is a function that I use based on the year 1st April to 31st March.

You simply pass any date to the function and it will return the correct financial year.

Public Function FinancialYear(AnyDate As Date) As String

Dim fYear As String
If Month(AnyDate) < 4 Then
    FinancialYear = Year(DateAdd("yyyy", -1, AnyDate)) & "/" & Year(AnyDate)
Else
    FinancialYear = Year(AnyDate) & "/" & Year(DateAdd("yyyy", 1, AnyDate))
End If


End Function

Simple but effective.


CodeMaster:

 

[yogesh]

I am preparing a form to create a new Invoice.

In this I want the invoice number field to be reset to 1 at the beginning of a new financial year.

Financial year eg. 2012 - 2013 (Begins on 1st April 2012 and ends on 31st March 2013)

Format for Invoice number 0001/2012-2013, 0002/2012-2013 and so on...

So how do i go about for creating a function in vba to achieve this.

Thanks in advance for your help.