combining a year field and number field

[rickaddison]

Hello,

Since I'm not certain how to search for an answer to my question, I'll ask it. I have a database that uses a field for the year (but I'm using a fiscal year that will end June 30, 2013) and another field as an autonumber. I use the year and autonumber as my reference number (i.e 2013-0001). I'd like to be able to combine these fields to generate the entire number. Also, I'd like for the year to add 1 beginning July 1st of each calendar year, and the autonumber start over at 0001 (i.e. 2014-0001 on July 1, 2013). Is this possible, and if so, how can I do it?

Thanks,

Rick Addison

 

[pr2-eugin]

Hello Rick, Well what you want is quiet similar to the solution put forward by RainLover.. Try out his Sample called DMax+1, I think that is what you actually need..

 

[rickaddison]

Thanks Paul. I'll check it out.

Rick

 

[jdraw]

You might be interested in this info re Fiscal year
http://support.microsoft.com/kb/210249

You can probably find more via Google re Msaccess and Fiscal year

 

[rickaddison]

Paul,

I'm having difficulty knowing how and where to use DMAX plus 1. I have a table with a date field called "MailDate", and a second field called "FiscalYear". I want to be able to calculate the "FiscalYear" using the "MailDate". Beginning with July 1 of each year I want to add "1" to the year in the "MailDate", so that July 1, 2013 will result in a FiscalYear of 2014, and so on. I've tried making "FiscalYear" a calculated field but I don't know if that is the correct approach. Can you give me some help in understanding this? I have to basically play with formulas until I get a desired result.

Thanks,

Rick