Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-30-2019, 06:56 AM   #1
spet
Newly Registered User
 
Join Date: Oct 2018
Posts: 16
Thanks: 6
Thanked 0 Times in 0 Posts
spet is on a distinguished road
Dlookup In Query

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","GradeAQualityAdjustme nt",[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!

spet is offline   Reply With Quote
Old 04-30-2019, 07:02 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,960
Thanks: 34
Thanked 711 Times in 694 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Dlookup In Query

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
spet (04-30-2019)
Old 04-30-2019, 07:05 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,305
Thanks: 153
Thanked 1,689 Times in 1,661 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Dlookup In Query

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","GradeAQualityAdjustme nt",
[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.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
spet (04-30-2019)
Old 04-30-2019, 07:22 AM   #4
spet
Newly Registered User
 
Join Date: Oct 2018
Posts: 16
Thanks: 6
Thanked 0 Times in 0 Posts
spet is on a distinguished road
Re: Dlookup In Query

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]));
spet is offline   Reply With Quote
Old 04-30-2019, 07:25 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,960
Thanks: 34
Thanked 711 Times in 694 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Dlookup In Query

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


Thanks for the assist, Minty!

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Tags
dlookup , where statement

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
AC2007:Main query, dependant query, and Dlookup in text control not working EdNerd Queries 1 05-08-2013 12:50 PM
How to run this dlookup in a query? ariansman Queries 7 11-16-2012 03:06 AM
query with dlookup mseifman Queries 3 10-08-2004 01:53 PM
Dlookup and Other Query emblic Queries 4 07-06-2003 10:57 PM




All times are GMT -8. The time now is 09:03 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World