ACCESS FEED

Jumat, 04 Maret 2022

Re: [MSAccessProfessionals] MAX Date in an Expression

Hi Art,

Your query columns include UnitCode, not LOCNCODE so you should try:

Me.txtCurrentTenant = DLookup("[NAME]", "qryCurrentTenantListAll", "[UnitCode] = '" & Me.UnitCode & "'")

This removes the "Value" property which is the default and unnecessary. This assumes UnitCode is a text field. It looks like your field named NAME is from another database that you might not have any control over. If you do, please remember that NAME is a reserved word since every object has a NAME property.

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:50 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] MAX Date in an Expression

OK. I think I am over thinking this. This is my code in the form_Load:

Me.txtCurrentTenant = DLookup("[NAME]", "qryCurrentTenantListAll", "[LOCNCODE] = '" & Me.UnitCode.Value & "'")

This sends me the error: "The expressionyou entered as a query parameter produced this error" '[LOCNCODE]'

THis is the query as it sits now.

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;

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

Re: [MSAccessProfessionals] MAX Date in an Expression

Art,

I don't see the LOCNCODE field in the query.

Duane

Sent from my mobile

On Mar 4, 2022, at 4:50 PM, Art Lorenzini via groups.io <dbalorenzini=yahoo.com@groups.io> wrote:

LOCNCODE

Re: [MSAccessProfessionals] MAX Date in an Expression

OK. I think I am over thinking this. This is my code in the form_Load:

Me.txtCurrentTenant = DLookup("[NAME]", "qryCurrentTenantListAll", "[LOCNCODE] = '" & Me.UnitCode.Value & "'")

This sends me the error: "The expressionyou entered as a query parameter produced this error" '[LOCNCODE]'

THis is the query as it sits now.

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;

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

Re: [MSAccessProfessionals] MAX Date in an Expression

Can we assume BSSI_Lease_Execution_Dat is the column you need in your expression? You need to help us out a little since we don't know your application.

Domain aggregate functions can be nested. I would probably use the generic function found at https://www.tek-tips.com/faqs.cfm?fid=4233

Duane

Sent from my mobile

On Mar 4, 2022, at 4:38 PM, Art Lorenzini via groups.io <dbalorenzini=yahoo.com@groups.io> wrote:

BSSI_Lease_Execution_Dat

Re: [MSAccessProfessionals] MAX Date in an Expression

Here are the fields in the query:

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

Re: [MSAccessProfessionals] MAX Date in an Expression

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

[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

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_