For Klatuu

  • Thread starter Thread starter Gerard
  • Start date Start date
G

Gerard

You sent me this code:


Public Function GetLetters(strBookCode As String) As String
And the following citeria in the query-thing:

BookCode: GetLetters([BookSymbol])
I don't quite understand you when you say that "booksymbol" should be
the name of the field that has the value I want.
The field is named Bookcode, as it is "cited" in the function and also
in the criteria-field in the query.
Maybe you can be still a bit clearer, I don't have any experience at all
with this kind of more complicated things.

I hope we'll succeed!

Regards,
Gerard
 
If the field in your table is named BookCode, then you can change the code to
this
BookSymbol: GetLetters([BookCode])

the name BookSymbol can be anything meaningful to your. Here is what is
happening. You are creating a Calculated Field in your query. That is a
field that is not in the table, but derived from some calculation on data in
a record in the table. You have to give it a name. It will become the name
of the field in the Query. Whereever you use the query, you will refer to
the field by that name.

The calculation, in this case, is done by a User Defined Function. You are
passing the value of the field named [BookCode] to the function. The
function removes all characters except letters from the value it receives and
passes back just the letters found in the value. The value returned by the
function will be the value in the field BookSymbol of the query.

Please post back if that doesn't clear it up for you.

Gerard said:
You sent me this code:


Public Function GetLetters(strBookCode As String) As String
And the following citeria in the query-thing:

BookCode: GetLetters([BookSymbol])
I don't quite understand you when you say that "booksymbol" should be
the name of the field that has the value I want.
The field is named Bookcode, as it is "cited" in the function and also
in the criteria-field in the query.
Maybe you can be still a bit clearer, I don't have any experience at all
with this kind of more complicated things.

I hope we'll succeed!

Regards,
Gerard
Click to expand...
 
Klatuu schreef:
If the field in your table is named BookCode, then you can change the code to
this
BookSymbol: GetLetters([BookCode])

the name BookSymbol can be anything meaningful to your. Here is what is
happening. You are creating a Calculated Field in your query. That is a
field that is not in the table, but derived from some calculation on data in
a record in the table. You have to give it a name. It will become the name
of the field in the Query. Whereever you use the query, you will refer to
the field by that name.

The calculation, in this case, is done by a User Defined Function. You are
passing the value of the field named [BookCode] to the function. The
function removes all characters except letters from the value it receives and
passes back just the letters found in the value. The value returned by the
function will be the value in the field BookSymbol of the query.

Please post back if that doesn't clear it up for you.

Gerard said:
You sent me this code:


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-----
Click to expand...

And the following citeria in the query-thing:

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.
Click to expand...

I don't quite understand you when you say that "booksymbol" should be
the name of the field that has the value I want.
The field is named Bookcode, as it is "cited" in the function and also
in the criteria-field in the query.
Maybe you can be still a bit clearer, I don't have any experience at all
with this kind of more complicated things.

I hope we'll succeed!

Regards,
Gerard
Click to expand...
Click to expand...
We are talking about making a selection query, right?
We are talking about typing something in the row "criteria", right?
What do you mean by "creating a calculated field"? How do you do that?
I tried several namings: Booksymbol,bookcode, but I get all the time the
same message:The expression contains an invalid operator: point,
exclamation mark, invalid brackets .

The problem is also that I have a dutch version, so a bit in doubt if I
understand well your indications.
The function is pasted in the opening screen after clicking at modules,
right?

Gerard
Gerard
 
Gerard, it just dawned on me I have been giving you some incorrect
information. The function does go in the VBA Editor as you asked. After you
have pasted it into a module, save the module, but give the module a
different name thant the funtion.

I was incorrectly telling you to put the code in the query in the field
name. I just realized this is an Update query, so it does not go in the
Field Row. It goes in the Update To row of the BookCode field and you don't
need a calculated field name, so it should be:

GetLetters([BookCode])

My sincere apology.

As to the invalid bracketing, I don't know that it is a problem with this
code. First, make your query a select query without the code in it. Run it
to make sure it works, then change it to an Update query and put the code in
as above and see what the results are.



Gerard said:
Klatuu schreef:
If the field in your table is named BookCode, then you can change the code to
this
BookSymbol: GetLetters([BookCode])

the name BookSymbol can be anything meaningful to your. Here is what is
happening. You are creating a Calculated Field in your query. That is a
field that is not in the table, but derived from some calculation on data in
a record in the table. You have to give it a name. It will become the name
of the field in the Query. Whereever you use the query, you will refer to
the field by that name.

The calculation, in this case, is done by a User Defined Function. You are
passing the value of the field named [BookCode] to the function. The
function removes all characters except letters from the value it receives and
passes back just the letters found in the value. The value returned by the
function will be the value in the field BookSymbol of the query.

Please post back if that doesn't clear it up for you.

Gerard said:
You sent me this code:


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-----

And the following citeria in the query-thing:

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.

I don't quite understand you when you say that "booksymbol" should be
the name of the field that has the value I want.
The field is named Bookcode, as it is "cited" in the function and also
in the criteria-field in the query.
Maybe you can be still a bit clearer, I don't have any experience at all
with this kind of more complicated things.

