Date Formula

[Aurora]

I am using Access 2000

I created a database with an autonumber field that
consists of the year - dash - 4 digit incremening number.
In the format field I put "03 - "&&&&. This gave me 03 -
2003, 2004 etc.

I thought I could just change the 03 to 04 for the new
year. But this changed everything in the database. I
only want to change the records added for this year. I
tried changing the format field to yy" - "&&&&. To get
the formula to read the current year but this did not
work. What am I doing wrong??????????? Please help me.
I have 2 databases I need to change today.

Aurora

 

[Tim Ferguson]

I created a database with an autonumber field that
consists of the year - dash - 4 digit incremening number.
In the format field I put "03 - "&&&&. This gave me 03 -
2003, 2004 etc.

You are lucky, in that you do have a decently-designed autonumber field
still, containing numbers 1,2,3,6... and so on. The format property only
sticks the static text around it when you see them. This is good -- had you
done this another way you might have ended up with field that looked like
03-001, 03-002, etc; which would have been much harder to deal with.

You need to add an extra column to the table, called RecordYear, and make
it a Long or an Integer. Run an update query to set this field in all the
records to 2003 like this:

UPDATE MyTable
SET RecordYear = 2003;

Now on your form, you can a textbox that connected to RecordYear and put
its DefaultValue to 2004.

If you want the counter to reset to 1 for each new year, then you'll need a
little bit of VBA code rather than an autonumber ("If you care what value
an Autonumber has, then you probably should not be using an Autonumber").
Try googling on "ACCESS Custom Autonumber" or look at the Access Web
<http://www.mvps.org/access>.

Hope that helps


Tim F

 

[Aurora]

Thank you for answering my question.

I am not sure this will get me what I want. I need a
field that contains both the year and the MDO number
together. Ex: 04-2345, 04-2346 etc.

The dept using this database want to keep track of this
number the same way they did before Access, to be able to
print the MDO form with the whole number together and to
search for the data by the whole number.

I thought I read somewhere, that the year could be put
infront of an incremating number to change with each year.
Do you have any other suggestions? I do not know anything
about VBA codes.

Aurora

 

[John Vinson]

I am using Access 2000

I created a database with an autonumber field that
consists of the year - dash - 4 digit incremening number.
In the format field I put "03 - "&&&&. This gave me 03 -
2003, 2004 etc.

This isn't an autonumber - this is a "custom counter intelligent key".
It's generally considered bad design, since you're storing data - a
year - redundantly in this field.

I thought I could just change the 03 to 04 for the new
year. But this changed everything in the database.

Well, yes; you're setting the Format property for the field itself.
The value in the field is one thing; the Format property merely
controls how the value in the field is displayed. In short the 03- or
04- is NOT PART OF THE FIELD; it's added when the field is displayed.

I only want to change the records added for this year. I
tried changing the format field to yy" - "&&&&. To get
the formula to read the current year but this did not
work. What am I doing wrong??????????? Please help me.
I have 2 databases I need to change today.

If you *must* have this field, I'd suggest storing two Integer fields
- a year number (4 for 2004, 12 for 2012 and so on) in one field, and
your sequential (not autonumber, manually assigned) value in a
separate field. These two fields can be concatenated for display
purposes with an expression like

Format(YearNo, "00") & Format(SeqNo, "-0000")

 

[John Vinson]

I need a
field that contains both the year and the MDO number
together. Ex: 04-2345, 04-2346 etc.

No. You do not.

You're confusing data STORAGE with data DISPLAY.

You can create the concatenated MDO number in a Query as a calculated
field, and use it in any way that you would use a table field (sort by
it, search for values, export, display in a form or report, etc.)

 

[Tim Ferguson]

I thought I read somewhere, that the year could be put
infront of an incremating number to change with each year.
Do you have any other suggestions? I do not know anything
about VBA codes.

Nothing to do with VBA coding. As John says, it is very easy to display the
two fields together as yy-aaa on forms or reports, and for export as
queries. Stuffing the whole thing into one field is a Bad Idea and does
create all sorts of problems down the road.

Hope that helps


Tim F

 

[Aurora]

John:

THANK YOU for your suggestion. It worked just fine.
While I have used concatenated formulas before
in "reports", I did not know I could use them in a query.
Again, thank you very much.

Aurora