Apr 11 2005, 12:21 PM
How can I prevent this from happening? I need it to show "050411001", but it's giving me "50411001". Here is the code.
Public Function NextRecId() As Long
Dim strNextNum As Long
Dim iNextRec As Long
Dim NextRecordId As Long
iNextRec = Nz(DMax("[TestID]", "tblTest"), 0)
NextRecordId = Format(Date, "yymmdd") & "001"
If iNextRec >= NextRecordId Then
NextRecId = iNextRec + 1
NextRecId = NextRecordId
Apr 11 2005, 12:23 PM
Apr 11 2005, 12:26 PM
In the table's design view
FORMAT THE FIELD 00000000
that will result in 050411001 being displayed correctly
Apr 11 2005, 12:31 PM
Your RecordID in your table should be an Autonumber instead of this concatenated value.
You can use this value for user indentifcation if you like .. but the record indentifier should be an Autonumber.
Also .. this concatenated value should be Text (string value) instead of a numeric datatype ...
Record indentifiers should have no meaning to you or your users ..
It is for the exclusive use of Access ...
Apr 11 2005, 12:37 PM
Tried both of those Idea's, but they did not produce the leading zero.
Apr 11 2005, 12:44 PM
This record ID is the format that the user has been using to track orders. This record ID is used by their customers and other places. So what your saying is use a the Access autonumber for it's purpose, to track the access records, and make a seperate field for this record? That's not going to solve my leading zero problem.
Apr 11 2005, 12:47 PM
Sure it will ... and if you change this concatenated value to a Text datatype instead of Numeric datatype that you are currently using .. as I replied in my previous post ...
A numeric datatype will drop any leading zeros ...
Apr 11 2005, 12:56 PM
Ok, I need to clearify, I am using the code above with the input mask =" \N000000\-000;;_" in both the table and form field. The table datatype is Text and has been the whole time. The number I'm trying to create needs to match the format that the customer specified which is Nyymmdd-###, I've tried to do this many ways and every way cuts off the leading zero. The code above working with the input mask got the incrementing to work correctly, but just left out the leading zero. Thats all I have to solve for right now.
Apr 11 2005, 01:01 PM
In your code that you posted .. you are creating a numeric result .. thus the loss of the leading zero.
And again ... if this used as the Primary Key value of your table ... this is wrong.
The PK should be an Autonumber and not this concatenate value ....
Apr 11 2005, 03:46 PM
Here is the answer, it increments and gives the correct format.
Public Function NextRecId() As String
Dim strNDate As String
Dim intADash As Integer
Dim strCurRec As String
Dim intRightCut As Integer
Dim strNewRec As String
strCurRec = Nz(DMax("[TestId]", "tblOrderID"), 0)
intRightCut = CInt(Right(strCurRec, 3))
strNDate = "N" & Format(Date, "yymmdd") & "-"
intADash = "001"
strNewRec = strNDate & Format(intADash, "000")
If intRightCut >= intADash Then
NextRecId = strNDate & Format(intRightCut + 1, "000")
NextRecId = strNewRec
Apr 11 2005, 04:04 PM
I wasn't getting it, and then it hit me when I was trying to tweek things in my code. Hicks you said it but I didn't understand it, I was trying to add a integer to a string and I didn't realize that in order to get a leading zero it had to be a string. The glitch is that Microsoft should allow leading zero's in integer or long integer strings then I would be ok, or I'll just accept the fact that they don't and work around it. Thanks for the help.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here