Formula to populate field??

[Qrackerman]

Is there a formula/module/macro that would be used to populate a field based on the date entered and primary key? Date entered is just that - date entered and primary key is autonumber. Or would it be a new query of some sort??

 

[dcx693]

You can use an update query if you need to store the data in a table. Set the Update To expression to something like:
=Format([Date_Entered],"yyyymmdd") & [Auto_Num]

Most users would discourage you from storing a calculated field in your data table, though, since the value you want can always be calculated for on-screen display.

 

[Qrackerman]

Thanks for the reply dcx, The database was started in 2003. the PK is autonumber and is around 1200 records. I want to keep going from 1200 in the PK but create a field that will start back at zero if data is entered in 2004 and again at zero in 2005?? So the first record in the year 2004 would be 1 and so on.

 

[dcx693]

You can extract the year from a date using the Year() formula. For example:
=Year([Date_Entered])

If you want to start numbering records based on the year they were entered, you can use a DMax() formula to find the last entered record for that year. For example, something like:
=DMax("[your_new_field]","your_table_name","Year([Date_Entered])=" & [your_date_field])
Once you find the max, just add 1 to it to get a "next" number.

The specific formula and the field names depend on your own object, of course.

 

[Qrackerman]

Great thanks, My confusion is where would this formula be placed, in an update query??

 

[dcx693]

In the "Update To" line of the update query. Read up on update queries in the Access on line help. They're designed pretty much like regular "select" queries, and changed to an update query by going to the Query menu and choosing Update.

 

[Qrackerman]

Thanks, I will give it a try

 

[Qrackerman]

Sorry, I am confused about: "Year([Date_Entered])= portion of that formula. What does it do??

 

[dcx693]

In that DMax formula, the third criteria of the function is where you specify critieria. If your aim is to find the largest number assigned to a record having a particular year as the start of the number, then you need to somehow specify that you want the DMax function to limit it's search to that year. You need to delimit the literal portions (the pieces that are being passed directly) of the DMax formula in quote marks. The piece that is variable should be outside of the quotes.

 

[Qrackerman]

Great, I got that part to work but when I do the update query, it updates the new field on every record with the same number. How do I have it check the number in the last record and update it by 1 in the new record each time??

 

[dcx693]

Perhaps an update query was the wrong way to go. Well, you do have the expression needed to calculate the next desired value.

Where do you typically enter in new records? Hopefully in a form. You can easily use that DMax expression in a bound form field (a bound field is one that has a table/query field as its controlsource - meaning it's directly linked and stored to/in the table/query).

 

[Qrackerman]

A new record is entered via an ASP form posting to the database

 

[Qrackerman]

Here is an example of what I am trying to accomplish, not really sure if I am explaining it correctly

ID Date Number
0001 01/01/03 0001
0002 01/01/03 0002
0003 01/01/03 0003.....
1200 12/01/03 1200
1201 01/01/04 0001
1202 01/01/04 0002

Could this be done in the original table or would I need to do this in a query?