Problem with LEFT JOIN type Query

[MSAccessRookie]

Greetings to all,

I have started a new project and encountered what appears to be a simple Query that causes MS Access 2003 to crash and restart. Any ideas as to where to look for the issue would be appreciated.

The Query itself does not appear to have anything obvious wrong with it, and the source of the problem seems to be the LEFT JOIN statement. If I modify the Query to use RIGHT JOIN or INNER JOIN, the code runs without error.

Is there something that I do not know about the properties of LEFT JOIN that could be causing this? I am looking into getting permission to upload a copy of the database, but that may take a while since I am a consultant and do not work for the company, so I will need approval to do so. What I can say at this time is the following:

The query in question:

SELECT LocationCodes.[STATION FLOC], LocationCodes.[STATION NAME]
FROM LocationCodes LEFT JOIN GetAcquisValues ON LocationCodes.[LOCATION CODE]=cCur(GetAcquisValues.Loc_Code)
WHERE LocationCodes.[STATION FLOC] IS NOT Null AND GetAcquisValues.Loc_Code IS NULL
GROUP BY LocationCodes.[STATION FLOC], LocationCodes.[STATION NAME];

LocationCodes is a Table with the following structure:

[LOCATION CODE]   Number
[LOCATION NAME]   Text
[STATION CODE]     Text
[STATION NAME]     Text

GetAcquisValues is the following Query (Loc_Code is Text):

SELECT Loc_Code, Sum(NumDC10Assets) AS NumberOfDC10Assets, Sum(NumTC10Assets) AS NumberOfTC10Assets, Sum(NumDC10Assets)+Sum(NumTC10Assets) AS NumberOfTotalAssets, Sum(DC10Value) AS DC10Acquis_Value, Sum(TC10Value) AS TC10Acquis_Value, (Sum(DC10Value)+Sum(TC10Value)) AS TotalAcquis_Value
FROM AcquisCombinedValues
GROUP BY Loc_Code;

 

[vbaInet]

Hi MSAccessRookie,

Is LocationCode a PK?
Is Loc_Code a PK?
Why are you joining against CCur()? Naturally CCur() doesn't like Nulls so it may be causing an Overflow.

 

[MSAccessRookie]

Hi MSAccessRookie,

Is LocationCode a PK?
Is Loc_Code a PK?
Why are you joining against CCur()? Naturally CCur() doesn't like Nulls so it may be causing an Overflow.

Thanks for the reply

LocationCode is a PK
Loc_Code is a member of a Query return, and as such is not a PK

I was using cCur, because [LOCATION CODE] is a Number and Loc_Code is a String. On evaluation, I realized it should have been cInt or cDbl, becasue the String represents a number without a decimal. Changing to either of those does not fix the problem.

 

[vbaInet]

None of the conversion functions are good with Nulls. Val() on the other hand is ok with Nulls.

If that doesn't work, then you need to put Val() inside GetAcquisValues (and not within the Join) so the query deals with it before joining.

 

[MSAccessRookie]

On the surface, Val() appears to be doing what I want. Thanks for pointing out the Null Issues with the conversion functions. That also explains why only the Left Join Failed. The value cannot have a Null, and the Field will not have a Null Value, unless it is missing fromthe RIGHT side of a LEFT JOIN.

-- Still a Rookie (but working on it)

 

[vbaInet]

That also explains why only the Left Join Failed. The value cannot have a Null, and the Field will not have a Null Value, unless it is missing fromthe RIGHT side of a LEFT JOIN.

Correct!

You're welcome!