Hairline receding rapidly - Help

[dshambrook]

somehow, I have been roped in to assisting in creating a sponsorship database for orphaned children in third world countries.

I'm lost and over my head trying to create a tax receipt scenario.

I need to be able to generate a tax receipt by year for all the sponsors.
I was doing good up until this next part.
They want a unique (not autonumber) tax receipt number appended to each receipt and stored in the event they need to create a duplicate for someone who did not receive or lost their original.

I need a way that they can enter the first receipt number and it will increment from that point on. They also need to be able to change the starting number at the begining of a new year.

ie. all receipts for 2005 would start with 2005xxx. When 2006 receipt time comes they would like to change the receipt numbers so they start with 2006xxx etc. All previous receipts must be retained.

I'm so lost..does anyone have a compass?

Thanks

Dave

 

[WayneRyan]

Dave,

Maintain the number As two fields:

TheYear - Long
TheSequence - Long

Then on your form's BeforeInsert event, put this code.

Me.TheYear = CLng(DatePart("YYYY", Date()))
Me.TheSequence = Nz(DMax("[TheSequence]", "YourTable", "[TheYear] = " & Me.TheYear), 0) + 1
[\code]

That's all the code you need.  When a new year starts, the DMax function returns a Null,
which the NZ function changes to a 0.  Then it just adds +1 to get the next value.

Why two fields?  You will spend a lot of time writing code to continually tear them apart.
It is much easier to sort & group them when they're stored seperately.

It is always easy to format them in a query, etc.

DisplayField: CStr(TheYear) & "-" & Format(TheSequence, "0000")

Wayne

 

[dshambrook]

The db has been uploaded

Thanks for replying Wayne, I really appreciate the assistance.

I've attached the database.

Apparently, it's not the year they want in the receipt number. They would like create a number every year and have all the receipts increment off that number. i.e this year the receipts they will use 50000 so the first receipt will be 50001.

They want to see a form/report that shows a breakdown of sponsoriD, total amount for the given tax receipt year, receipt number.
If everything looks good, they will hit a button and print all the receipts using the receipt report.

As mentioned in previous post, the receipt number, sponsorID and date printed must be recorded in a table for reference.

Thanks in advance for any assistance.