Invalid use of null

[dee]

Hi,

My query is working fine unless there is a blank or n/a in my
tbl_participants. When that happens, I get an Invalid use of Null error.

If I remove my <60, it displays the integer value of my BoxNo, but when I
add it, I get the error. Here is my code:

SELECT DISTINCT tbl_participants.ppt_first_name,
tbl_participants.ppt_last_name, tbl_participants.box_no,
tbl_participants.ppt_no, tbl_participants.study_no, tbl_dwelling.house_no,
tbl_communities.community_code, tbl_communities.community_name, "X0A 1H0" AS
postal_code, CInt([box_no]) AS Box
FROM tbl_communities, tbl_dwelling INNER JOIN tbl_participants ON
tbl_dwelling.dwelling_id = tbl_participants.dwelling_id
WHERE (((tbl_participants.box_no)<>"isempty" And
(tbl_participants.box_no)<>"n/a") AND ((tbl_participants.study_no) Not Like
"ULC*") AND ((tbl_communities.community_code)="15") AND
((CInt([box_no]))<60));

 

[Conan Kelly]

dee,

I'm guessing that is because some [box_no]'s are NULL. If that is so, then
you are trying to convert NULL to an interger with "((CInt([box_no]))<60))".

Look up the NZ() function in Access Help.

Maybe this will work:

((CInt(Nz([box_no],0)))<60))

HTH,

Conan

 

[KARL DEWEY]

WHERE (((tbl_participants.box_no)<>"isempty" And
(tbl_participants.box_no)<>"n/a")
You have tbl_participants.box_no as a text field then looking to see if it
has a number less than 60 ((CInt([box_no]))<60)); ODD!

It looks like a problem with your parentheses. I do not follow your
combinations in the where part of the query.

 

[dee]

I know it's odd. The reason why is that depending on the box number, the
address may refer to one postal code or another. It's a rather crazy
request, but I'm trying to do my best to accomodate the people who have no
idea that this isn't easy to achieve!

In addition to blanks, I also may have n/a in the box number to make things
even more confusing.
--
Thanks!

Dee

(tbl_participants.box_no)<>"n/a")
You have tbl_participants.box_no as a text field then looking to see if it
has a number less than 60 ((CInt([box_no]))<60)); ODD!

It looks like a problem with your parentheses. I do not follow your
combinations in the where part of the query.
--
KARL DEWEY
Build a little - Test a little

Hi,

My query is working fine unless there is a blank or n/a in my
tbl_participants. When that happens, I get an Invalid use of Null error.

If I remove my <60, it displays the integer value of my BoxNo, but when I
add it, I get the error. Here is my code:

SELECT DISTINCT tbl_participants.ppt_first_name,
tbl_participants.ppt_last_name, tbl_participants.box_no,
tbl_participants.ppt_no, tbl_participants.study_no, tbl_dwelling.house_no,
tbl_communities.community_code, tbl_communities.community_name, "X0A 1H0" AS
postal_code, CInt([box_no]) AS Box
FROM tbl_communities, tbl_dwelling INNER JOIN tbl_participants ON
tbl_dwelling.dwelling_id = tbl_participants.dwelling_id
WHERE (((tbl_participants.box_no)<>"isempty" And
(tbl_participants.box_no)<>"n/a") AND ((tbl_participants.study_no) Not Like
"ULC*") AND ((tbl_communities.community_code)="15") AND
((CInt([box_no]))<60));

 

[dee]

Thanks - between the nz and doing part of the query in a separate one, it
seems to be working.

Thanks again!
--
Thanks!

Dee

dee,

I'm guessing that is because some [box_no]'s are NULL. If that is so, then
you are trying to convert NULL to an interger with "((CInt([box_no]))<60))".

Look up the NZ() function in Access Help.

Maybe this will work:

((CInt(Nz([box_no],0)))<60))

HTH,

Conan

Hi,

My query is working fine unless there is a blank or n/a in my
tbl_participants. When that happens, I get an Invalid use of Null error.

If I remove my <60, it displays the integer value of my BoxNo, but when I
add it, I get the error. Here is my code:

SELECT DISTINCT tbl_participants.ppt_first_name,
tbl_participants.ppt_last_name, tbl_participants.box_no,
tbl_participants.ppt_no, tbl_participants.study_no, tbl_dwelling.house_no,
tbl_communities.community_code, tbl_communities.community_name, "X0A 1H0"
AS
postal_code, CInt([box_no]) AS Box
FROM tbl_communities, tbl_dwelling INNER JOIN tbl_participants ON
tbl_dwelling.dwelling_id = tbl_participants.dwelling_id
WHERE (((tbl_participants.box_no)<>"isempty" And
(tbl_participants.box_no)<>"n/a") AND ((tbl_participants.study_no) Not
Like
"ULC*") AND ((tbl_communities.community_code)="15") AND
((CInt([box_no]))<60));