Complex(for me) INSTR problem in MS Access

[RobertGabriel]

I am trying to find multiple entries that meet my criteria.
this works great--
Work_Order: IIf(IIf(InStr(1 ,[ML]![Description],"200")>0,Val(M id([ML]![Description],
InStr(1,[ML]![Description],"200"),9)))>20 0000000,Mid([ML]![Description],
InStr(1,[ML]![Description],"200"),9))

Example result = 200158687. My problem is this field has more than 1 number that I need to extract. My table format has 2 columns: named
CRnum and Description. I need a record for each Work_Order
ie:
CRnum Work_Order.

[nico5038]

Your rules for selection aren't really clear to me.
The above sample could be achieved using e.g. a LIKE statement as:
where Description like '200*'

When the Description can hold multiple fields separated by spaces you could switch to using a function to split the field and inspect the different words.

Please provide more info about the possible contents of the Description field.

Nic;o)

[RobertGabriel]

Your rules for selection aren't really clear to me.
The above sample could be achieved using e.g. a LIKE statement as:
where Description like '200*'

When the Description can hold multiple fields separated by spaces you could switch to using a function to split the field and inspect the different words.

Please provide more info about the possible contents of the Description field.

Nic;o)

The Description field is a text field that people type/copy-paste into. the Numbers I am trying to extract can be seperated by anything. spaces, commas, carriage returns, etc. so,
what I have is 1 record with
CRnum and Description.
I want to end up with as many records as are workorders.
CRnum and WorkOrder

[nico5038]

Hmm, then the first thing would be to create a function to extract the needed number.
Does it has a fixed length and can there be more than one ?

Nic;o)

[RobertGabriel]

Hmm, then the first thing would be to create a function to extract the needed number.
Does it has a fixed length and can there be more than one ?

Nic;o)

The Numbers are fixed length: 9. and they all start with 200

[nico5038]

Are you familiar with VBA coding ?

This can be solved using a loop like:

strDescription = Me!Description
WHILE INSTR(strDescription,"200") > 0
    ' get string 
    strNumber=Mid(strDescription,instr(strDescription,"200"),9)
    ' act on found string posistion...

    ' remove part with number (no length specified will take all from starting position)
    strDescription = Mid(strDescription,instr(strDescription,"200")+9)
WEND

Getting the idea ?

Nic;o)

[RobertGabriel]

I am limited with VBA. I can usually hack some but not reall good at making my own.

Are you familiar with VBA coding ?

This can be solved using a loop like:

strDescription = Me!Description
WHILE INSTR(strDescription,"200") > 0
    ' get string 
    strNumber=Mid(strDescription,instr(strDescription,"200"),9)
    ' act on found string posistion...

    ' remove part with number (no length specified will take all from starting position)
    strDescription = Mid(strDescription,instr(strDescription,"200")+9)
WEND

Getting the idea ?

Nic;o)

[nico5038]

OK, we can start with the above code, but I need to know what you want to do with the found numbers.
In general I would create a function to process all rows from the table and use the loop to insert the found numbers in the Description in another table.

For the recordset processing the code is available in the helpfile. When you try to find that and place that code here, I can help with embedding the above WHILE lop.

Let me know how the processing will have to be, post the recordset processing loop and we can start creating the function.

Nic;o)

[RobertGabriel]

OK, we can start with the above code, but I need to know what you want to do with the found numbers.
In general I would create a function to process all rows from the table and use the loop to insert the found numbers in the Description in another table.

For the recordset processing the code is available in the helpfile. When you try to find that and place that code here, I can help with embedding the above WHILE lop.

Let me know how the processing will have to be, post the recordset processing loop and we can start creating the function.

Nic;o)

ex.
CRNum Description
07-18733 "200260588
200260599"

07-18930 "Required prior to withdrawal with fuel adjacent to the rod 200260588/200260599"

07-18186 "PLCO 07-C11-050
200260170 For all valves. Other orders voided."
07-18738 200258896 200187000

