Search string for a character 2

[jmeckley]

I have 2 instances where I need this process done. One needs to search fields for "-". Everything to the left goes in field "A", everything to the right goes in field "B".

The second instance would need to do the same except search for an end of line character. It's not visible in the field in access, but it is visible in Excel. It looks like two, bold, pipe characters.

I have done this before, searching for the "-", using a formula in Excel, but I do not know how to do this in an Access module using VBA. Thank you for your assistance

Jason Meckley
Database Analyst
WITF

 

[chrissie1]

try searching in the help file for the instr function "What a wonderfull world" - Louis armstrong

 

[MarrowGenx]

Have you tried the split function. If not, check out this page:

http://www.j-walk.com/ss/excel/tips/tip93.htm

It shows some good examples of how this function works. Hope this helps.

Marrow

 

[FancyPrairie]

i = instr(strValue,"-&quot

if (i > 0) then
A = Mid(strValue,1,i-1)
B = Mid(strValue,i+1)
End if

I assume you are looking for a carriage return line feed character. If so, it would look like this:

i = instr(strValue,vbCrLf)

 

[jmeckley]

Of the options listed here I think FancyPrairie's example would work the best for my situation. I have inserted the code and can follow the logic. However, I am stuck again.

I have the following code in place to run the query. I don't know how to set the value of the field = to StrValue, and then update the appropiate fields for that record. The source field is Benefit, the 2 destination fields are Benefit_Code (before dash), and Benefit_Desc (after dash).

the row of **** indicates where I am stuck. Your help is extremely beneifical.

Public Function DashSearch()
Dim I As Integer
Dim strValue As String
Dim SqlStr As String
Dim TCount As Integer
Dim qdfTemp As QueryDef 'Passed another public function to count the records
Dim DB As Database 'this database
Dim ChkRst As DAO.Recordset 'the current recordset

' Open the record set
SqlStr = "select Benefit from Constituent_Benefit_tbl;"
Set DB = CurrentDb
Set qdfTemp = DB.CreateQueryDef("", SqlStr)
GetrstTemp qdfTemp
Set ChkRst = qdfTemp.OpenRecordset(dbOpenDynaset, dbSeeChanges)

'If records appear cycle through and parse the data
If ChkRst.RecordCount <> 0 Then
ChkRst.MoveFirst
strValue = ChkRst.Fields.??? '****************************

I = InStr(strValue, &quot;-&quot

If (I > 0) Then
A = Mid(strValue, 1, I - 1)
B = Mid(strValue, I + 1)
End If
End If

ChkRst.Close
Set ChkRst = Nothing

End Function Thank you for your assistance

Jason Meckley
Database Analyst
WITF

 

[FancyPrairie]

Not sure what you are trying to do. But here's my guess.

Dim dbs as DAO.Database
Dim rst as DAO.Recordset

Dim i as Integer

Set dbs = CurrentDB
Set rst = dbs.OpenRecordset(&quot;Select Benefit from Constituent_Benefit_tbl;&quot

if (rst.bof) and (rst.eof) then
Msgbox &quot;No Records&quot;
Else
strValue = rst!YourFieldName
i = InStr(strValue, &quot;-&quot

If (i > 0) Then
A = Mid(strValue, 1, i - 1)
B = Mid(strValue, i + 1)
End If
End If

rst.Close
Set rst = Nothing