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

  • Thread starter Thread starter rocketeer
  • Start date Start date
R

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.
 
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
(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.)
Click to expand...
I can use this workaround, but it BUGS me. What is going on here?
Click to expand...
Thanks,
Jerome.
Click to expand...
Click to expand...

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.
 
Back
Top