Im working with two fields:

doc_num= format 05-01-1
The first 2 digits represents the year. The second two represent the month.
The last part is just a record number.

doc_rec_num= it contains the record number(the last part of doc_num)
I want the database to search for the maximum doc_rec_num based on the first
two parts of the doc_num. If the max doc_rec_num=0 then increment it by 1. If
the max doc_rec_num=1 then increment it by 1.

For example:

doc_number: 05-01-01

When the user inputs a new record, the database will search for the max
doc_rec_num (01 in this example) and increment it by 1 making the new
doc_number 05-01-02.

I tried this code to find the max number, but i get an "Invalid argument in
a domain aggregate function" error:

Dim x As integer
x = DMax(doc_rec_num, "table x", doc_num = Forms![add new record]!year & "-"
& Forms![add new record]!month & "-" & "*")

Perhaps

x = NZ(DMax("doc_rec_num", "table x", "doc_num Like " & Chr(34) & _
Forms![add new record]!year & "-" & Forms![add new record]!month & "-*"
& Chr(34)) ,0) + 1

"tope12" <(E-Mail Removed)> wrote in message
news:EF844DFF-B2D4-4B7A-ADAD-(E-Mail Removed)...
> Im working with two fields:
>
> doc_num= format 05-01-1
> The first 2 digits represents the year. The second two represent the
> month.
> The last part is just a record number.
>
> doc_rec_num= it contains the record number(the last part of doc_num)
> I want the database to search for the maximum doc_rec_num based on the
> first
> two parts of the doc_num. If the max doc_rec_num=0 then increment it by 1.
> If
> the max doc_rec_num=1 then increment it by 1.
>
> For example:
>
> doc_number: 05-01-01
>
> When the user inputs a new record, the database will search for the max
> doc_rec_num (01 in this example) and increment it by 1 making the new
> doc_number 05-01-02.
>
> I tried this code to find the max number, but i get an "Invalid argument
> in
> a domain aggregate function" error:
>
> Dim x As integer
> x = DMax(doc_rec_num, "table x", doc_num = Forms![add new record]!year &
> "-"
> & Forms![add new record]!month & "-" & "*")

if the format is always 2digits-2digits-recordnumber then you could do
something with the left and right functions.

It could look something like this:
dmax(right("05-01-1", len("05-01-1") -6), "table x", left(doc_num,6) =
forms!x!year & "-" & forms!x!month & "-")

I made a new field and changed the function to:
DMax("[doc_rec_num]", "table x", "[yr_mon]='05-01'")

I set up so that the max number will show up in a text box, but the number
doesn't show up. I made this if statement to display the word "yes" in the
inbox if DMax >0 and it works. I dont know why the number doesn't show up.



"JM" wrote:

> if the format is always 2digits-2digits-recordnumber then you could do
> something with the left and right functions.
>
> It could look something like this:
> dmax(right("05-01-1", len("05-01-1") -6), "table x", left(doc_num,6) =
> forms!x!year & "-" & forms!x!month & "-")
>
>