dmax function question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
Back
Top