Solved Mod Modification

[Gr3g0ry]

Public Function MaxCID() As Long
MaxCID = Nz(DMax("CustomerID", "CUSTOMERDETAILS"), 0) + 1
End Function

this mod returns the next available integer CustomerID

is there a way to modify this existing mod to SPLIT & INCREMENT a STRING Id, P100, to return P101 ??

 

[theDBguy]

Hi. You may not have to. Have you tried to see if it works as is?

 

[plog]

You need store your data in the manner you want to use it. That means since you want to treat CustomerID as a number, you store it as such.

If the 'P' is signficant and different among customers (i.e. it represents a customer type) then you should be storing it in its own field, not jammed into another. If it is not signficant and attached to every CustomerID, then you should concatenate (https://www.techonthenet.com/access/functions/string/concat.php) it onto CustomerID in a query and then reference that field in the query when you need to display it.

Futher, your function will fail (produce invalid results) if CustomerID is a string: P5 > P101

 

[Gr3g0ry]

You need store your data in the manner you want to use it. That means since you want to treat CustomerID as a number, you store it as such.

If the 'P' is signficant and different among customers (i.e. it represents a customer type) then you should be storing it in its own field, not jammed into another. If it is not signficant and attached to every CustomerID, then you should concatenate (https://www.techonthenet.com/access/functions/string/concat.php) it onto CustomerID in a query and then reference that field in the query when you need to display it.

Futher, your function will fail (produce invalid results) if CustomerID is a string: P5 > P101

i understand what you are saying. is there any way to split the CustomerID ? like in java u can split a string , can tht be done ?? additionally using DMax may be a problem since it retuns the max integer.. im just trying to increase knowledge and try to solve this problem.

 

[Gr3g0ry]

Hi. You may not have to. Have you tried to see if it works as is?

ive tried, the value i keep getting is 0

 

[plog]

i understand what you are saying. is there any way to split the CustomerID ?

To get a substring you use Mid:
https://www.techonthenet.com/access/functions/string/mid.php

To find the position of something inside a string you use InStr:

MS Access: InStr Function

This MSAccess tutorial explains how to use the Access Instr function with syntax and examples. The Microsoft Access Instr function returns the position of the first occurrence of a string in another string.

www.techonthenet.com

If you are going to use those functions I suggest you do it and permanently seperate your CustomerID like I mentioned in my first post.

 

[CJ_London]

@DBG - Have you tried to see if it works as is?

the whole would be trying to add a number to a string - but accept is will return P100. You just need to split it to add 1, then put it all back together again.

 

[theDBguy]

@DBG - Have you tried to see if it works as is?

the whole would be trying to add a number to a string - but accept is will return P100. You just need to split it to add 1, then put it all back together again.

Hi @CJ_London. I guess you're right. I stand corrected. Thanks!

 

[Gr3g0ry]

To get a substring you use Mid:
https://www.techonthenet.com/access/functions/string/mid.php

To find the position of something inside a string you use InStr:

MS Access: InStr Function

This MSAccess tutorial explains how to use the Access Instr function with syntax and examples. The Microsoft Access Instr function returns the position of the first occurrence of a string in another string.

www.techonthenet.com

If you are going to use those functions I suggest you do it and permanently seperate your CustomerID like I mentioned in my first post.

hey. took what u said and worked with it. this is the result.

the error is this:

is there any way to add an integer to a string and store as a new string ?? any suggestions ?? how do you typecast a variable in vba ??

 

[plog]

One last try to get you to get you to do it the correct way, then some help with this poorly chosen path.

Your ID has 2 discrete parts. In a database discrete data needs to be stored discretely. Each "piece" of data needs to go into its own field as its correct type. You do that and there's no extraction, no conversion, and no reassembly. When you want those 2 pieces of data to appear together you merge them like you are trying in this function. Do the assembly of these 2 pieces of data in a query or whenever needed.

The string concatenation operator is &

MaxOID = "org" & newOrg

If that doesn't work, do a Debug.Print and see exactly whats in newOrg.

That's my last shot. Good luck.

 

[theDBguy]

Public Function MaxCID() As Long
MaxCID = Nz(DMax("CustomerID", "CUSTOMERDETAILS"), 0) + 1
End Function

this mod returns the next available integer CustomerID

is there a way to modify this existing mod to SPLIT & INCREMENT a STRING Id, P100, to return P101 ??

Hi. If you're still stuck with this, can you please tell us more about the "string" you're trying to split? Is it always a "P" and is it always just one letter? However, I agree with @plog. You should split the letters and numbers in your table.

 

[Gr3g0ry]

Hi. If you're still stuck with this, can you please tell us more about the "string" you're trying to split? Is it always a "P" and is it always just one letter? However, I agree with @plog. You should split the letters and numbers in your table.

Public Function MaxOID() As String
Dim CUS As String
Dim newCus As Integer

CUS = DMax("CustomerID", "CustomerInformationTable")

newCus = Mid(CUS, 5) + 1

If newCus < 10 Then
MaxOID = "CUS-00" & newCus

ElseIf newCus > 10 And newCus < 100 Then
MaxOID = "CUS-0" & newCus

Else
MaxOID = "CUS-" & newCus
End If


i fixed it bro ... thanks. so the ID is generally CUS-###, the first in the series being 001 so i wanted to increment the numeric part while maintaining the prefix CUS-.

 

[theDBguy]

Public Function MaxOID() As String
Dim CUS As String
Dim newCus As Integer

CUS = DMax("CustomerID", "CustomerInformationTable")

newCus = Mid(CUS, 5) + 1

If newCus < 10 Then
MaxOID = "CUS-00" & newCus

ElseIf newCus > 10 And newCus < 100 Then
MaxOID = "CUS-0" & newCus

Else
MaxOID = "CUS-" & newCus
End If


i fixed it bro ... thanks. so the ID is generally CUS-###, the first in the series being 001 so i wanted to increment the numeric part while maintaining the prefix CUS-.

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[Gr3g0ry]

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

Public Function MaxRID() As String
Dim rental As String
Dim newR As Integer

rental = DMax("RentalId", "Rentals") 'R-001
'MsgBox (rental)

newR = Mid(rental, 3) + 1

If newR < 10 Then
MaxRID = "R-00" & newR

ElseIf newR == 10
MaxRID = "R-0" & newR

ElseIf newR > 10 And newR < 100 Then
MaxRID = "R-0" & newR

Else
MaxRID = "R-" & newR

End If

End Function

@theDBguy the bold and underlined section. how do i compare values ? im trying to see id the new ID num is equal to 10 ... ???

 

[arnelgp]

you can make your function compact and without using Comparison:

Public Function MaxRID() As String
    Dim rental As String
    rental = DMax("RentalId", "Rentals") & "" 'R-001
    'MsgBox (rental)
    rental = Replace(rental, "R-", "")
    MaxRID = "R-" & Format(Val(rental) + 1, "000")
End Function

 

[theDBguy]

Public Function MaxRID() As String
Dim rental As String
Dim newR As Integer

rental = DMax("RentalId", "Rentals") 'R-001
'MsgBox (rental)

newR = Mid(rental, 3) + 1

If newR < 10 Then
MaxRID = "R-00" & newR

ElseIf newR == 10
MaxRID = "R-0" & newR

ElseIf newR > 10 And newR < 100 Then
MaxRID = "R-0" & newR

Else
MaxRID = "R-" & newR

End If

End Function

@theDBguy the bold and underlined section. how do i compare values ? im trying to see id the new ID num is equal to 10 ... ???

Hi. I was going to suggest something like:

Public Function MaxRID() As String

MaxRID="R-" & Format(DMax("Val(Mid(Nz(RentalID,0),3))","Rentals")+1,"000")

End Function

 

[Gr3g0ry]

Hi. I was going to suggest something like:

Public Function MaxRID() As String

MaxRID="R-" & Format(DMax("Val(Mid(Nz(RentalID,0),3))","Rentals")+1,"000")

End Function

Can u explain to me what this does please ?

 

[Gr3g0ry]

Public Function MaxRID() As String
Dim rental As String
Dim newR As Integer

rental = DMax("RentalId", "Rentals") 'R-001
'MsgBox (rental)

newR = Mid(rental, 3) + 1

If newR < 10 Then
MaxRID = "R-00" & newR


ElseIf newR >= 10 And newR < 100 Then
MaxRID = "R-0" & newR

Else
MaxRID = "R-" & newR

End If

End Function

THis is how i resolved the issue.
Last night i went to bed and left my PC up n running. i got up just now and this same module that was working perfectly last night is now giving errors. the error is as follows ....

any i deas why im gettin this particular error ??

 

[Gr3g0ry]

No worries guys, i found out the problem. i had copied the module, and given it another name but did not change the name of the function in the module .... no worries

 

[theDBguy]

Can u explain to me what this does please ?

Hi. Glad to hear you got it working. No use in explaining the code I posted, I think, if it didn't work or you're not using it anyway. It would have been nice to know, though, if it worked or not. You didn't try it at all?