473,320 Members | 1,914 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
2 2845
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: 21novembre | last post by:
Hi all, I made a question several days before to describe my strange trouble of mysqldump. But I still can't figour it out. Well, I just want to ask another question whether I could just backup...
0
by: s_gregory | last post by:
The mdb is considerable size 70 +- mb. A complex union query was working well, but when an additional union select... was added into the query, selecting identical fields from a different source,...
5
by: deko | last post by:
After developing an MDB in Access 2003 on WS03, then making it into an MDE and deploying it on a WinXP box with Access 2003 installed, I get this error: Function is not available in expressions...
0
by: John Smith | last post by:
Hi I am getting the above error when I try to run the union query shown below select all as , as , as , as , ,, ,, , , as , as , as , as , as from
3
by: pw | last post by:
Hi, I created and distributed an Access 2003 MDE. When the user opens up a form he get's an error message :Function is not available in expressions in query expression 'Trim( & ", " & )'. ...
1
by: jmarr02s | last post by:
I am using MS Access 2003 and get the following error "Data type mismatch in criteria expression" Any suggestions on how to fix this? This is my code: SELECT IIf(=15 Or =19, "15 og 19",...
2
by: sfrvn | last post by:
I am embarrassed to say I cannot make this work. Recently upgraded to Access 2003, but do not know if that part of problem (AKA 'syntax change'). Would someone be kind enough to lead me by the...
4
by: thebarefootnation | last post by:
Hi I have the following error message "Syntax Error (missing operator) in query expression" occurring when I am trying to update combo box within a form. My code is: Dim strSQL As String
6
by: blackdogharry | last post by:
Hi, I am a novice VBA user. My access version is 2007 on Windows XP PC. I need to create a query that will take pre-existing text values of the B_status field (such as "Received_Emailed",...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.