Building a record ID

[dcc15 via AccessMonster.com]

With much help and a lot of head scratching (learning), I have the following
code (working and tested) and to get this project completed (crunch time) I
need to add a text string (fixed) so the stored (as text) value will look
like : (TEXT) (YEAR) (MONTH) (NUMBER)= DMR-200711001, am hoping the prefix
(static text) can be added to the code somewhere (simple).
Thanks to all that have help in getting to this point.

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = CDbl(Format(Date, "yyyymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strYrMo & "001"
Else
Me.dmrincrnum = Left(varResult, 6) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If

End Sub

 

[Daniel]

I hope I understood your request properly, but try something like


Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant
Dim strPref As String

strPref = "Whatever text Prefix You Wish To Use"
strYrMo = CDbl(Format(Date, "yyyymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strPref & strYrMo & "001"
Else
Me.dmrincrnum = strPref & Left(varResult, 6) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If

End Sub

 

[John Spencer]

I think there might need to be a bit more modification

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant
Dim strPref As String

strPref = "Whatever text Prefix You Wish To Use"
strYrMo = CDbl(Format(Date, "yyyymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strPref & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strPref & strYrMo & "001"
Else
Me.dmrincrnum = strPref & Left(varResult, 6 + Len(StrPref)) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If

End Sub

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I hope I understood your request properly, but try something like


Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant
Dim strPref As String

strPref = "Whatever text Prefix You Wish To Use"
strYrMo = CDbl(Format(Date, "yyyymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strPref & strYrMo & "001"
Else
Me.dmrincrnum = strPref & Left(varResult, 6) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If

End Sub

--
Hope this helps,

Daniel P

With much help and a lot of head scratching (learning), I have the
following
code (working and tested) and to get this project completed (crunch time)
I
need to add a text string (fixed) so the stored (as text) value will look
like : (TEXT) (YEAR) (MONTH) (NUMBER)= DMR-200711001, am hoping the
prefix
(static text) can be added to the code somewhere (simple).
Thanks to all that have help in getting to this point.

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = CDbl(Format(Date, "yyyymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strYrMo & "001"
Else
Me.dmrincrnum = Left(varResult, 6) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If

End Sub

 

[John W. Vinson]

I think there might need to be a bit more modification

and yet a bit more. Using a Double for the (integer!!!) number string is
unwise; you'll run into roundoff error at some point, and it's less efficient.
Try just leaving strYrMo as a string without going through any sort of number
conversion:

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant
Dim strPref As String

strPref = "Whatever text Prefix You Wish To Use"
strYrMo = Format(Date, "yyyymm")
strWhere = "CStr([dmrincrnum]) Like """ & strPref & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strPref & strYrMo & "001"
Else
Me.dmrincrnum = strPref & Left(varResult, 6 + Len(StrPref)) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If

End Sub


John W. Vinson [MVP]

 

[dcc15 via AccessMonster.com]

Thanks
Had to make 1 change because on second record on it returned 2 prefixes
Me.dmrincrnum = strPref & Left(varResult, 6 + Len(StrPref)) & _
Changed to
Me.dmrincrnum = Left(varResult, 6 + Len(StrPref)) & _

Tested: 20 entries with 4 date {Now()} changes, seems to work fine.

This (your) method/code (for some reason) looks better to me.
I wish I knew enough to know why this method is better. I am getting better
at seeing/understanding what the code is doing, but still need to learn
structure requirements and how expressions interact with each other (still
reading/learning).
Could you break it down (explain) for me, especially the quotes and
quote/asterisk portion.
strWhere = "CStr([dmrnum]) Like """ & strPref & strYrMo & "*"""
Thanks again (to all), I can now start building my new DB!

I think there might need to be a bit more modification

and yet a bit more. Using a Double for the (integer!!!) number string is
unwise; you'll run into roundoff error at some point, and it's less efficient.
Try just leaving strYrMo as a string without going through any sort of number
conversion:

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant
Dim strPref As String

strPref = "Whatever text Prefix You Wish To Use"
strYrMo = Format(Date, "yyyymm")
strWhere = "CStr([dmrincrnum]) Like """ & strPref & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strPref & strYrMo & "001"
Else
Me.dmrincrnum = strPref & Left(varResult, 6 + Len(StrPref)) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If

End Sub

John W. Vinson [MVP]

 

[John W. Vinson]

Could you break it down (explain) for me, especially the quotes and
quote/asterisk portion.
strWhere = "CStr([dmrnum]) Like """ & strPref & strYrMo & "*"""
Thanks again (to all), I can now start building my new DB!

Well, since dmrnum is already a String, I would suggest that the
CStr([dmrnum]) could be replaced by just [dmrnum] - if dmrnum were a Number
field, CStr() would convert it to a string.

The numerous quotemarks are to deal with the fact that in order to generate
the syntactically required quotemark delimiter within a string defined by
quotemarks, you must use two consecutive quotemarks. What you want in strWhere
is an expression like

[dmrnum] LIKE "DMR-200711*"

To build this up, you use "" within the string constants wherever you want a "
in the result; you'll concatenate three pieces:

"[dmrnum] LIKE """

takes the third from last and next to last quotemarks as a pair to represent
one quotemark, generating

[dmrnum] LIKE "

Then you concatenate the string DMR- (from the variable strPref) and the
string 200711 (from the variable strYrMo); finally you'll add the string

"*"""

which - again using the doubletalk double doublequote - evaluates to

*"

Concatenating all these pieces together with the & oprator gives the final
desired result.

John W. Vinson [MVP]

 

[dcc15 via AccessMonster.com]

Thank you
CStr= Covert data type (string)
“â€â€= insert/include “ as a delimiter
"*""= still not sure about this, I assume this is referring to the “sequence
number†portion of the string (001), is/does the asterisk refer to “any other
portion of the string�

I dropped the CStr() and everything seems to work fine.
Thanks for the clean-up and explanation, I’m sure I would get to this in my
reading but there is no better teacher (for me) than seeing/ using in
practical application.

Thanks again for your help

Could you break it down (explain) for me, especially the quotes and
quote/asterisk portion.
strWhere = "CStr([dmrnum]) Like """ & strPref & strYrMo & "*"""
Thanks again (to all), I can now start building my new DB!

Well, since dmrnum is already a String, I would suggest that the
CStr([dmrnum]) could be replaced by just [dmrnum] - if dmrnum were a Number
field, CStr() would convert it to a string.

The numerous quotemarks are to deal with the fact that in order to generate
the syntactically required quotemark delimiter within a string defined by
quotemarks, you must use two consecutive quotemarks. What you want in strWhere
is an expression like

[dmrnum] LIKE "DMR-200711*"

To build this up, you use "" within the string constants wherever you want a "
in the result; you'll concatenate three pieces:

"[dmrnum] LIKE """

takes the third from last and next to last quotemarks as a pair to represent
one quotemark, generating

[dmrnum] LIKE "

Then you concatenate the string DMR- (from the variable strPref) and the
string 200711 (from the variable strYrMo); finally you'll add the string

"*"""

which - again using the doubletalk double doublequote - evaluates to

*"

Concatenating all these pieces together with the & oprator gives the final
desired result.

John W. Vinson [MVP]

 

[John W. Vinson]

Thank you
CStr= Covert data type (string)
“��= insert/include “ as a delimiter
"*""= still not sure about this, I assume this is referring to the “sequence
number� portion of the string (001), is/does the asterisk refer to “any other
portion of the string�?

Are you using Word or something to post? I think you're getting smart quotes
which my newsreader is displaying as cryptic special characters... makes it
hard to read!

The asterisk in a LIKE query is a wildcard matching any string. The intention
of the code is to find the maximum existing value of the sequence number for
the year/month, so the query is searching for all records starting with the
given prefix, year and month - using the wildcard to find all sequence
numbers.

John W. Vinson [MVP]

 

[dcc15 via AccessMonster.com]

Sorry, was using word to spellcheck.

Are you using Word or something to post? I think you're getting smart quotes
which my newsreader is displaying as cryptic special characters... makes it
hard to read!

The asterisk in a LIKE query is a wildcard matching any string. The intention
of the code is to find the maximum existing value of the sequence number for
the year/month, so the query is searching for all records starting with the
given prefix, year and month - using the wildcard to find all sequence
numbers.

John W. Vinson [MVP]