Query based on autonumber field failing w/o CInt()

[rocketeer]

I have queries built from other queries that I'm trying to join. I
wish to give a simplified version of my problem.

I have one query I'll call qryEmployees. It has a column
CurrentJobCode, which is defined to be Long Integer. If I create a
query like this:
select CurrentJobCode from qryEmployees where CurrentJobCode = 54
I get one record returned.

I have another query I'll call qryGrades. It has a column JobCode,
which ultimately comes from a Long Integer autonumber field. This
query:
select JobCode from qryGrades where JobCode = 54
returns one record.

If I join the two:
select CurrentJobCode from qryEmployees inner join qryGrades on
qryEmployees.CurrentJobCode = qryGrades.JobCode where
qryEmployees.CurrentJobCode = 54
I get zero records.

But if I introduce a CInt(), like this:
select CurrentJobCode from qryEmployees inner join qryGrades on
CInt(qryEmployees.CurrentJobCode) = qryGrades.JobCode where
qryEmployees.CurrentJobCode = 54
I get the expected one record.

(Note that duplicating this on another system might not yield the
results I'm getting. It might be dependent on the number of layers of
prior queries, or whatever.)

I can use this workaround, but it BUGS me. What is going on here?

Thanks,
Jerome.

 

[rocketeer]

Depends on what the qryEmployees does to CurrentJobCode. Does it turn
it into a string? [Using Format() on a number will do that.] Is the
data type of qryEmployees.CurrentJobCode the same as qryGrades.JobCode?
--
MGFoster:::mgf00

I have queries built from other queries that I'm trying to join. I
wish to give a simplified version of my problem.

I have one query I'll call qryEmployees. It has a column
CurrentJobCode, which is defined to be Long Integer. If I create a
query like this:
select CurrentJobCode from qryEmployees where CurrentJobCode = 54
I get one record returned.

I have another query I'll call qryGrades. It has a column JobCode,
which ultimately comes from a Long Integer autonumber field. This
query:
select JobCode from qryGrades where JobCode = 54
returns one record.

If I join the two:
select CurrentJobCode from qryEmployees inner join qryGrades on
qryEmployees.CurrentJobCode = qryGrades.JobCode where
qryEmployees.CurrentJobCode = 54
I get zero records.

But if I introduce a CInt(), like this:
select CurrentJobCode from qryEmployees inner join qryGrades on
CInt(qryEmployees.CurrentJobCode) = qryGrades.JobCode where
qryEmployees.CurrentJobCode = 54
I get the expected one record.

(Note that duplicating this on another system might not yield the
results I'm getting. It might be dependent on the number of layers of
prior queries, or whatever.)

I can use this workaround, but it BUGS me. What is going on here?

Thanks,
Jerome.

No gyrations are done on it. The value is fetched in one query,
passed along in others (perhaps including in queries that group, such
as "GROUP BY CurrentJobCode") and then finally referenced as you see
above. No calculation or conversion performed. This is confirmed by
me in the column display: The CurrentJobCode looks and acts like a
number, not a string.

I could think I had a rounding error (such as 54.000001), but the
column is defined as Long Integer.

Jerome.