Quotation Marks Necessary?

[mbaddar]

Hi,

I have this query below. I find that when I don't include these quotation marks at the end of my RTRIM statements I don't get back all of the criteria satisfying data. Does anyone know why this is the case? Does it have something to do with null values?Is it a good coding practice to include these quotation marks?

I'm very curious about this and would appreciate any help.

Thanks,
MBaddar


SELECT LRecs.*, MRecs.*
FROM MRecs LEFT JOIN LRecs ON MRecs.fld_RID = LRecs.fld_RID
WHERE (((LRecs.fld_Status)<>[MRecs]![fld_Status]))
OR ((RTRIM(MRecs.fld_ThirdPartyInfo1)&&quot;&quot<>RTRIM([LRecs]![fld_ThirdPartyInfo1]&&quot;&quot)
OR ((RTRIM(MRecs.fld_ThirdPartyInfo2)&&quot;&quot<>RTRIM([LRecs]![fld_ThirdPartyInfo2])&&quot;&quot

 

[Guest_imported]

There are two flavors of RTRIM:

RTRIM -> works on variants, returns a VARIANT (of type String)
RTRIM$ -> works on strings, returns a string.

If rtrim$() is passed a NULL, you get an error (because you can't convert NULL to a string...)
If rtrim() is passed a NULL, you get a NULL.

Since the comparison logic with nulls as operands always gives you a result of null, you either need to do what you've done, or change to using RTRIM$().

Are you sure you need to do the RTRIM functions? Generally, Access will whack any trailing spaces on char values.

You might be able to add the fields you're checking as OUTER JOIN fields as well, but you'll have to do two queries to do it both ways (and combine them in a UNION query). More complicated, yes, but probably faster...

 

[mbaddar]

Hi,

Thanks so much for the response. So, it was the RTRIM function that was causing my query not to bring back all of the data (when the rtrim function was trying to trim a field in my table with a null value it was bringing back a result of null)?

This was driving me crazy. I couldn't find much on it in the documentation.

Thanks,
MBaddar