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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.