I hope we'll succeed!

Regards,
Gerard
Click to expand...
Click to expand...
We are talking about making a selection query, right?
We are talking about typing something in the row "criteria", right?
What do you mean by "creating a calculated field"? How do you do that?
I tried several namings: Booksymbol,bookcode, but I get all the time the
same message:The expression contains an invalid operator: point,
exclamation mark, invalid brackets .

The problem is also that I have a dutch version, so a bit in doubt if I
understand well your indications.
The function is pasted in the opening screen after clicking at modules,
right?

Gerard
Gerard
Click to expand...
 
Klatuu schreef:
Gerard, it just dawned on me I have been giving you some incorrect
information. The function does go in the VBA Editor as you asked. After you
have pasted it into a module, save the module, but give the module a
different name thant the funtion.

I was incorrectly telling you to put the code in the query in the field
name. I just realized this is an Update query, so it does not go in the
Field Row. It goes in the Update To row of the BookCode field and you don't
need a calculated field name, so it should be:

GetLetters([BookCode])

My sincere apology.

As to the invalid bracketing, I don't know that it is a problem with this
code. First, make your query a select query without the code in it. Run it
to make sure it works, then change it to an Update query and put the code in
as above and see what the results are.



Gerard said:
Klatuu schreef:
If the field in your table is named BookCode, then you can change the code to
this
BookSymbol: GetLetters([BookCode])

the name BookSymbol can be anything meaningful to your. Here is what is
happening. You are creating a Calculated Field in your query. That is a
field that is not in the table, but derived from some calculation on data in
a record in the table. You have to give it a name. It will become the name
of the field in the Query. Whereever you use the query, you will refer to
the field by that name.

The calculation, in this case, is done by a User Defined Function. You are
passing the value of the field named [BookCode] to the function. The
function removes all characters except letters from the value it receives and
passes back just the letters found in the value. The value returned by the
function will be the value in the field BookSymbol of the query.

Please post back if that doesn't clear it up for you.

:

You sent me this code:


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-----

And the following citeria in the query-thing:

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.

I don't quite understand you when you say that "booksymbol" should be
the name of the field that has the value I want.
The field is named Bookcode, as it is "cited" in the function and also
in the criteria-field in the query.
Maybe you can be still a bit clearer, I don't have any experience at all
with this kind of more complicated things.

I hope we'll succeed!

Regards,
Gerard
Click to expand...
We are talking about making a selection query, right?
We are talking about typing something in the row "criteria", right?
What do you mean by "creating a calculated field"? How do you do that?
I tried several namings: Booksymbol,bookcode, but I get all the time the
same message:The expression contains an invalid operator: point,
exclamation mark, invalid brackets .

The problem is also that I have a dutch version, so a bit in doubt if I
understand well your indications.
The function is pasted in the opening screen after clicking at modules,
right?

Gerard
Click to expand...
Click to expand...

As soon as I start the Update query, after introducing :
GetLetters([BoekCode]), the program jumps to the VB-screen, makes the
first line yellow, and says: syntaxis failure.
What to do?
I made a correction for all the instances mentioning Bookcode to
Boekcode (that is the word in dutch), but that can not be the cause of
the problem, I guess...
When I want to go back to the query-screen, it says: this interrupts the
fault-finding procedure...

Gerard
 
I don't know where there may be a syntax error. I tested it before I posted
it.
Can you post it back to me so I can look it over.

Also, is it possible there is a problems with the Alphabet? I know there
are some differences in some languages, like German, which has some different
letters.

Gerard said:
Klatuu schreef:
Gerard, it just dawned on me I have been giving you some incorrect
information. The function does go in the VBA Editor as you asked. After you
have pasted it into a module, save the module, but give the module a
different name thant the funtion.

I was incorrectly telling you to put the code in the query in the field
name. I just realized this is an Update query, so it does not go in the
Field Row. It goes in the Update To row of the BookCode field and you don't
need a calculated field name, so it should be:

GetLetters([BookCode])

My sincere apology.

As to the invalid bracketing, I don't know that it is a problem with this
code. First, make your query a select query without the code in it. Run it
to make sure it works, then change it to an Update query and put the code in
as above and see what the results are.



Gerard said:
Klatuu schreef:
If the field in your table is named BookCode, then you can change the code to
this
BookSymbol: GetLetters([BookCode])

the name BookSymbol can be anything meaningful to your. Here is what is
happening. You are creating a Calculated Field in your query. That is a
field that is not in the table, but derived from some calculation on data in
a record in the table. You have to give it a name. It will become the name
of the field in the Query. Whereever you use the query, you will refer to
the field by that name.

The calculation, in this case, is done by a User Defined Function. You are
passing the value of the field named [BookCode] to the function. The
function removes all characters except letters from the value it receives and
passes back just the letters found in the value. The value returned by the
function will be the value in the field BookSymbol of the query.

Please post back if that doesn't clear it up for you.

:

You sent me this code:


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-----

And the following citeria in the query-thing:

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.

