select where does not equal but should equal? 1

[simon551]

Hi Access Guru:
I have this query that gives me some details about bonds. I want to be able to run it so that I see all the details only if the tblUpdate.UpdateD does not match a specific date("RunDate"), and then I want the tblUpdate.UpdateD and UpdatePrice to be blank. Does that make sense? tblUpdate should have UpdatePrice values on a bunch of dates, but I want to check it for a specific date, and then be able to input. I've tried Not RunDate in the criteria for UpdateD but that will give me all the UpdateD info that is there for other dates.
Thanks in advance. Sorry if my question sounds confusing.


SELECT tblManagersTE.Manager, tblBondDetails.BondID, tblBondDetails.BondDesc, tblBondDetails.CA, tblBondDetails.Coupon, tblBondDetails.MatureD, tblBondDetails.FirstCallD, tblBondDetails.CallPerc, tblUpdate.UpdateD, tblUpdate.UpdatePrice, [Forms]![frmReconPrice]![txtDate] AS RunDate
FROM (tblManagersTE INNER JOIN tblBondDetails ON tblManagersTE.MgrID = tblBondDetails.MgrID) LEFT JOIN tblUpdate ON tblBondDetails.BondID = tblUpdate.BondID;

 

[PHV]

You may try to replace this:
LEFT JOIN tblUpdate ON tblBondDetails.BondID = tblUpdate.BondID;
By this:
LEFT JOIN (
SELECT * FROM tblUpdate WHERE UpdateD=[Forms]![frmReconPrice]![txtDate]
) AS U ON tblBondDetails.BondID = U.BondID
WHERE U.BondID Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[simon551]

hugs and kisses PHV! You are an amazing person.

Thanks again.