datasource switching

[allanopogi]

hello..
jst wanted to consult you guys about my scene,
im currently using my own simple database for invoices,
now we are switching fiscal years ang i dont want my invoices data
mixed up with the previous year. im thinkin of creating a new table
for the new fiscal year w/ the same structure as the previous, and maybe
i can switch from the previous database or the current if i required to
is datasource switching possible in access forms?
please advice me for possible solutions..
thank you very much.


AllanO

 

[datAdrenaline]

I am assuming you have a date/time "stamp" (Date/Time typed field with a default) on your records correct? ... If NOT, then you need one, and default the field value to Now() ...

With a Date/Time typed field used in this manner, there is NO NEED for more tables ... just filter the date/time typed field to filter, and you can get what ever set of records you want.

 

[allanopogi]

thanks sir..
ill try that..

 

[gemma-the-husky]

i think the date/time stamp is not necessarily going to work - this will indeed tell you when yuo created a record, but often you will find yourself creating records in an accounting period AFTER the true accounting period

BUT

you must have an invoice date in the field anyway - so you can simply find all your invoices by using a query with criteria

[invoicedate] between startdate and enddate 'parameters from forms/variables
[invoicedate] between #01/12/08# and #31/12/08# 'direct entry of dates (UK layout)

 

[DCrake]

Another addition is to create a function that calculates the fiscal year based on any date. Then use this function in a query to filter out all invoices by fiscal year.

Sample function based on 1st April to 31st March

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

In immediate window type in

?FinancialYear(Date())
2009/2010

 

[datAdrenaline]

>> but often you will find yourself creating records in an accounting period AFTER the true accounting period
<<

True indeed ... the point of my post was meant to reveal that the table should have a date of some sort, then the UI should filter on that date accordingly ... basically trying to encourage a single table ... ... definately a good catch/reminder gemma.

With that thought, it does remind me that I have often seen a date stamp field that was non-editable (Date/Time), along with an "transaction date" which has a default of =Date(), but was editable. With the two date fields, one can analyze how quickly data got in the system.