Compare Fields with < But Get #Error

[Doctor]

The following query gives the old #Error. But I can't figure out what is
causing it. My query is based on another query with the following fields:
EventID, Teams, Places, and Lanes. All of these fields are number fields.

I want my current query to figure out how many heats of each event I need to
run during competition. But when I use this I get the #Error. If I switch the
< to * or / or =, I get a number in the query results; but with the < I only
get #Error. For grins and giggles, I posted the entire SQL statement below,
because I know someone will ask.

Thanks in advance.

Trouble Statement:
Heats2: IIf([Teams]<[Lanes],1,0)

Rest of SQL:
SELECT qryEventTeamCount.EventID,
IIf([Teams]<[Lanes],1,IIf([Teams]<(2*[Lanes]-[Places]),2,IIf([teams]<(3*[Lanes]-2*[places]),3,IIf([teams]<(4*[Lanes]-3*[Places]),4,0))))
AS Heats, IIf([Teams]<[Lanes],1,0) AS HeatsTest
FROM qryEventTeamCount;

Both Heats and HeatsTest give the #Error

 

[Jeff Boyce]

What happens if you substitute actual (test) values in place of the
fieldnames?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Doctor]

It works fine with numbers in the fields, as expected. Based on that I would
expect that the problem with be with the format of the fields Teams and
Lanes. However, if I do Teams*Lanes, I get an aswer; if I do Teams/Lanes I
get a numerical answer; and if I do = I get a numerical answer. This one is
tough for me. Can't crack it.

What happens if you substitute actual (test) values in place of the
fieldnames?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The following query gives the old #Error. But I can't figure out what is
causing it. My query is based on another query with the following fields:
EventID, Teams, Places, and Lanes. All of these fields are number fields.

I want my current query to figure out how many heats of each event I need
to
run during competition. But when I use this I get the #Error. If I switch
the
< to * or / or =, I get a number in the query results; but with the < I
only
get #Error. For grins and giggles, I posted the entire SQL statement
below,
because I know someone will ask.

Thanks in advance.

Trouble Statement:
Heats2: IIf([Teams]<[Lanes],1,0)

Rest of SQL:
SELECT qryEventTeamCount.EventID,
IIf([Teams]<[Lanes],1,IIf([Teams]<(2*[Lanes]-[Places]),2,IIf([teams]<(3*[Lanes]-2*[places]),3,IIf([teams]<(4*[Lanes]-3*[Places]),4,0))))
AS Heats, IIf([Teams]<[Lanes],1,0) AS HeatsTest
FROM qryEventTeamCount;

Both Heats and HeatsTest give the #Error

 

[Doctor]

I might be on to something, I copied the subquery results and pasted them
into excel to see if I got any of those green triangles. Sure enough, even
though the Lanes column contained only numbers, excel read them as text. I'll
be able to figure this one out. Thanks for helping me trouble shoot, Jeff.

What happens if you substitute actual (test) values in place of the
fieldnames?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The following query gives the old #Error. But I can't figure out what is
causing it. My query is based on another query with the following fields:
EventID, Teams, Places, and Lanes. All of these fields are number fields.

I want my current query to figure out how many heats of each event I need
to
run during competition. But when I use this I get the #Error. If I switch
the
< to * or / or =, I get a number in the query results; but with the < I
only
get #Error. For grins and giggles, I posted the entire SQL statement
below,
because I know someone will ask.

Thanks in advance.

Trouble Statement:
Heats2: IIf([Teams]<[Lanes],1,0)

Rest of SQL:
SELECT qryEventTeamCount.EventID,
IIf([Teams]<[Lanes],1,IIf([Teams]<(2*[Lanes]-[Places]),2,IIf([teams]<(3*[Lanes]-2*[places]),3,IIf([teams]<(4*[Lanes]-3*[Places]),4,0))))
AS Heats, IIf([Teams]<[Lanes],1,0) AS HeatsTest
FROM qryEventTeamCount;

Both Heats and HeatsTest give the #Error

 

[Jeff Boyce]

Here's a possibility...

Check back in the underlying tables in Access. Do any of the fields
affected have a data type of "lookup"? If so, congratulations! You've just
(re-)discovered one of the reasons why the lookup data type gathers such
consistent consensus ... AGAINST its use!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I might be on to something, I copied the subquery results and pasted them
into excel to see if I got any of those green triangles. Sure enough, even
though the Lanes column contained only numbers, excel read them as text.
I'll
be able to figure this one out. Thanks for helping me trouble shoot, Jeff.

What happens if you substitute actual (test) values in place of the
fieldnames?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The following query gives the old #Error. But I can't figure out what
is
causing it. My query is based on another query with the following
fields:
EventID, Teams, Places, and Lanes. All of these fields are number
fields.

I want my current query to figure out how many heats of each event I
need
to
run during competition. But when I use this I get the #Error. If I
switch
the
< to * or / or =, I get a number in the query results; but with the < I
only
get #Error. For grins and giggles, I posted the entire SQL statement
below,
because I know someone will ask.

Thanks in advance.

Trouble Statement:
Heats2: IIf([Teams]<[Lanes],1,0)

Rest of SQL:
SELECT qryEventTeamCount.EventID,
IIf([Teams]<[Lanes],1,IIf([Teams]<(2*[Lanes]-[Places]),2,IIf([teams]<(3*[Lanes]-2*[places]),3,IIf([teams]<(4*[Lanes]-3*[Places]),4,0))))
AS Heats, IIf([Teams]<[Lanes],1,0) AS HeatsTest
FROM qryEventTeamCount;

Both Heats and HeatsTest give the #Error