DMAX Function

[Angie]

I am trying to create an unique ID, that is a combination of 2 fields
selected by the user from a form and sequential number unique for each
combination of those 2 fields. Format of that ID is X9-9X-9999. The
first 2 caracters are the Article Nr (alphanumeric) and the next 2 are
the Part of that article (alphanumeric). Ex A7-Y2-0087
The function that I created, is having problem with the DMAX function,
on the criteria part of that function.
Please anybody that can help, will be really appreciated.
I am using Access 2002

Public Function CreateIDs()

Dim strArt As String
Dim strPart As String
Dim strConcat As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strNewPK_Nr As String


'Set database to the current application
Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset("qryFindLast_FileNr")

strLastPK_Nr = rst!MaxNr.Value

With rst
.MoveLast
End With


strArt = Me.ARTICLE.Value 'Data selected by user from combo box
strPart = Me.PART.Value 'Data selected by user from combo box
strConcat = strArt & "-" & strPart & "-"

'Here where the problem is ("[PK_FILE_NUMBER] Like '"*" &
"[strConcat]" & "*"')!!!
strNewPK_Nr = DMax("[pk_file_number]", "[qryFindLast_FileNr]",
"[PK_FILE_NUMBER] Like '"*" & "[strConcat]" & "*"')
strNewPK_Nr = CInt(strNewPK_Nr) + 1

Me.MaxNr = strNewPK_Nr

End Function

Thanks

Angie

[Jon]

In article <366a92d9.0201...@posting.google.com>, imdb...@msn.com
(Angie) writes:

Angie

Firstly, it appears that you don't need to declare the Recordset and Database
objects - they are not being used. Secondly, the double quotes around strConcat
are required - it will make Access concatenate [strConcat] into the criteria,
rather than the value from this string. Finally, it looks like the last single
quote is outside the double quotes. Try this instead:

strNewPK_Nr=DMax("[pk_file_number]","[qryFindLast_FileNr]","[PK_FILE_NUMBER]

Like ' * " & strConcat & " * ' ")

I've left spaces between the single and double quotes in the criteria for
visibility - in practice they wouldn't be needed. You might also be able to
base this DMax directly on the table, rather than a query.

You should also cater for the possibility of the first record being entered
into the table, in which case DMax will return Null, by using the Nz function
(ie Nz(DMax(.....)))

Jon

Access tips & tricks - http://www.applecore99.com
Microsoft Access webring - http://a.webring.com/hub?ring=microsoftaccess