Set Current Period based on todays date

[Marc_wrates]

I've been asked to updated our companys VAT database and one of the requirements is to auto enter the current vat period.

The vat period ID ranges 1-8

eg period 3 runs 1jul2009 - 30sep2009

I want the currentVatPeriod to be set to 3 as todays date is during this date range the problem is that I am really struggling to get it to work.

Logically I have came up with the following Calculation

currentVatPeriod = VatPeriod::VatPeriod

WHERE (get(currentDate) ≥ VatPeriod::Start AND get(currentDate) ≤ VatPeriod::End)

any help would be greatly appreciated

Marc

[comment]

Can you explain what these VAT periods mean, and where do you want to "auto enter the current vat period"?

[Marc_wrates]

We keep VAT information for 2 years, this 2 year period is broken up into 8 seperate periods.

The requirement for "auto entered current vat period" came from the end user, I was going to store it in a calculation field in a seperate table. I would then use this table to autocomplete a field that the user would see.

If I am going the wrong way about this please advise.

Marc.

[comment]

If I am going the wrong way about this please advise.

It seems to me to be the case, but I still don't understand what this is about. Usually, "VAT information" means (a) the VAT rates in force from time to time, and ( the VAT rate and amount that was actually applied to a transaction.

[Marc_wrates]

the vat amount for each transaction i have set to autocomplete in a field which is used when calculating vat amounts.

The VAT Period is used for reports we run at certain times of the year to send off to Customs and Excise.

eg.

Any invoices we pay during the period 1jul09 - 30sep09 are included as VAT Period 3.

but rather than having to set the vat period for each invoice Its required that it is entered automatically.

ie if i have an invoice for smallCompany dated 20 July 2009 it will complete the vat period as 3 for me.

[comment]

Oh, I see - you are talking about a reporting period.

If the reporting period is given by the transaction's date, then recording a period number is rather redundant. It seems you are reporting quarterly, so any transaction falling within the reported quarter can be associated with a report simply by its date.

However, if you want, you could calculate the period number as =

Ceiling ( Month ( TxDate ) / 3 ) + 4 * ( Year ( TxDate ) - 2009 )

This will calculate the serial number of the quarter, starting with Q1 2009.

[Marc_wrates]

Thanks for the help comment,

[tyt3]

Not sure what the format is on the date... reading through I got the impression it is a text field in the format ddmmmyy, i.e. 1jul09, 15mar09

If this is true... Maybe you could try...

Let(_my=(Right(myDate,5));

Case(_my="jan09";1; _my="feb09";1; _my="mar09";1;

_my="apr09";2; _my="may09";2; _my="jun09";2;

_my="jul09";3; _my="aug09";3; _my="sep09";3;

_my="oct09";4; _my="nov09";4; _my="dec09";4;

_my="jan10";5; _my="feb10";5; _my="mar10";5;

_my="apr10";6; _my="may10";6; _my="jun10";6;

_my="jul10";7; _my="aug10";7; _my="sep10";7;

_my="oct10";8; _my="nov10";8; _my="dec10";8;

)

)

I am sure this could be done much more elegantly, but I tested it out and it worked for me.

If it is a regular date field, comment's post works perfectly.

Edited July 20, 2009 by Guest
Not sure on field format