How do I refer to a value on the right side of a character?

[JumpinJimRivers]

For instance, if I have 549-##, and want to refer to ##.

Thanks in advance.

 

[JumpinJimRivers]

One other thing, the "##" part could be only "#" or it could be "###", so the "-" needs to be the reference point I presume.

 

[jimmythegeek]

If my Fieldname is MyField, then this works:

mid(MyField, instr(1, MyField, "-&quot + 1) Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[Guest_imported]

Taking the assumption that the number is in a string variable try something like the following;

paste into vba module
written in access 2000

'******start cut here
Function fnRetrievePart()

Dim vmySTR As String ' the number
Dim imyLoc As Integer
Dim imyLen As Integer
Dim vmySTRBIT As String
Dim vmyPart As String

'the string to be searched
vmySTR = "915-4322"
'this with differentt number strings before and after the '-'


'the location of the break point in the string as int
imyLoc = InStr(vmySTR, "-&quot
'get the lenght of the string
imyLen = Len(vmySTR)
'retrieve from the string the right side information,
'from the last character to the break point
'set it to a variable to work with
vmyPart = Right(vmySTR, imyLen - imyLoc)

'output the variable
MsgBox vmyPart

'does this help?
End Function

'*****End cut here

 

[JumpinJimRivers]

Ok, thanks for the help from both of you. I think yours is what I need, JimmyTheGeek, but still need to tweak a little bit.

What I'm trying to do is increment the value after the "-" by one on an event. (if the initial value is null, then just make it "afixed number-1)
So based on what you gave me, I have somehting like this. But it's all red....Do you know why? I don't.




Me![MyField] = IIf(IsNull(DMax("[MyField]", "[myTable]", "[MyCriteria]=" & Me![MyCriteria])),me![NumberBeforeTheHyphen]&"-"&"1", dmax(mid("[MyField]", instr(1, [MyField], "-&quot + 1),"[MyTable]",[MyCriteria]="&[MyCriteria]) + 1)



I know it's crazy, but have a stab if you're feeling adventursome (or bored)....

 

[jimmythegeek]

Is this a primary key?
Does the number in front of the hyphen ever change? Is it a constant, or does someone enter it?
The first part of your immediate it, you test for null, why would the value ever be null? Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[JumpinJimRivers]

OK, sorry for the confusion, the number after the "-" is always null if it's the first number entered based on MyCriteria. My Criteria is a Job Number, a foreign key in MyTable, and also the number before the hyphen, and this procedure references the Job Number, i.e. MyCriteria (whatever), in a hidden field in the current form.

Does that help explain the deal or does it just make things worse?

 

[jimmythegeek]

I am sorry to say, but I am more confused. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[JumpinJimRivers]

Sorry about that, I think that might be my fault. Here it is without the IsNull part, which severely complicated the looks of it.

Me![MyField]=Me![NumberBeforeTheHyphen] & "-"& dmax(mid("[MyField]", instr(1, [MyField], "-&quot + 1),"[MyTable]",[MyCriteria]="&[MyCriteria]) + 1)


Does that help?

 

[jimmythegeek]

So the number in front of the dash (pre-dash) represents a job number, and the number after the dash (post-dash)represents an incrementing number for the given pre-dash number (almost like a SUB job number)?

If there is no post-dash number for the pre-dash number, create one? If there is one, increment it?

If so, what is the value you are starting with for the post-dash number? 1, 01, 001, 100 ?

Am I getting close? Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[JumpinJimRivers]

Yes, you're right on target. The pre-dash is the Job Number which is always the same number and is picked up in a hidden field on the form, and the post-dash is suppose to act like an AutoNumber that just numbers the Subcontractors entered in for that Job. So, for example, the first Subcontractor entered for Job #549 would be 549-1. It really shouldn't be 549-001 or 549-01.

 

[jimmythegeek]

First, I would suggest making the post-dash number either 01 if there will never be more than 99, or 001 if there will be more than 99, but less than 999. The reason is, because since this field is a text field (no choice with the dash), It will sort like this:

549-1
549-10
549-11
549-2
549-3
etc.

Making it 2 or 3 characters after the dash will prevent this.

So that being said, and assuming you are going with this plan, paste the following function into a module, change everything in red accordingly, and save it as whatever you want (Fieldname is the name of the 549-02 field in your table (YourTable)).

=====================================
Public Function GetNewJobNum(prefix As String) As String
Dim rs As Recordset
Dim strSQL As String, strVal As String
Dim tmpVal As Integer

strSQL = "SELECT FieldName FROM YourTable WHERE Left(FieldName, 3) = '" & prefix & "' ORDER BY FieldName"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

If rs.RecordCount > 0 Then
rs.MoveLast
tmpVal = CInt(Right(rs!FieldName, 2)) + 1

If tmpVal < 10 Then
strVal = CStr(&quot;0&quot; & tmpVal)
Else
strVal = CStr(tmpVal)
End If

GetNewJobNum = prefix & &quot;-&quot; & strVal
Else
GetNewJobNum = prefix & &quot;-01&quot;
End If

rs.Close
Set rs = Nothing
End Function
=====================================

Now on your form, you simply call the function, passing it the pre-dash value:

Me!WhateverField = GetNewJobNum(Me!NumberInFrontOfDash)

Please post any problems or errors. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[JumpinJimRivers]

Other tasks have kept me from trying the code yet, but just wanted to give a sincere thanks for the help. I'll keep you posted.

--thanks again!