Count characters in file upto specific character

[chris-uk-lad]

Hi,

I have a text file that im reading in VB. Im using the following code to specify the start and length of the text i want to import surnames into a table (position 5 in text doc, 9 in length) and these values are closed with a semi-colon.

If Trim(Mid(strLineOfText, 5, 9)) <> "" Then
strNINO = Trim(Mid(strLineOfText, 5, 9))
qryExecute![@Surname] = Trim(Mid(strLineOfText, 5, 9)) 
End If

But not all the Surnames are of the same length, so could read.

Johns;;;;
Burton;;;
Marshall;

so how could i do it, so that with my code above, i can have it read in the value upto the ';' instead of the specified length '9'.

Many Thanks

 

[KenHigg]

InStr() should give you the location of the first ";". You can work it into what you have from there...

 

[chris-uk-lad]

sorry i should have specified the data more. The text fiel thats being read has (for example)

ABC;JOHNS;27 MAPLE ROAD;

So i want to pick up the second occurance (will always be the second occurance, always starting at position 5)

but i dont know how to include the 5th position start, ending in ';' in:

Position = InStr(strLineOfText, ";")

Also, after this solved, can the variable position be used in the positioning, for example replacing 9 with the word 'position'

Trim(Mid(strLineOfText, 5, 9))

 

[CyberLynx]

If you have a string that you know is delimited, which appears to be the case here, then in my opinion the easiest way to pull the individual elements from the string is by using the Split function and place all the string elements into an array. For example:

Dim MyArray() as Variant
Dim MyString As String

MyString = "ABC;JOHNS;27 MAPLE ROAD"
MyArray = Split(MyString, ";")


Now, to retrieve the desired string element, simply reference the The Array that will hold it. For example: If you want to retrieve the 2nd element out of the delimited string (JOHNS) then you would reference the 1st Array element (Arrays start from zero not 1 unless Option Base 1 is placed within the Declarations section of your module):

MsgBox MyArray(1)

Also, after this solved, can the variable position be used in the positioning, for example replacing 9 with the word 'position'

Yes...it can

.

 

[chris-uk-lad]

the plsit isnt working, and tbh i dont understand why not as ive used the exact same thing elsewhere.

Dim strArray() As String
strArray = Split(strLineOfText, ";")

it keeps saying that the sub or function not defined (for Split)

changed it to variant and still doesnt work :x

 

[CyberLynx]

The Split function should work unless you are running a version of Access that does not contain it within the VBA Language. There is an alternative function you can try that was written by Allen Browne. It is named ParseWord.

.

 

[nIGHTmAYOR]

Chris for a peculier reason MS Access hates arrays , and yes i was at ur shoes one day , those who posted that solution never realy tried it or probably has add loads of ms libraries to their project referances
All you have to do is strike out the As statment so it goes like :

Dim strArray()
strArray = Split(strLineOfText, ";")

Works like a charm , no errors eh

 

[datAdrenaline]

Split() became available in Access 2000, along with a host of other cool functions (InStrREV, Replace, Join, etc ...). My favorite set of replacements can be found here ...

http://www.tek-tips.com/faqs.cfm?fid=4342

I really like these because they utilize a conditional compilation directive in order to prevent overloading if the app is upgraded to a higher format level.

One thing to note, however, is that the replacement Split() must return its results into a VARIANT and not an unbound string array. The VB6 version of Split() is good with this as well ... for example ...

Dim aMyArray As Variant
aMyArray = Split(...

Instead of ...

Dim asMyArray() As String
asMyArray = Split(...

 

[CyberLynx]

Dim myArray()

is the same as

Dim myArray() As Variant

Default for Variables in VBA is Variant.

I personally have never had an issue with Arrays within Access VBA or any other VBA or VB.

There are no references for the Split function just the Version of VBA you are using.

 

[nIGHTmAYOR]

waiting in anticipation then

 

[datAdrenaline]

Its not appropriate to declare an array of variants for this circumstance. Possibly just a Variant, as I have shown, which will then morph into the Array of strings returned by the Split() (both the VBA5 UDF version and the VB6 inherent version) but an Array of Variants is not the best way to go here ... In fact declaring as an Array of Variants will raise an error when using the VBA6 function. The VBA5 UDF, works fine, but not the best choice .. IMO ...

Lets illustrate ... The following WILL raise an error on the line that tries to set the Array of Variants ...

Public Function foobar2()
 
    Dim aVariant() As Variant
 
    aVariant = Split("Hello:There", ":")  '<--- Errors here for vba6 :)
 
    Debug.Print TypeName(aVariant(0))
 
End Function

However, this next block of code is fine ...

Public Function foobar3()
 
    Dim vVariant As Variant
    Dim aString() As String
 
    vVariant = Split("Hello:There", ":") 'VBA5 UDF Replacement OR VBA6
    aString = Split("Hello:There", ":")  'VBA6 and up
 
    Debug.Print TypeName(vVariant(0))
    Debug.Print TypeName(aString(0))
 
End Function

Note that the VBA6 version of Split() can write to an unbound Array of the String datatype, as well as a simple variant. The VBA5 UDF I linked to earlier writes to a simple Variant variable (or an Array of Variants). So for complete compatibility I will often declare the recipient variables of the Split() functions as a simple Variant ...

 

[KenHigg]

Wonder if he ever got this working...?

 

[nIGHTmAYOR]

datAdrenaline i love it when people enterpret my words in a professional manner and take all the credit

 

[datAdrenaline]

Hello nIGHTmAYOR ...

>> datAdrenaline i love it when people enterpret my words in a professional manner and take all the credit <<

... hmmm ... I am unsure of your tone or meaning? ... If I have offended you, that was definately not me intent ...

With respect to interpretation of your words .... which words did I interpret? I simply stated that what you posted will error out in A2000 and higher, and that an Array of variants was not appropriate here, I do not see where I re-interpretted anything you said?

Could you be specific as to where I might have "took the credit" for ideas that were originally presented by you on this thread? ... please allow that there may be some duplication of ideas simply do to the way explanations unfold or with posts received during composition time.

 

[chris-uk-lad]

Sorry guys!! thought i had already posted that id finished it :x

            position = InStr(26, strLineOfText, ";")
            endLine = position - 26
            If Trim(Mid(strLineOfText, 26, endLine)) <> "" Then