I don't quite understand you when you say that "booksymbol" should be
the name of the field that has the value I want.
The field is named Bookcode, as it is "cited" in the function and also
in the criteria-field in the query.
Maybe you can be still a bit clearer, I don't have any experience at all
with this kind of more complicated things.

I hope we'll succeed!

Regards,
Gerard

We are talking about making a selection query, right?
We are talking about typing something in the row "criteria", right?
What do you mean by "creating a calculated field"? How do you do that?
I tried several namings: Booksymbol,bookcode, but I get all the time the
same message:The expression contains an invalid operator: point,
exclamation mark, invalid brackets .

The problem is also that I have a dutch version, so a bit in doubt if I
understand well your indications.
The function is pasted in the opening screen after clicking at modules,
right?

Gerard
Click to expand...
Click to expand...

As soon as I start the Update query, after introducing :
GetLetters([BoekCode]), the program jumps to the VB-screen, makes the
first line yellow, and says: syntaxis failure.
What to do?
I made a correction for all the instances mentioning Bookcode to
Boekcode (that is the word in dutch), but that can not be the cause of
the problem, I guess...
When I want to go back to the query-screen, it says: this interrupts the
fault-finding procedure...

Gerard
Click to expand...
 
Klatuu said:
I don't know where there may be a syntax error. I tested it before I posted
it.
Click to expand...

The function will (probably!) fail if you pass an empty string. Try
this instead:

Public Function GetLetters(strBookCode As Variant) As String
Dim lngCtr As Long
Const conAlphabet As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
GetLetters = ""
If Len(strBookCode) = 0 Then Exit Function 'NULL/empty string

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
 
Thanks for reviewing the code.

It will not fail on an empty string. It will return an empty string;
however, it will fail on a Null. The reason it does not fail on an empty
string is that lngCtr is initialized at 0 for an empty string. The first
test of the loop
For lngCtr = 1 To Len(strBookCode)
Click to expand...
lngCtr is greater than the 0 the Len function will return, so the loop will
never execute. The function is a string function so it will return an empty
string.

You modification will also fail on a Null vaule:
If Len(strBookCode) = 0 Then Exit Function 'NULL/empty string
Click to expand...
Testing the len of Null returns Null, so it will continue to the next line
where it will fail.

Also, since we changed the data type to Variant, good practice would be to
change the names in the procedure to be prefixed with var rather than str.

IMHO, one line If statements are a not good coding practice. The are not as
readable and can easily be missed when making mods to code.

One other thing that occurs to me is that it also will replace a Null with a
zero length string, which is probably not the right thing to do. With that,
good naming conventions, and good coding practices in mind, here is a rewrite:

Public Function GetLetters(varBookCode As Variant) As Variant
Dim lngCtr As Long
Const conAlphabet As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

'If null or zero length string is received - send it back
If Len(Nz(varBookCode, "")) = 0 Then
GetLetters = varBookCode
Else
'Strip out everything except Alphabet Letters
For lngCtr = 1 To Len(varBookCode)
If InStr(conAlphabet, Mid(varBookCode, lngCtr, 1)) > 0 Then
GetLetters = GetLetters & Mid(varBookCode, lngCtr, 1)
End If
Next lngCtr
End If
End Function
 
Klatuu schreef:
Thanks for reviewing the code.

It will not fail on an empty string. It will return an empty string;
however, it will fail on a Null. The reason it does not fail on an empty
string is that lngCtr is initialized at 0 for an empty string. The first
test of the loop
lngCtr is greater than the 0 the Len function will return, so the loop will
never execute. The function is a string function so it will return an empty
string.

You modification will also fail on a Null vaule:
Testing the len of Null returns Null, so it will continue to the next line
where it will fail.

Also, since we changed the data type to Variant, good practice would be to
change the names in the procedure to be prefixed with var rather than str.

IMHO, one line If statements are a not good coding practice. The are not as
readable and can easily be missed when making mods to code.

One other thing that occurs to me is that it also will replace a Null with a
zero length string, which is probably not the right thing to do. With that,
good naming conventions, and good coding practices in mind, here is a rewrite:

Public Function GetLetters(varBookCode As Variant) As Variant
Dim lngCtr As Long
Const conAlphabet As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

'If null or zero length string is received - send it back
If Len(Nz(varBookCode, "")) = 0 Then
GetLetters = varBookCode
Else
'Strip out everything except Alphabet Letters
For lngCtr = 1 To Len(varBookCode)
If InStr(conAlphabet, Mid(varBookCode, lngCtr, 1)) > 0 Then
GetLetters = GetLetters & Mid(varBookCode, lngCtr, 1)
End If
Next lngCtr
End If
End Function
Click to expand...
This works!

Thank you very much, Klatuu.

Kind regards,

Gerard
 
Klatuu said:
Thanks for reviewing the code.
Click to expand...

Even though I did it badly? :)
Testing the len of Null returns Null, so it will continue to the next line
where it will fail.
Click to expand...

Rather intriguingly, Len(vbNullString) returns 0, which either means
that vbNullString isn't actually NULL, or that Len() doesn't work the
way as described in the manual!
 
Back
Top