Re: [MSAccessProfessionals] MAX Date in an Expression

Here are the fields in the query:

SELECT B4602200.NAME, tblUnit.UnitCode, tblUnit.BedroomCount, tblUnit.CurrentStatus, tblUnit.UnitStatus, tblUnit.ClassificationType, tblUnit.UnitHousingType, tblUnit.UnitID, tlkpCommunity.CommunityName, B4602200.BSSI_Tenant_Lease_Status, B4602200.BSSI_Lease_Execution_Dat

FROM (B4602200 INNER JOIN tblUnit ON B4602200.LOCNCODE = tblUnit.UnitCode) LEFT JOIN tlkpCommunity ON tblUnit.CommunityID = tlkpCommunity.CommunityID

WHERE (((B4602200.BSSI_Tenant_Lease_Status)=2))

ORDER BY tblUnit.UnitCode;

What I am trying to return is the current tenant name. To get that I need the Latest lease start date because the query returns previous tenants.

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

On Friday, March 4, 2022, 04:06:00 PM CST, Duane Hookom <duanehookom@hotmail.com> wrote:

Hi Art,

It's a bit difficult without knowing the fields in qryCurrentTenant or other tables. Do you want the max date for the current unit? Just taking a WAG:

Me.txtMaxLeaseStartDate = DMax("[LeaseStartDate]", "qryCurrentTenant", "[LOCName] = '" & Me.txtUnitID.Value & "'")

Regards,

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Art Lorenzini via groups.io <dbalorenzini=yahoo.com@groups.io>
Sent: Friday, March 4, 2022 4:00 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] MAX Date in an Expression

 

Is there a MAX date in Access? I have a Dlookup expression Me.txtCurrentTenant = DLookup("[Name]", "qryCurrentTenant", "[LOCName] = '" & Me.txtUnitID.Value & "'")

I need to add like AND MAX("LeaseStartDate")

Or something like that.

Any ideas