Solved

access : a function to return a specific character

Posted on 2012-03-20
18
254 Views
Last Modified: 2012-03-21
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
0
Comment
Question by:topUKlawyer
  • 11
  • 6
18 Comments
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 37744561
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
 

Author Comment

by:topUKlawyer
ID: 37744638
thanks

if the field was called XYZ in the table what would an update query look like with that function
0
 
LVL 57
ID: 37744671
Just be carefull if it has more then two periods in it anywhere...

Jim.
0
 

Author Comment

by:topUKlawyer
ID: 37744740
i have made  a vba function now but dont know where to put it in the query update design
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 37745385
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
 

Author Comment

by:topUKlawyer
ID: 37746241
many thanks will test and report
0
 

Author Comment

by:topUKlawyer
ID: 37746582
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 37746639
Is everything in Access? The function code and the query have to be executed in the same host.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 37746670
Also, are your sure that macros are enabled in Access?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:topUKlawyer
ID: 37746725
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 37746841
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
 

Author Comment

by:topUKlawyer
ID: 37746888
Thanks. Should it have been in a  module? I put it with everything else vba.
0
 

Author Comment

by:topUKlawyer
ID: 37747045
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
 

Author Comment

by:topUKlawyer
ID: 37747160
WHERE (((archiveTable.compAlphaLawClientandFileNumber) Like "***.***"));

returns the correctly entered records and not the ones where the dots have been missed out
0
 

Author Comment

by:topUKlawyer
ID: 37747165
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
 

Author Comment

by:topUKlawyer
ID: 37747196
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 37747400
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
 

Author Comment

by:topUKlawyer
ID: 37747650
would award more than 500 if I could

Thanks.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now