Solved

access : a function to return a specific character

Posted on 2012-03-20
18
256 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

803 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