Query ignoring functions returned value

[Vince Morgan]

Hi,
I'm not sure that I have the right group, so please bear with me.
Recently I created a fuction in the MS Access VBA IDE. The function returns
a Boolean.
I created the function to use as criteria in an Access query, and then I
discovered a problem.
The query runs ok, but it's apparently ignoring the functions return val.
It doesn't matter whether the function returns true, or false. If I
remember correctly (I haven't worked on it for a couple of days) the query
behaves as though the function always returns false, or was that true?
I've stepped through the function and observed that it is returning the
correct values, but the resulting recordset indicates otherwise
Has anyone experienced this, or know anything about this?
I'd be most gratefull for any help.
--
TIA
Vince Morgan
Remove UNSPAM
vi****@UNSPAMoptusnet.com.au

[Anthony England]

"Vince Morgan" <vi****@UNSPAMoptusnet.com.au> wrote in message
news:44**********************@news.optusnet.com.au ...

Hi,
I'm not sure that I have the right group, so please bear with me.
Recently I created a fuction in the MS Access VBA IDE. The function
returns
a Boolean.
I created the function to use as criteria in an Access query, and then I
discovered a problem.
The query runs ok, but it's apparently ignoring the functions return val.
It doesn't matter whether the function returns true, or false. If I
remember correctly (I haven't worked on it for a couple of days) the query
behaves as though the function always returns false, or was that true?
I've stepped through the function and observed that it is returning the
correct values, but the resulting recordset indicates otherwise
Has anyone experienced this, or know anything about this?
I'd be most gratefull for any help.
--
TIA
Vince Morgan
Remove UNSPAM
vi****@UNSPAMoptusnet.com.au

You haven't posted either the function or the query, so here is an example:
The function is designed to be used in a query so it is defined as public
and it accepts a variant argument since database fields can have null
values. The purpose of the function is simply to test whether a field (the
contact's name) is Bob. It returns a True/False value which is used in the
query below.

Public Function NameIsBob(varValue As Variant) As Boolean
If varValue = "Bob" Then
NameIsBob = True
End If
End Function
SELECT ConID, ConName,
NameIsBob([ConName]) AS ConIsBob
FROM tblContact
If you still haven't found what's going wrong, then post the function and
the SQL of the query.

[Anthony England]

"Vince Morgan" <vi****@UNSPAMoptusnet.com.au> wrote in message
news:44**********************@news.optusnet.com.au ...

Hi,
I'm not sure that I have the right group, so please bear with me.
Recently I created a fuction in the MS Access VBA IDE. The function
returns
a Boolean.
I created the function to use as criteria in an Access query, and then I
discovered a problem.
The query runs ok, but it's apparently ignoring the functions return val.
It doesn't matter whether the function returns true, or false. If I
remember correctly (I haven't worked on it for a couple of days) the query
behaves as though the function always returns false, or was that true?
I've stepped through the function and observed that it is returning the
correct values, but the resulting recordset indicates otherwise
Has anyone experienced this, or know anything about this?
I'd be most gratefull for any help.
--
TIA
Vince Morgan
Remove UNSPAM
vi****@UNSPAMoptusnet.com.au

You haven't posted either the function or the query, so here is an example:
The function is designed to be used in a query so it is defined as public
and it accepts a variant argument since database fields can have null
values. The purpose of the function is simply to test whether a field (the
contact's name) is Bob. It returns a True/False value which is used in the
query below.

Public Function NameIsBob(varValue As Variant) As Boolean
If varValue = "Bob" Then
NameIsBob = True
End If
End Function
SELECT ConID, ConName,
NameIsBob([ConName]) AS ConIsBob
FROM tblContact
If you still haven't found what's going wrong, then post the function and
the SQL of the query.

[Vince Morgan]

"Anthony England" <ae******@oops.co.uk> wrote in message
news:du**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

Public Function NameIsBob(varValue As Variant) As Boolean
If varValue = "Bob" Then
NameIsBob = True
End If
End Function
SELECT ConID, ConName,
NameIsBob([ConName]) AS ConIsBob
FROM tblContact
If you still haven't found what's going wrong, then post the function and
the SQL of the query.

My funtion signature is identical to your example, except for one thing. I
didn't declare it Public AFAIK.
This is something I'm doing at work and I am not able to check the SQL
string until I return tomorrow afternoon.
I'll check it first thing.
Thank you Anthony for taking the time to reply, your help is much
appreciated.

--
Vince Morgan
Remove UNSPAM
vi****@UNSPAMoptusnet.com.au

[Anthony England]

"Vince Morgan" <vi****@UNSPAMoptusnet.com.au> wrote in message
news:44**********************@news.optusnet.com.au ...

"Anthony England" <ae******@oops.co.uk> wrote in message
news:du**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

Public Function NameIsBob(varValue As Variant) As Boolean
If varValue = "Bob" Then
NameIsBob = True
End If
End Function
SELECT ConID, ConName,
NameIsBob([ConName]) AS ConIsBob
FROM tblContact
If you still haven't found what's going wrong, then post the function and
the SQL of the query.

My funtion signature is identical to your example, except for one thing.
I
didn't declare it Public AFAIK.
This is something I'm doing at work and I am not able to check the SQL
string until I return tomorrow afternoon.
I'll check it first thing.
Thank you Anthony for taking the time to reply, your help is much
appreciated.

--
Vince Morgan
Remove UNSPAM
vi****@UNSPAMoptusnet.com.au

Sorry, I should have mentioned that you don't have to put Public - if the
function is in a separate module then that will be the default. If it were
declared in a form's module or if you have explicitly declared it as
Private, then you would be getting a different sort of error. Then it would
say:
Undefined function 'YourFunctionName' in expression.

If I had to guess the problem, I would guess that either there is some fault
in the logic, so you have if...then but a condition is never met and the
function always returns either True or False. Or perhaps you do something
like this:

Function NameIsBob(varValue As Variant) As Boolean

Dim blnReturn As Boolean

If varValue = "Bob" Then
blnReturn = True
End If

End Function

where you intend to use the variable blnReturn to return the value, but omit
the final line of the function which should be:
NameIsBob = blnReturn
Without this final line, the function will always return False (the default
value for a boolean)

Anyway, this is all speculation until we see the function and query.

[CDMAPoster]

Anthony England wrote:

Anyway, this is all speculation until we see the function and query.

Some more speculation in case putting Public in does not help:

Does the function compare against a Null value?

James A. Fortune
CD********@FortuneJames.com

[Vince Morgan]

"Anthony England" <ae******@oops.co.uk> wrote in message
news:du**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

You haven't posted either the function or the query, so here is an example: The function is designed to be used in a query so it is defined as public
and it accepts a variant argument since database fields can have null
values. The purpose of the function is simply to test whether a field (the contact's name) is Bob. It returns a True/False value which is used in the query below.

Well, it was just a moment of stupidity. I should have realized it was the
SQL string.
The function, which works fine, is below.
<>
Function IsAllUpper(str As Variant) As Boolean
Dim count As Long
Dim charCode As Long
If Not IsNull(str) Then
IsAllUpper = True
For count = 1 To Len(str)
charCode = Asc(Mid$(str, count, 1))
If Not ((charCode = 32) Or (charCode = 9)) Then
If charCode > 96 Then
IsAllUpper = False
Exit For
End If
End If
Next
End If
End Function
<>
However, the SQL string is frighteningly incorrect.

<>
SELECT [Test Module].POSITION
FROM [Test Module]
WHERE ((([Test Module].POSITION)=IsAllUpper([POSITION])));

<>
I changed the last line to

"WHERE (IsAllUpper([POSITION]) = True)"

I dropped the function into the 'Criterea' box and imagined it understand
the implied condition. Too much C++ I guess.
Thank you Anthony, and James, very much for your help. And please excuse my
wasting your time
Much appreciated.
--
Vince Morgan
Remove UNSPAM
vi****@UNSPAMoptusnet.com.au

[Anthony England]

"Vince Morgan" <vi****@UNSPAMoptusnet.com.au> wrote in message
news:44**********************@news.optusnet.com.au ...

"Anthony England" <ae******@oops.co.uk> wrote in message
news:du**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

You haven't posted either the function or the query, so here is an

example:

The function is designed to be used in a query so it is defined as public
and it accepts a variant argument since database fields can have null
values. The purpose of the function is simply to test whether a field

(the

contact's name) is Bob. It returns a True/False value which is used in

the

query below.

Well, it was just a moment of stupidity. I should have realized it was
the
SQL string.
The function, which works fine, is below.
<>
Function IsAllUpper(str As Variant) As Boolean
Dim count As Long
Dim charCode As Long
If Not IsNull(str) Then
IsAllUpper = True
For count = 1 To Len(str)
charCode = Asc(Mid$(str, count, 1))
If Not ((charCode = 32) Or (charCode = 9)) Then
If charCode > 96 Then
IsAllUpper = False
Exit For
End If
End If
Next
End If
End Function
<>
However, the SQL string is frighteningly incorrect.

<>
SELECT [Test Module].POSITION
FROM [Test Module]
WHERE ((([Test Module].POSITION)=IsAllUpper([POSITION])));

<>
I changed the last line to

"WHERE (IsAllUpper([POSITION]) = True)"

I dropped the function into the 'Criterea' box and imagined it understand
the implied condition. Too much C++ I guess.
Thank you Anthony, and James, very much for your help. And please excuse
my
wasting your time
Much appreciated.
--
Vince Morgan
Remove UNSPAM
vi****@UNSPAMoptusnet.com.au

No problem. I don't know whether you will be pleased or not to learn that
the normal way to test whether a string is in all upper case is by using the
built-in StrComp function

?StrComp("Egg","EGG",vbBinaryCompare) returns 1

?StrComp("EGG","EGG",vbBinaryCompare) returns zero
Since the constant vbBinaryCompare=0, I would use the following to check if
a field was all upper case or not:

SELECT ID, MyField,
StrComp([MyField],UCase([MyField]),0)=0 AS IsUpperCase
FROM MyTable

[Vince Morgan]

"Anthony England" <ae******@oops.co.uk> wrote in message
news:du**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...

No problem. I don't know whether you will be pleased or not to learn that
the normal way to test whether a string is in all upper case is by using the built-in StrComp function

?StrComp("Egg","EGG",vbBinaryCompare) returns 1

?StrComp("EGG","EGG",vbBinaryCompare) returns zero
Since the constant vbBinaryCompare=0, I would use the following to check if a field was all upper case or not:

SELECT ID, MyField,
StrComp([MyField],UCase([MyField]),0)=0 AS IsUpperCase
FROM MyTable

That's a lot more elegant and a whole lot faster I'm sure. Time was not my
friend when I wrote the query and I haven't used Access since about 2001.
Excuses aside, I'll be using the above method from hereon :)
Thank you again Anthony,
Very much appreciated,
Regards,

Vince Morgan
Remove UNSPAM
vi****@UNSPAMoptusnet.com.au