Dlookup In Query

[spet]

I'm trying to execute this query to return a value from another table(GradeAQualityAdjustment).

SELECT CheckStubInfo.PatronNo, CheckStubInfo.[BF%], CheckStubInfo.SCC, CheckStubInfo.Bacteria, CheckStubInfo.[Protein%], Round(IIf((350-([CheckStubInfo].[SCC]/1000))>0,(350-([CheckStubInfo].[SCC]/1000))*[Pricing Info].[SCC Premium Price]),2) AS SCCAvg, DLookUp("QualityAdjustment","GradeAQualityAdjustment",[CheckStubInfo].[SCC] Between DLookUp("BegQuality","GradeAQualityAdjustment") And DLookUp("EndQuality","GradeAQualityAdjustment")) AS QualityAdj
FROM [Pricing Info] INNER JOIN CheckStubInfo ON [Pricing Info].Date = CheckStubInfo.Date
WHERE ((([Pricing Info].Date)=[CheckStubInfo].[Date]) AND ((CheckStubInfo.Grade)="A"));

My problem is, that as soon as my query finds the first QualityAdj value, it only returns that value for the records that match. It doesn't look at any other records. I'm pretty sure it has to do with my where statement and my syntax for passing that as a string, but I'm confused where to put my "'".


Thank you!

 

[theDBguy]

Hi. Are you trying to look up multiple values for the same match or just one? DLookup() can only return one value. If you want all matching values, maybe you could try JOINing the other table in your query instead.

 

[Minty]

Bring the other table (GradeAQualityAdjustment) into your query and directly refer to the fields in it without a join in your criteria.

At the moment your DLookup criteria makes no sense (I've split it out to help see);

DLookUp("QualityAdjustment","GradeAQualityAdjustment",
[CheckStubInfo].[SCC] Between DLookUp("BegQuality","GradeAQualityAdjustment") And DLookUp("EndQuality","GradeAQualityAdjustment")) AS QualityAdj

CheckStubInfo.SCC isn't part of the GradeAQualityAdjustment table so it can't make head nor tail of it.

 

[spet]

Wow! I was overthinking that. Thank you Minty!


This works so much better...
SELECT CheckStubInfo.PatronNo, CheckStubInfo.[BF%], CheckStubInfo.SCC, CheckStubInfo.Bacteria, CheckStubInfo.[Protein%], Round(IIf((350-([CheckStubInfo].[SCC]/1000))>0,(350-([CheckStubInfo].[SCC]/1000))*[Pricing Info].[SCC Premium Price]),2) AS SCCAvg, GradeAQualityAdjustment.QualityAdjustment AS QualAdj
FROM GradeAQualityAdjustment, [Pricing Info] INNER JOIN CheckStubInfo ON [Pricing Info].Date = CheckStubInfo.Date
WHERE ((([Pricing Info].Date)=[CheckStubInfo].[Date]) AND ((CheckStubInfo.Grade)="A") AND ([CheckStubInfo].[Scc] Between [GradeAQualityAdjustment].[BegQuality] And [GradeAQualityAdjustment].[EndQuality]));

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.


Thanks for the assist, Minty!