UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Min Function Not Working, Access 2016    
 
   
wheeledgoat
post May 28 2020, 08:01 AM
Post#1



Posts: 114
Joined: 18-December 18



I've got two similar queries, the Min function works as expected on one, but not the other. Can anyone see what I evidently can't?

DLookup("[MinOfdateDrugExpire]", "[QryDrugExpire]") here works:
CODE
SELECT [tblPrecertDrug-MM].PrecertIDFK, [tblPrecertDrug-MM].inessential, Min([tblPrecertDrug-MM].dateDrugExpire) AS MinOfdateDrugExpire, [tblPrecertDrug-MM].dateDrugExpire, tblDrug.LeadTime, [dateDrugExpire]-[LeadTime] AS ReviewDate
FROM tblDrug RIGHT JOIN [tblPrecertDrug-MM] ON tblDrug.[drugID] = [tblPrecertDrug-MM].[DrugIDFK]
GROUP BY [tblPrecertDrug-MM].PrecertIDFK, [tblPrecertDrug-MM].inessential, [tblPrecertDrug-MM].dateDrugExpire, tblDrug.LeadTime
HAVING ((([tblPrecertDrug-MM].PrecertIDFK)=[Forms]![frmPrecert]![precertID]) AND (([tblPrecertDrug-MM].inessential)=False));


but DLookup("[MinOfMTMExpiration]", "[QryInsuranceExpire]") here does not! It seems to defy me and give me the max value, even when I rearrange the data..!?
CODE
SELECT [tblPrecertInsurance-MM].PrecertIDFK, [tblPrecertInsurance-MM].InsuranceIDFK, Min([tblPrecertInsurance-MM].MTMExpiration) AS MinOfMTMExpiration, [tblPrecertInsurance-MM].MTMExpiration, tblInsurance.insuranceMonthlyVerify
FROM tblInsurance RIGHT JOIN [tblPrecertInsurance-MM] ON tblInsurance.[insuranceID] = [tblPrecertInsurance-MM].[InsuranceIDFK]
GROUP BY [tblPrecertInsurance-MM].PrecertIDFK, [tblPrecertInsurance-MM].InsuranceIDFK, [tblPrecertInsurance-MM].MTMExpiration, tblInsurance.insuranceMonthlyVerify
HAVING ((([tblPrecertInsurance-MM].PrecertIDFK)=[Forms]![frmPrecert]![precertID]));


Attached is a screenshot of the data
the first (qryDrugExpire) returns the minimum date, 5/27/2020.
the second (qryInsuranceExpire) returns 5/31/2020 despite the 5/7/2020 entry. This also happens when I delete the data and re-enter in opposite order (with the earlier date/minimum value listed first) - it still gives me the later date!

At this point I think it's mocking me. But I can't be sure.
Attached File(s)
Attached File  querydatass.jpg ( 59.87K )Number of downloads: 3
 

--------------------
"If you don't make mistakes, you're not working on hard enough problems." -Frank Wilczek

"Success is getting what you want. Happiness is wanting what you get." -B.R. Hayden

Virtue is sufficient for happiness, therefore a sage is immune to misfortune.
Go to the top of the page
 
theDBguy
post May 28 2020, 08:30 AM
Post#2


UA Moderator
Posts: 78,506
Joined: 19-June 07
From: SunnySandyEggo


Hi. Rather than DLookup(), have you tried using DMin()?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
wheeledgoat
post May 28 2020, 08:46 AM
Post#3



Posts: 114
Joined: 18-December 18



Oh boy. I don't think I knew DMin was a thing. That sounds fabulous!

In the meanwhile, I did figure out the problem. Works after I removed [tblPrecertInsurance-MM].InsuranceIDFK from the SELECT statement. I think I vagly understand why, but don't think I could explain it.

I just may retool the whole thing to use DMin anyway - that seems a lot cleaner for my purpose.

Thanks DBguy!!!

--------------------
"If you don't make mistakes, you're not working on hard enough problems." -Frank Wilczek

"Success is getting what you want. Happiness is wanting what you get." -B.R. Hayden

Virtue is sufficient for happiness, therefore a sage is immune to misfortune.
Go to the top of the page
 
theDBguy
post May 28 2020, 12:01 PM
Post#4


UA Moderator
Posts: 78,506
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2020 - 09:15 AM