Member Avatar for ITMeister

I have a database here that I'm re-building to make it more up to date for my customer. They need a Quote table, form, report, etc.

Their quote numbers are generated as "2008-000"
2008 being for the year, and 000 goes as the automated number, for each individual quote.

This is a tricky one for me. I'm thinking the best way to set this up would be as two different fields, since they are two different criteria bases.

For "2008" what code, setup, etc could I use to make this transition on it's own come the begining of 2009, 2010, etc? This isn't something I'm familiar with.

for "000" I'd need to input an initial starting value of where they are at, and then it would need to generate the numbers from there. Seem to have problems pulling up an area to insert an initial value.

If anyone has any idea of a briliant or at least workable set up for this, I'd appreciate the input!

~Amanda Baumeister

  • 2 Contributors
  • 4 Replies
  • 141 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by ITMeister
Member Avatar for timothybard

For one of my customers, they needed invoices to have invoice numbers in a very similar format. How I handled it was to have a table called dictionary that kept track of the incrementing value; each time an invoice was generated, it would increase the value by 1 and record it in the current invoice record. As far as the year goes, you can use the Year(Now()) function to return the current year; simply use the function when you are generating the quotes and record the value on the current record.

Member Avatar for ITMeister

Thanks for the info, but still having some trouble.

For one, do you know of any "arguements list" anywhere I could print off and keep in file for just such an emergency? lol

The Year(now()) in my default value field just keeps coming back incorrect. I thought I had it via the expression building but it decided to return a date in 1905.......quite obviously no where near our current year.

Ya if you could just eleborate more on your initial answer that'd be phenominal. lol. I'm a bit of a wiz on Access but once in a while I run into something that just stumps me no matter what I try.

Thank you!

Member Avatar for timothybard

I'm not sure I know what you mean by an "arguments list". Could you explain what you mean.

As far as the Year(now())... I will have to experiment how to use it as a default value for a field.

However, when I mentioned using Year(now()), I meant using vba to create a record and have vba calculate Year(now()) and place it in the record.

Let me know if you have any other issues. From your last post, it seemed as if you might have more questions on your mind, so if you do, just ask and I'll try to answer.

Member Avatar for ITMeister

I was able to solve the year issue by using an integer instead of the date/time function. Worked immediately lol. For some reason the internal calendar on Access has todays date as accurate, but the default month/day/year goes back to 1905. Little strange but apparently a lot of people have this problem.

I'm working on my incrementing values now. If you have suggestions, they are welcome.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.