Access problem

[wallingd]

In a list of items thus

11VE5
8CY2
9OR11
11CY10

where e.g 11 is the student year and VE5 is the housegroup. how can one return only the housegroup in a query? A "Right([fieldname],3)" parse function, which exists at the minute, is not sufficing due to the varying lengths of the data. No leading zeroes can be added.

This is for a sports day database.

Thanks

 

[BTB 500]

Well, this formula works in Excel, so you might be able to use/adapt it:

=IF(ISERROR(VALUE(MID(A1,2,1))),RIGHT(A1,LEN(A1)-1),RIGHT(A1,LEN(A1)-2))


(where cell A1 contains the text)

 

[Flyer]

In a list of items thus

11VE5
8CY2
9OR11
11CY10

where e.g 11 is the student year and VE5 is the housegroup. how can one return only the housegroup in a query?

Whose idea was it to structure the data like that?!

You're going to need a function that walks through your data character by character until it reaches an alpha character; this is what you need in ColdFusion:

<cfset GetHouseGroup = "">
<cfset intLength = Len(Trim(strData))>
<cfloop from="1" to="#intLength#" index="i">
	<cfif Mid(strData, i, 1) GTE 0 AND Mid(strData, i, 1) LTE 9>
		<!--- Do Nothing --->
	<cfelse>
		<cfset GetHouseGroup = Mid(strData, i-1, intLength)>
	</cfif>
</cfloop>

<cfoutput>#GetHouseGroup#</cfoutput>

So, converting to Access VBA (been a while, so check it ) ...

Public Function GetHouseGroup(strData As String) As String 

Dim i As Integer, intLength as Integer

intLength = Len(Trim(strData)) 

For i = 1 To intLength
    If Mid(strData, i, 1) >= 0 And Mid(strData, i, 1) <= 9 Then
        'Do Nothing
    Else
        GetHouseGroup = Mid(strData, i-1, intLength) 
        Exit Function 
    End If 
Next i 

End Function

Shove that in a module, and use this in a query column:

HouseGroup: GetHouseGroup([YourData])

Should be enough to get you going

 

[BTB 500]

Assuming there are less than 100 student years there are only two options though:

character 2 of the string is numeric
character 2 of the string is not numeric

That's what I based my formula on - a simple 'If, else'

 

[Flyer]

Assuming there are less than 100 student years there are only two options though:

Good point ... but don't like to make assumptions

 

[BTB 500]

We both agree the data structure is pants, anyway

 

[Howard]

Now, in English , please gentlemen .......

 

[BTB 500]

Only if you explain about Spring tides and stuff

 

[masqueraid]

"Now, in English , please gentlemen .......

"

If the second character is a letter, split the string into the first character (Student Year) and the remaining text (Housegroup). If the second character is a number split after the first two characters instead.

 

[JonLittlechild]

Try this:

Mid([FieldName], Len(Format(Val([FieldName]), "####")) + 1)

Jon

 

[wallingd]

The data structure was invented by the college, not me. Not a VBA god, but thanks for the help anyway everyone. I was wondering if you could have an if..then that processes the len of the string and does a right,4 if the len = 6 and right,3 if len = 5. I will stick some of your formulae in and see what happens.

 

[BTB 500]

I was wondering if you could have an if..then that processes the len of the string and does a right,4 if the len = 6 and right,3 if len = 5. I will stick some of your formulae in and see what happens.

No, that would return R11 for your example value of 9OR11. Because both parts are variable in length it's a little more complicated.

I'm no VBA God either, hence my simple formula (which works 100% in Excel so presumably will in Access if that has the same string etc. functions available).

 

[wallingd]

I personally would have done it in Excel and I follow your formula. Other things have taken priority though at the minute...

 

[JonLittlechild]

Try this:
Mid([FieldName], Len(Format(Val([FieldName]), "####")) + 1)

This works in three stages.

Firstly it gets the numeric value of the string using the Val() function which gives:

11VE5 -> 11
8CY2 -> 8
9OR11 -> 9
11CY10 -> 11

Then it takes this numeric value and converts it back to a string using the Format() function.

Now that we know the length of the number on the front of the string, we can take the remainder of the string using the Mid() function.

So, Format(Val([FieldName]), "####") gives you up to the first non-numeric character (as a string) and Mid([FieldName], Len(Format(Val([FieldName]), "####")) + 1) gives you the rest, regardless of the length of the two segments.

No need for a VBA function or Excel. I can give you the SQL equivalent if you need.

Jon

 

[wallingd]

That's fine thanks Jon - I gave that a go. Think we're ok now generally with the database.