using fiscal date formate "2011/12" filtering a string query will not work

[jaymin]

Hi All,


Hope someone can give me some guidance, i have a form with a combo box that gives me a list of fiscal year end dates formatted like eg 2010/11, 2011/12, 2012/13 etc.
if i change the combo box to accept only year eg 2010, 2011, 2013 etc and run the form it works fine, but if i change it to the fiscal format it does not want to see it.
I get the date from this code in the field list FinDate: ([FinYear]-1) & "/" & Right(([FinYear]+0),2) and the criteria for it is Like [Forms]![frmStaffSelection]![cboYear] & "*" this give me the format and i get a list of fiscal dates as above good.
but when i run the combo box and enter the fiscal years it opens a input box for FinDate asking for input, if I hard code a fiscal date Like "2011/12"& "*" into the criteria it returns nothing although there is data like it in the query.
Hope this make a bit of sence

Peter

[ranman256]

I have 2 boxes for fiscal year.... 1 year, 1 month.
choose 2014 in cboYr
choose 11 in cboMo
THEN let the form calculate start and end dates txtStartDate, txtEndDate.
(the afterupdate of cboyr, or cbomo, would recalc the 2 text boxes according you YOUR FY.

all reports would run off the 2 text boxes for the date range

[jaymin]

Runman256
Thanks for your quick reply, I will try it, will be a couple days before i have it done, i will let you know
Peter

[jaymin]

I believe what was wrong, i was trying to run a calculating field off other calculating fields and it kept asking me to enter data, but when i used a calculating field that used a field from the table it worked.
i used this formula to calculate the fiscal year for the year date - FYear: Year([OTDate])-IIf([OTDate]<DateSerial(Year([OTDate]),6,0),1,0), this gave me the minor year of the fiscal year e.g. 2013 of the fiscal year of 2013/14, the 6 is the month you want for the change over, the 1,0 is for the iif statement this will minus a year if the month is = or less that June = 2013, if it it is greater than 6 = July the minor year would be the same year 2014, and to get the format for the combo box to work when staff selected the 2013/14, i used a function to check what was selected e.g. 2013/14 then had some if / then statements to change the text box value to what i wanted, then pointed the filter criteria on the query to it. work s fine
Regards
Peter