Okay, I will try to make it as easy for you as I can.
First you need to the function in a standard module. To do this:
1. Select Modules in the database window
2. Click New - The VBA Editor will open with a new module
The will be a couple of Option Statements at the top of the window
3. Paste the code below the Option Statements
4. Click Save - It will ask you for a name
Give it any name you want, I usually start module names with mod.
For example, modUtilities .
It cannot be the same name as the Function, that will cause an error.
It is okay if you name it modGetLetters, because the names are not the
same.
5. Close the VB Editor
Code to insert:
Public Function GetLetters(strBookCode As String) As String
Dim lngCtr As Long
Const conAlphabet As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
For lngCtr = 1 To Len(strBookCode)
If InStr(conAlphabet, Mid(strBookCode, lngCtr, 1)) > 0 Then
GetLetters = GetLetters & Mid(strBookCode, lngCtr, 1)
End If
Next lngCtr
End Function
----End of Code-----
6. Open your query in the Query Builder
7. Create a Calculated field to return the value you want. You do that by
putting it
in the Field row of the query builder. Give it a name followed by : and
the
calculation. In this case, it will be the function. It should look
like this
BookCode: GetLetters([BookSymbol])
BookSymbol should actually be the name of the field that has to value
you want
to return only the letters for. It is not necessary to inclulde the
field in the output
of the query.
8. Save the query and run it to see if you are getting what you expect.
Let me know how it works out for you.
Gerard said:
Klatuu schreef:
Write a VBA function that strips out everything you don't want and returns
what you want to keep. Create a calculated field in the query that calls the
function with the bookcode:
Bkcd: GetLetters([bookcode])
Gerard said:
I looked serously to find a solution for the following problem in Access
2003, but I could not find it.
Maybe somebody can help me:
I am making a catalogue of a certain library in Amsterdam. I would like
to make a query that shows the authors names, and the bookcode, but only
the letters of the code, representing the author's names. Say, an
author's name is "Chai", the booksymbol is, say, 2-25-CH3
I would like the query to show only letters CH, without the numbers
Is this possible, do you think, and how, if so, could it be done?
I hope I'll get some help.
Kind regards,
Gerard Blok
Hallo,
Thank you very much for your reply.
You wrote to me the following:
Write a VBA function that strips out everything you don't want and returns
what you want to keep. Create a calculated field in the query that
calls the
function with the bookcode:
Bkcd: GetLetters([bookcode])
As I have only a little experience with Access and I am not able to
understand what you mean exactly.
Could you be a bit clearer for a beginner?
What is a VBA function, and how do I write it?
I pasted GetLetters([bookcode])in the appropriate query-field, but I got
a fault-message. Could you tell me exactly what to do?
I hope I am not demanding too much....
Greetings,
Gerard (Amsterdam)
