Solved

access : a function to return a specific character

Posted on 2012-03-20
18
257 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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
 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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 …

856 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