access : a function to return a specific character

is there a function which will return the character AFTER a specific character.

I have a number of field entries of different length but all have a .(point) in them somewhere eg

A201.5
B3478.28

Is there a function that will return the string character AFTER the dot.
and how would i refer in ot to the examples above
topUKlawyerAsked:
Who is Participating?
 
GrahamSkanRetiredCommented:
Well you can write one in VBA
Function FirstCharAfterFirstDot(MyString As String) As String
    FirstCharAfterFirstDot = Left$(Split(MyString, ".")(1), 1)
End Function

Open in new window

0
 
topUKlawyerAuthor Commented:
thanks

if the field was called XYZ in the table what would an update query look like with that function
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Just be carefull if it has more then two periods in it anywhere...

Jim.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
topUKlawyerAuthor Commented:
i have made  a vba function now but dont know where to put it in the query update design
0
 
GrahamSkanRetiredCommented:
You can use this in the SQL, if it to be executed in the Access environment, so:

SELECT MyTable.ID, FirstCharAfterFirstDot([Oldfeldd]) AS NewField
FROM MyTable;
0
 
topUKlawyerAuthor Commented:
many thanks will test and report
0
 
topUKlawyerAuthor Commented:
Thanks.

I have made the function in vba as suggested but when i run the query having inserted the function it states

undefined fucntion in query

why might it be undefined
0
 
GrahamSkanRetiredCommented:
Is everything in Access? The function code and the query have to be executed in the same host.
0
 
GrahamSkanRetiredCommented:
Also, are your sure that macros are enabled in Access?
0
 
topUKlawyerAuthor Commented:
Function FirstCharAfterFirstDot(compAlphaLawClientAndFileNumber As String) As String
    FirstCharAfterFirstDot = Left$(Split(compAlphaLawClientAndFileNumber, ".")(1), 1)
End Function

above: this is what I have in vba

below: SQL

SELECT archiveTable.compAlphaLawClientandFileNumber, FirstCharAfterFirstDot([compAlphaLawClientandFileNumber]) AS NewField
FROM archiveTable;
0
 
GrahamSkanRetiredCommented:
That looks OK. I tested similar code in my 2007 Access database, putting it into a VBA code module.

The SQL was tested in the query design mode. It does produce an #Error for records where there is no dot in the field. Otherwise it works as expected.
0
 
topUKlawyerAuthor Commented:
Thanks. Should it have been in a  module? I put it with everything else vba.
0
 
topUKlawyerAuthor Commented:
I have put it in  amodule and goes like your ie with error messages.

error 9 subscript out of range.

The errors are in records where eg someone has not got a Dot.
Is there anything I can do to filter the query records to only those with dots to avoid error messages.
0
 
topUKlawyerAuthor Commented:
WHERE (((archiveTable.compAlphaLawClientandFileNumber) Like "***.***"));

returns the correctly entered records and not the ones where the dots have been missed out
0
 
topUKlawyerAuthor Commented:
OK the only problem now is that the function only returns the first value after the dot
what would I need to change to make it the first 3 values after.
0
 
topUKlawyerAuthor Commented:
OK thats sorted now with above:

for points award, can you just walk me through the syntax.
Left I know but not left$
Split I dont know.

Left$(Split(compAlphaLawClientandFileNumber, ".")(1), 3)
End Function
0
 
GrahamSkanRetiredCommented:
The $ means that it is a string function. It does the same as Left(). At one time, it was compulsory (VB versions 1 and 2) on all VB string functions. Even since, it used to be recommended as being slightly faster with the $ than without, because VB knows immediately that it is not a Variant function, and doesn't have to work it out.

From habit I tend to use in on the original string functions, which also include Right$() and Mid$(), but not on the newer ones like Split and Join, introduced in VB5.

Split creates an Array, usually a sting, though it could be integer os other numbers.

Dim strArray() as string

strArray = Split("Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday",  ",")

Creates a string array, so that

MsgBox StrArray(0) will display "Sunday" (my quotes).

and so will

MsgBox Split("Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday",  ",")(0)


Incidentally, I could have used the $ sign, so:

Dim strArray()$

strArray = Split$("Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday",  ",")
0
 
topUKlawyerAuthor Commented:
would award more than 500 if I could

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.