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

Query error: "Data types in the criterion expression are incompatible"

P: n/a
Hello everyone,

I was having the following problem with a query, and after failing to
find a similar solution
on these newsgroups I decided to post here. I am quite new to Access,
so would appreciate any help/pointers in the right direction.

The problem is that I keep getting the error to the likes of "Data
types in the criterion expression are incompatible" when the query
runs.

So I have this query that runs from a form, and the query uses a
publicly defined function:

Public Function MassCalcApp1(strNation As String, strSex As String,
strAgeGroup As String, intRefYear As Integer, dblBodyHeight As Double,
dblWaist As Double, dblSitLen As Double) As String

The function returns a string, which is then used in the following
query:

++++++++++++++++++++++++++++
SELECT
A.PID,
A.Fname,
A.Sname

FROM qryAllPeople A
WHERE
(
A.Sex
)
= 'm'
AND
(
MassCalcApp1 (GetNation(), GetSex(), GetAgeGroup(),
GetRefYear(), [A.BodyHeight], 900, [A.SitLen] ) = 'OG'
)

ORDER BY A.PID;
++++++++++++++++++++++++++++

GetNation(), GetSex(), etc are all publicly defined functions that
return a global variable, and there are no problems with the values
that they return.
The problem I suspect is with the values for [A.BodyHeight] and
[A.SitLen], which are doubles stored in the tables. I have tested the
function MassCalcApp1 in the Immediate window during program execution
with my own values for [A.BodyHeight] and [A.SitLen], and it works.
When values are taken from the query/table, then it gives the
abovementioned error.
In my case, the comma "," character is used as a decimal place
holder. Could that be it? Is the damned comma sneaking in somewhere and
causing this?

As I said, any help would be appreciated as this is currently driving
me up the wall.

Regards,
J

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Jean" <je**********@hotmail.com> wrote in message
Public Function MassCalcApp1(strNation As String, strSex As String,
strAgeGroup As String, intRefYear As Integer, dblBodyHeight As Double,
dblWaist As Double, dblSitLen As Double) As String

The function returns a string, which is then used in the following
query:

++++++++++++++++++++++++++++
SELECT
A.PID,
A.Fname,
A.Sname

FROM qryAllPeople A
WHERE
(
A.Sex
)
= 'm'
AND
(
MassCalcApp1 (GetNation(), GetSex(), GetAgeGroup(),
GetRefYear(), [A.BodyHeight], 900, [A.SitLen] ) = 'OG'
)


Using criteria (='OG') with a user defined function does seem to cause this
type of problem at times.
I would try modifying the function to allow for Null values for 'BodyHeight'
and 'SitLen'
i.e. in the function declare these as Variant. (you may need to modify the
workings of the function as well).

--
peter


Nov 13 '05 #2

P: n/a
Hi Peter,

that is exactly the right answer! I changed my function and it works
now. Will test it and see what happens.

Thanks a lot
Jean

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.