adding by increments to a string

[Carmen]

I have written this function to automatically "calculate" a field based on two other fields. Therefore, CalcSecID is a string. This code works perfectly.....


Option Compare Database

Dim yr
Dim sem
Dim ModID
Dim session


Function CalcSecID()
'capture the year of session 1 date as yr and label the fall and _
spring semesters according to the month of session 1. Combine _
yr, sem, and module id to create the section ID field.

ModID = Forms!frmClasses!ModuleID
session = Forms!frmClasses!Session1
yr = Year(session)

If (DatePart("m", [session]) <= 6) Then
sem = "SP"
ElseIf (DatePart("m", [session]) >= 7) Then
sem = "FA"
End If

CalcSecID = yr & sem & "M" & ModID & "-" & 1
Forms!frmClasses!Text28 = CalcSecID


End Function

So you can see that my field ends up with the format "2001FAM3-1" (for example). Now I need to increase this number by one for each record. So the next record would be "2001FAM3-2", etc... I have created the following For Each...Next Loop that I would call in the function:

Private Sub SecIDLoop()
Dim CalcSecID As Variant
'to add one to each calcsecid
For Each CalcSecID In frmClasses
CalcSecID = CalcSecID + 1
Next
End Sub

However I keep getting a type mismatch message no matter what I do. I've tried CalcSecID=Val(CalcSecID) and that doesn't work either. By the way, the underlying data type in the associated table is text. Does someone please have a suggestion? Thanks!

 

[Travis]

Carmen,

The problem is, you only want to calculate on the value after the "-".

Use Instr and Mid to get the value

Then use Val to convert it from a String to a Double.

Basically you break your ID into two pieces: The Static Piece and the Variable Piece. Then you reset the ID = to the Concatenation of the Static and Variable.

Private Sub SecIDLoop()
Dim CalcSecID As Variant
'to add one to each calcsecid
For Each CalcSecID In frmClasses
CalcSecID = Left(CalcSecID,Instr(1,CalcSecID,"-")) & (val(Mid(CalcSecID,Instr(1,CalcSecID,"-")+1) + 1)
Next
End Sub

 

[Carmen]

Thanks for your help Travis, but it is still not working. I still get the Type Mismatch error. Any other suggestions????

 

[Travis]

What line are you getting the error on?

 

[DALeffler]

Shouldn't...

CalcSecID = Left(CalcSecID,Instr(1,CalcSecID,"-")) & (val(Mid(CalcSecID,Instr(1,CalcSecID,"-")+1) + 1)

be

CalcSecID = Left(CalcSecID,Instr(1,CalcSecID,"-")) & right(str(val(Mid(CalcSecID,Instr(1,CalcSecID,"-")+1) + 1),1)

The type mismatch comes from concatenating a value to a string (I think...)

Doug.

 

[Carmen]

I think you are right, Doug, that it's because I'm trying to combine the string and value. Maybe I'm going about this the completely wrong way, but I'm sure there must be a way to do it! Travis, I am getting the error when I attempt to enter data in the form. If I do not call the loop and just run the original code, it works fine. I tried Doug's coding and still get the type mismatch. It appears to me that your suggestions would work....

 

[Carmen]

I am trying something different:

Sub ReplaceID()
Dim calcsecid As String
Dim strPhrase As String
Dim strOldValue As String
Dim strNewValue As String

strPhrase = calcsecid
strOldValue = Right(calcsecid, 1)
strNewValue = (Val(Right(strPhrase, 1)) + 1)

strPhrase = Replace(strPhrase, strOldValue, strNewValue, 1, -1)

End Sub

I have quit getting the Type Mismatch error, but now I am apparently not calling it correctly from my original function. I am putting the Call ReplaceID line directly before the line Forms!frmClasses!text28 = calcsecid. So now it runs the original code and pays absolutely no attention to the sub routine. But I think it's progress! Any suggestions would be greatly appreciated though. PS: When I run the sub independently in an immediate window, it actually works!

[This message has been edited by Carmen (edited 12-07-2001).]