variant data type

  • Thread starter Thread starter mrs
  • Start date Start date
M

mrs

I am trying to figure out why some of my records are not
being found in a query. The example below uses 105393
but other RAs are not being found with DispositionCodeID
blank. Here is the SQL:

SELECT tblTechSupport.RANumber,
tblModelNumber.ModelNumber,
tblTechSupport.DispositionCodeID, tblTechSupport.RACodesID

FROM (tblRACodes AS tblRACodes_1 INNER JOIN
((tblTechSupport INNER JOIN tblModelNumber ON
tblTechSupport.ModelID = tblModelNumber.ModelID)
INNER JOIN tblRACodes ON tblTechSupport.RACodesID =
tblRACodes.RACodesID) ON tblRACodes_1.RACodesID =
tblTechSupport.DispositionCodeID) INNER JOIN tblDate ON
tblTechSupport.RANumber = tblDate.RANUMBER

WHERE (((tblTechSupport.RANumber)="105393"));

RANumber 105393 is not being found during the search.
While poking around I discovered that when I populate
DispostionCode with an entry the query finds the RA.
Without the DispostionCode it doesn't find it.
DispostionCode is NOT a required field.


Not sure it this helps but:
When I did find 105393 with the DispostionCode populated
I erased the DispostionCode from the query and got:

You tried to assign the Null value to a variable that is
not a Variant data type.


Why are records not being found?

Thanks

Matt
 
With INNER JOINS all the tables must contain a least one record relevant to the
join or you won't get a row of data returned.

You can change the joins to LEFT or RIGHT JOINS and that will return data from
the other tables.

If you are using the query grid to write the query, double click on the join
line and select one of the options to return all records from one table and only
matching from the other table. You can run into problems with this-Access will
complain it can't run the query and suggest you break it into separate queries.

Also, you may not need to do this on all the joins. You will have to do it on
at least the table(s) involving the DispositionCode. As a GUESS, you might end
up with something that looks like the following.

SELECT tblTechSupport.RANumber,
tblModelNumber.ModelNumber,
tblTechSupport.DispositionCodeID, tblTechSupport.RACodesID

FROM (tblRACodes AS tblRACodes_1 LEFT JOIN
((tblTechSupport
INNER JOIN tblModelNumber
ON tblTechSupport.ModelID = tblModelNumber.ModelID)
INNER JOIN tblRACodes
ON tblTechSupport.RACodesID = tblRACodes.RACodesID)
ON tblRACodes_1.RACodesID = tblTechSupport.DispositionCodeID)
INNER JOIN tblDate
ON tblTechSupport.RANumber = tblDate.RANUMBER

WHERE (((tblTechSupport.RANumber)="105393"));
 
Thanks John,

Its working....
-----Original Message-----
With INNER JOINS all the tables must contain a least one record relevant to the
join or you won't get a row of data returned.

You can change the joins to LEFT or RIGHT JOINS and that will return data from
the other tables.

If you are using the query grid to write the query, double click on the join
line and select one of the options to return all records from one table and only
matching from the other table. You can run into problems with this-Access will
complain it can't run the query and suggest you break it into separate queries.

Also, you may not need to do this on all the joins. You will have to do it on
at least the table(s) involving the DispositionCode. As a GUESS, you might end
up with something that looks like the following.

SELECT tblTechSupport.RANumber,
tblModelNumber.ModelNumber,
tblTechSupport.DispositionCodeID, tblTechSupport.RACodesID

FROM (tblRACodes AS tblRACodes_1 LEFT JOIN
((tblTechSupport
INNER JOIN tblModelNumber
ON tblTechSupport.ModelID = tblModelNumber.ModelID)
INNER JOIN tblRACodes
ON tblTechSupport.RACodesID = tblRACodes.RACodesID)
ON tblRACodes_1.RACodesID = tblTechSupport.DispositionCodeID)
INNER JOIN tblDate
ON tblTechSupport.RANumber = tblDate.RANUMBER

WHERE (((tblTechSupport.RANumber)="105393"));

.
Click to expand...
 
Back
Top