Help required with strings

[smiler44]

Help required with strings resolved

Cant believe 'm stuck with this.

My string is contained within a variable. I want to know if the string contains abc. the location of abc may change or it may not even be there.
If the variable contains abc I want my next variable to contain abc and the next 6 characters to the right.

I can check if the variable contains abc and I can get the position of the a but I cant work out how to get the abc and the next 8 characters.
Please can you help? This is what I have

 Dim pos As Integer
 dim asg as string
 dim varytwo as string
  
 asg = "the aplpabet abcdefghi is the first 9 letters"
  
 pos = InStrRev(asg, "abc")
  
 vary2 = ? ' this is where I am stuck

thank you
smiler44

 

[smiler44]

got it

 Dim pos As Integer
 dim asg as string
 dim varytwo as string
  
 asg = "the aplpabet abcdefghi is the first 9 letters"
  
 pos = InStrRev(asg, "abc")

 If pos > 0 Then
      varytwo = Mid(asg, pos, 8)
      MsgBox ("string found")
      End If
 [/code
  
 smiler44

 

[Rx_]

The custom function will work just fine. If this function is used in a query, the custom function works fine.
Another option just for theory purpose is to use the Select statement in a query or recordset. Then use built in SQL functions.
If it is a large recordset where time and resources are important, my prediction is that the Select using built-in functions will be faster.
Of course, if it is just one to one thousand records at a time, it won't really make a big difference.

I was looking for the five characters after the keyword Pad in a column.

SELECT Mid([NEPA_Document_Name],InStr([Copy Of NEPA].[NEPA_Document_Name],"Pad"),5) AS MidExample, 
InStr([Copy Of NEPA].[NEPA_Document_Name],"Pad") AS InstrLocation, 
Len([NEPA_Document_Name]) AS LengthofColumn, 
Left([NEPA_Document_Name],Len([NEPA_Document_Name])-InStr([Copy Of NEPA].[NEPA_Document_Name],"Pad")) AS Together
FROM [Copy Of NEPA];

This example doesn't exactly meet your objective. But, it indicates that in Theory Select Statement has some built in functionality to achieve the same goal.

-- SQL Server: The substring is used
SELECT SUBSTRING('In Theory, Practice and Theory are the same, in Practice they are not',11,6)

 

[smiler44]

The custom function will work just fine. If this function is used in a query, the custom function works fine.
Another option just for theory purpose is to use the Select statement in a query or recordset. Then use built in SQL functions.
If it is a large recordset where time and resources are important, my prediction is that the Select using built-in functions will be faster.
Of course, if it is just one to one thousand records at a time, it won't really make a big difference.

I was looking for the five characters after the keyword Pad in a column.

SELECT Mid([NEPA_Document_Name],InStr([Copy Of NEPA].[NEPA_Document_Name],"Pad"),5) AS MidExample, 
InStr([Copy Of NEPA].[NEPA_Document_Name],"Pad") AS InstrLocation, 
Len([NEPA_Document_Name]) AS LengthofColumn, 
Left([NEPA_Document_Name],Len([NEPA_Document_Name])-InStr([Copy Of NEPA].[NEPA_Document_Name],"Pad")) AS Together
FROM [Copy Of NEPA];

This example doesn't exactly meet your objective. But, it indicates that in Theory Select Statement has some built in functionality to achieve the same goal.

-- SQL Server: The substring is used
SELECT SUBSTRING('In Theory, Practice and Theory are the same, in Practice they are not',11,6)

sorry RX_ that has gone right over my head

smiler44

 

[Rx_]

Welcome to my life!

But, for this answer:
You can write a custome function (with vba) and use it to manage strings inside a Form. Or inside a Query.
In the Query Designer:
MyCustomeFunction: TheFunction([FirstName])

The function will read the value of the record's field [FirstName] and retrurn the string the funciton was designed for.
For small data sets, this works really well.

If Linked tables are being used and the dataset returned is somewhat larger, then the Access custom function will not play well with SQL Server ODBC. It will work. But, it won't work as quickly or efficientlly as a native SQL Statement.
It turns out that the SQL language does support string management.
However, it probably isn't as simple to use as writing a custom VBA function. In cases where large datasets need to be managed, it would probably be worth using a SQL solution.

Just an FYI. Sometimes the motorcycle will get the package delivered. Othertimes we need to use the freight train.