Using a query to generate value for field?

[Guest]

In Access 2000, I have two fields in a database that I want to populate with
year-dependent values.

The first field is the last two digits of the year in which the record was
added, saved as text format. I used Expression Builder to automatically
insert the digits as a two-character string.

I want the second field to be a three-character string of numbers that
automatically increments for each new record. However, this field has to
revert to "001" if the field is the first added since the start of the year.
I think this would require a query, but I'm not sure how to match up a query
with a macro in Access.

Should I do this using a standard Access macro or VBA code? Any help would
be great.

 

[Steve Schapel]

Carstonio,

This is not really a job for a macro. It would be better to simply use
the Default Value property of this field on the form. I would recommend
you make this a Number data type field. You can always format it to
display with the leading zeros whenever you need it to show like this
for your purposes on form or report. The Default Value setting would be
something like this (untested!)...

Nz(DMax("[IncrementalNumber]","YourTable","Format(Year(Date()),'yy')=[YearField]"),0)+1

 

[Guest]

Thanks for the suggestion. Unfortunately, my database doesn't use forms. I
use an MS Jet connection over an intranet to insert and update records with
ASP scripting. And the Default Value property for the fields doesn't allow
references to other fields in Table Design view. Can I use VBA code with the
same expression to automatically generate the field value whenever a new
record is inserted?

Carstonio,

This is not really a job for a macro. It would be better to simply use
the Default Value property of this field on the form. I would recommend
you make this a Number data type field. You can always format it to
display with the leading zeros whenever you need it to show like this
for your purposes on form or report. The Default Value setting would be
something like this (untested!)...

Nz(DMax("[IncrementalNumber]","YourTable","Format(Year(Date()),'yy')=[YearField]"),0)+1

--
Steve Schapel, Microsoft Access MVP

In Access 2000, I have two fields in a database that I want to populate with
year-dependent values.

The first field is the last two digits of the year in which the record was
added, saved as text format. I used Expression Builder to automatically
insert the digits as a two-character string.

I want the second field to be a three-character string of numbers that
automatically increments for each new record. However, this field has to
revert to "001" if the field is the first added since the start of the year.
I think this would require a query, but I'm not sure how to match up a query
with a macro in Access.

Should I do this using a standard Access macro or VBA code? Any help would
be great.

 

[Guest]

I made several unsuccessful attempts to modify the database to produce the
correct value. Eventually I decided to modify the ASP to produce the result I
needed.

The SQL command I used is:

Select Max(IncrementalNumber) from YourTable Where YearField =
Right(Year(Date()),2)

And to generate the number:

If Recordset.EOF and RecordSet.BOF Then
Response.Write("1")
Else
Response.Write(Recordset.Fields.Item("Expr1000").Value + 1)

"Expr1000" is the calculated field that has the results of the SQL query.

The pages that display the database records have a function that converts
IncrementalNumber to a string and adds leading zeros.

I wouldn't have been able to do this without the formula you provided.
Thanks for your help.

Carstonio,

This is not really a job for a macro. It would be better to simply use
the Default Value property of this field on the form. I would recommend
you make this a Number data type field. You can always format it to
display with the leading zeros whenever you need it to show like this
for your purposes on form or report. The Default Value setting would be
something like this (untested!)...

Nz(DMax("[IncrementalNumber]","YourTable","Format(Year(Date()),'yy')=[YearField]"),0)+1

--
Steve Schapel, Microsoft Access MVP

In Access 2000, I have two fields in a database that I want to populate with
year-dependent values.

The first field is the last two digits of the year in which the record was
added, saved as text format. I used Expression Builder to automatically
insert the digits as a two-character string.

I want the second field to be a three-character string of numbers that
automatically increments for each new record. However, this field has to
revert to "001" if the field is the first added since the start of the year.
I think this would require a query, but I'm not sure how to match up a query
with a macro in Access.

Should I do this using a standard Access macro or VBA code? Any help would
be great.