By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,585 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

Query ignoring functions returned value

P: n/a
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
Mar 6 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a

"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.
Mar 6 '06 #2

P: n/a

"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.
Mar 6 '06 #3

P: n/a
"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
Mar 7 '06 #4

P: n/a

"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.
Mar 7 '06 #5

P: n/a
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

Mar 7 '06 #6

P: n/a
"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
Mar 8 '06 #7

P: n/a

"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


Mar 8 '06 #8

P: n/a
"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
Mar 8 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.