desired results
CRnum
07-18733 200260588
07-18733 200260599
07-18930 200260588
07-18930 200260599
07-18186 200260170
07-18738 200258896
07-18738 200187000

[RobertGabriel]

Thank you for taking the time to help me.

desired results
CRnum WorkOrder
07-18733 200260588
07-18733 200260599
07-18930 200260588
07-18930 200260599
07-18186 200260170
07-18738 200258896
07-18738 200187000

[nico5038]

I get the idea, I'll give the needed recordset processing loop and function structure:

FUNCTION fncGetWorkDetail()
' The input table records
dim rsIn as DAO.recordset
' The output records with the details
dim rsOut as DAO.recordset

set rsIn = currentdb.openrecordset("tblWorkOrderDescription")
set rsOut = currentdb.openrecordset("tblWorkDetail")
' test for rows to be processed, else stop
If rs.eof and rs.bof then 
   exit function
endif

rsIn.movefirst

WHILE Not rsIn.eof

   rsIn.Movenext
WEND

END FUNCTION

Just add the code to a module and try to add the while from the previous comment to split the description into the needed field(s).
Then we can continue tomorrow.

Nic;o)

[RobertGabriel]

Then we can continue tomorrow.

Nic;o)

I have added the code to a module. not sure how to put the loop code in.

[nico5038]

The following code:

WHILE Not rsIn.eof

   rsIn.Movenext
WEND

will process the rows in the table row by row. The rsIn.Movefirst just infron of the loop will make the first row available and rsIn.movenext will position on the next row.

The empty line is where we need "the action" (read the processing loop for the description).

To get the Description from the rsIn recordset we can use:

strDescription = rsIn!Descriptio n

We'll also have to define the strDescription field (best on top of the code just below the FUNCTION statement like:

Dim strDescription as String

Try to make the function now with the Dim and the loop and post the result here. There's still one essential part missing, I'm curious or you find that :-)

Nic;o)

[RobertGabriel]

I think I have to do something to return a complete record.
CrNum

Function fncGetWorkDetail()
' The input table records
Dim rsIn As DAO.Recordset
' The output records with the details
Dim rsOut As DAO.Recordset
Dim strDescription As String
Dim strCrNum As String


Set rsIn = CurrentDb.OpenRecordset("tblWorkOrderDescription")
Set rsOut = CurrentDb.OpenRecordset("tblWorkDetail")

Set strDescription = rsIn!Description
Set strCrNum = rsIn!CRNum

' test for rows to be processed, else stop
If rs.EOF And rs.BOF Then
   Exit Function
End If

rsIn.MoveFirst

While Not rsIn.EOF
strDescription = Me!Description
While InStr(strDescription, "200") > 0
    ' get string
    strNumber = Mid(strDescription, InStr(strDescription, "200"), 9)
    ' act on found string posistion...

    ' remove part with number (no length specified will take all from starting position)
    strDescription = Mid(strDescription, InStr(strDescription, "200") + 9)
Wend
   rsIn.MoveNext
Wend

End Function

[RobertGabriel]

Modified

Function fncGetWorkDetail()
' The input table records
Dim rsIn As DAO.Recordset
' The output records with the details
Dim rsOut As DAO.Recordset
Dim strDescription As String
Dim strCrNum As String


Set rsIn = CurrentDb.OpenRecordset("tblWorkOrderDescription")
Set rsOut = CurrentDb.OpenRecordset("tblWorkDetail")



' test for rows to be processed, else stop
If rs.EOF And rs.BOF Then
   Exit Function
End If

rsIn.MoveFirst

While Not rsIn.EOF
strCrNum = rsIn!CRNum
strDescription = rsIn!Description
While InStr(strDescription, "200") > 0
    ' get string
    strNumber = Mid(strDescription, InStr(strDescription, "200"), 9)
    ' act on found string posistion...

    ' remove part with number (no length specified will take all from starting position)
    strDescription = Mid(strDescription, InStr(strDescription, "200") + 9)
Wend
   rsIn.MoveNext
Wend

End Function