Date within range

[theKruser]

I am trying to include a statment in my query in which a date stored in a table is compared to a start and end date that will update itself (i.e. fiscal year dates). I will eventually embed it into an Iif statment to return a value if the selected date is either within or not within the given date range.

For example:
If [date] is within this fiscal year, then return blank, if not return EXPIRED.

The problem lies in defining the fiscal year with automatic update. Since FY08 runs from 1 Oct 07 to 30 Sep 08, the only way I can figure out to compare [date] is by using Between command and manually defining dates. Then every new fiscal year I have to change the query. This would not be a problem to do, but I am trying to make this as user friendly as possible to minimize user programming in the future. Any ideas???

 

[khawar]

Download the attached db

 

[theKruser]

That worked...thank you very much. I don't understand a bit of it, but copy and past with a tweak makes it work. Thank you very much for your help. Any idea how to do the same, but with current semi-annual period (1 Jan-30 Jun and 1 Jul-31 Dec)??

 

[Brianwarnock]

The ifs with dateserial are building the current fiscal year based on the current date by checking where in the year you are, if before 1 oct then the current fiscal year is current year-1 oct 1st to current year sept 30th etc
Dateserial format is Dateserial(year,month,day) returns a date

Brian

 

[theKruser]

That helps immensely. Thank you for your time and help.

 

[khawar]

Uploaded again with query for semiannual periods

 

[theKruser]

You, sir, are an absolute wizard. One day, I hope to be half as good as you. I can tell, you just wrote both of those within minutes of my request for help. Thank you for all of your help. I have been racking my brain for 3 days trying to get it done. I can't tell you how much you have helped.

 

[raskew]

This sample query, based on Northwind's Orders table, will return all records based on the operator-prompted FY Start Date.

SELECT
    [COLOR="Red"]Orders.OrderID[/COLOR]
  , [COLOR="red"]Orders.CustomerID[/COLOR]
  , [COLOR="red"]Orders.OrderDate[/COLOR]
  , Year([enter fystartdate mm/dd/yy]) & "/" & Year([enter fystartdate mm/dd/yy])+1 AS FiscalYear
FROM
   [COLOR="red"]Orders[/COLOR]
WHERE
   ((([COLOR="red"]Orders.OrderDate[/COLOR]) Between [enter fystartdate mm/dd/yy] 
AND
   DateAdd("yyyy",1,[enter fystartdate mm/dd/yy])-1))
ORDER BY
   [COLOR="red"]Orders.OrderDate[/COLOR];

Just copy/paste to a new query in an application that includes Northwind's Orders table, then run the query -- or --

Copy/paste to a new query then modify the query to reflect your table/field names.

HTH - Bob

 

[khawar]

Explanation of the code as asked by you

IIf(Month(Date())>=10

check if month is greater than or equal to october it means that the next financial year

has been started other wise you are in previous year

IIf([Tdate] Between DateSerial(Year(Date()),10,1) And DateSerial(Year(Date())+1,9,30),"","Expired")

This condition is applied when the previous condition is true i-e month is october or greater Date serial builds the date by getting year, month and date so range starts from 1st october of the month and ends on September next year and if the date is falling between above range its open else its expired


IIf([Tdate] Between DateSerial(Year(Date())-1,10,1) And DateSerial(Year(Date()),9,30),"","Expired"))

This condition is checked when the month is less than October again date serial builds the year but this time the financial year has been started in previous calender year so you can see year(date())-1 in the above statement which gives you the date of previous year again if the date is falling between above range its open else its expired

 

[theKruser]

Now I understand. Thank you for taking the time.