Re: [MSAccessProfessionals] Syntax error

There is some punctuation code differences between these two lines.

 

 

Sent from Mail for Windows

 

From: Paul Baldy
Sent: Wednesday, March 9, 2022 2:56 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Syntax error

 

Oops, thanks Duane.

 

 

 

Paul

Sent via mobile device

 

 

-------- Original message --------

From: Duane Hookom <duanehookom@hotmail.com>

Date: 3/9/22 2:49 PM (GMT-08:00)

To: MSAccessProfessionals@groups.io

Subject: Re: [MSAccessProfessionals] Syntax error

 

I think there are some missing double-quotes before the final ")".

 

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= " & VRecordCode & " AND [TransactionTypeID]= 2")

 

If as you mentioned all criteria a text data types:

 

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= '" & VRecordCode & "' AND [TransactionTypeID]= '2'")

 

Duane

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Paul Baldy <Pbaldy@gmail.com>
Sent: Wednesday, March 9, 2022 11:40 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Syntax error

 

You have a lot of unnecessary concatenation in there.  Try

 

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= " & VRecordCode & " AND [TransactionTypeID]= 2)

 

If as you mentioned all criteria a text data types:

 

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= '" & VRecordCode & "' AND [TransactionTypeID]= '2')

Paul

 

------ Original Message ------

From: "Youssef" <youssef2309@gmail.com>

To: MSAccessProfessionals@groups.io

Sent: 3/9/2022 9:37:30 AM

Subject: Re: [MSAccessProfessionals] Syntax error

 

Dear Mr. Paul,
thanks for your support 
I've updated the syntax and still get the message (expected: list separator or ) )
I can't notice the mistake 

please support again
TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "'" & " AND Val([RecordCode]) <= " & VRecordCode & " AND  [TransactionTypeID]= '" & 2 & "'" & ")

thanks in advance

 

Re: [MSAccessProfessionals] Syntax error

Oops, thanks Duane.

Paul

Sent via mobile device

-------- Original message --------

From: Duane Hookom <duanehookom@hotmail.com>

Date: 3/9/22 2:49 PM (GMT-08:00)

To: MSAccessProfessionals@groups.io

Subject: Re: [MSAccessProfessionals] Syntax error

I think there are some missing double-quotes before the final ")".

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= " & VRecordCode & " AND [TransactionTypeID]= 2")

If as you mentioned all criteria a text data types:

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= '" & VRecordCode & "' AND [TransactionTypeID]= '2'")

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Paul Baldy <Pbaldy@gmail.com>
Sent: Wednesday, March 9, 2022 11:40 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Syntax error

 

You have a lot of unnecessary concatenation in there.  Try

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= " & VRecordCode & " AND [TransactionTypeID]= 2)

If as you mentioned all criteria a text data types:

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= '" & VRecordCode & "' AND [TransactionTypeID]= '2')

Paul

------ Original Message ------

From: "Youssef" <youssef2309@gmail.com>

To: MSAccessProfessionals@groups.io

Sent: 3/9/2022 9:37:30 AM

Subject: Re: [MSAccessProfessionals] Syntax error

Dear Mr. Paul,
thanks for your support 
I've updated the syntax and still get the message (expected: list separator or ) )
I can't notice the mistake 

please support again
TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "'" & " AND Val([RecordCode]) <= " & VRecordCode & " AND  [TransactionTypeID]= '" & 2 & "'" & ")

thanks in advance

Re: [MSAccessProfessionals] Syntax error

I think there are some missing double-quotes before the final ")".

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= " & VRecordCode & " AND [TransactionTypeID]= 2")

If as you mentioned all criteria a text data types:

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= '" & VRecordCode & "' AND [TransactionTypeID]= '2'")

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Paul Baldy <Pbaldy@gmail.com>
Sent: Wednesday, March 9, 2022 11:40 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Syntax error

 

You have a lot of unnecessary concatenation in there.  Try

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= " & VRecordCode & " AND [TransactionTypeID]= 2)

If as you mentioned all criteria a text data types:

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= '" & VRecordCode & "' AND [TransactionTypeID]= '2')

Paul

------ Original Message ------

From: "Youssef" <youssef2309@gmail.com>

To: MSAccessProfessionals@groups.io

Sent: 3/9/2022 9:37:30 AM

Subject: Re: [MSAccessProfessionals] Syntax error

Dear Mr. Paul,
thanks for your support 
I've updated the syntax and still get the message (expected: list separator or ) )
I can't notice the mistake 

please support again
TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "'" & " AND Val([RecordCode]) <= " & VRecordCode & " AND  [TransactionTypeID]= '" & 2 & "'" & ")

thanks in advance

Re: [MSAccessProfessionals] Syntax error

You have a lot of unnecessary concatenation in there.  Try

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= " & VRecordCode & " AND [TransactionTypeID]= 2)

If as you mentioned all criteria a text data types:

TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "' AND Val([RecordCode]) <= '" & VRecordCode & "' AND [TransactionTypeID]= '2')

Paul

------ Original Message ------

From: "Youssef" <youssef2309@gmail.com>

To: MSAccessProfessionals@groups.io

Sent: 3/9/2022 9:37:30 AM

Subject: Re: [MSAccessProfessionals] Syntax error

Dear Mr. Paul,
thanks for your support 
I've updated the syntax and still get the message (expected: list separator or ) )
I can't notice the mistake 

please support again
TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "'" & " AND Val([RecordCode]) <= " & VRecordCode & " AND  [TransactionTypeID]= '" & 2 & "'" & ")

thanks in advance

Re: [MSAccessProfessionals] Syntax error

Dear Mr. Paul,
thanks for your support 
I've updated the syntax and still get the message (expected: list separator or ) )
I can't notice the mistake 

please support again
TotalDebit = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] = '" & txtAccountID & "'" & " AND Val([RecordCode]) <= " & VRecordCode & " AND  [TransactionTypeID]= '" & 2 & "'" & ")

thanks in advance

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#116198) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Re: [MSAccessProfessionals] Syntax error

String values require delimiters around them:

http://www.theaccessweb.com/general/gen0018.htm

Paul

------ Original Message ------

From: "Youssef" <youssef2309@gmail.com>

To: MSAccessProfessionals@groups.io

Sent: 3/9/2022 8:22:18 AM

Subject: [MSAccessProfessionals] Syntax error

Dear Colleagues,
I got the attached message for b/m function,
thanks to feedback with what mistake I did
Notes: for data types (RecordCode - AccountID - TransactionTypeID) all are string variables 

Public Function TotalDebitTillRecordCode(txtAccountID As Variant, txtRecordCode As Variant) As Double

TotalDebitTillRecordCode = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] like " & txtAccountID & " And Val([RecordCode]) <= " & txtRecordCode & " And [TransactionTypeID] =" & 2)

End Function

 

[MSAccessProfessionals] Syntax error

Dear Colleagues,
I got the attached message for b/m function,
thanks to feedback with what mistake I did
Notes: for data types (RecordCode - AccountID - TransactionTypeID) all are string variables 

Public Function TotalDebitTillRecordCode(txtAccountID As Variant, txtRecordCode As Variant) As Double

TotalDebitTillRecordCode = DSum("[AmountDebitFrom]", "Qry_TransactionsFullData", "[AccountID] like " & txtAccountID & " And Val([RecordCode]) <= " & txtRecordCode & " And [TransactionTypeID] =" & 2)

End Function

 

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#116196) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

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.

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

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:

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

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.

View/Reply Online (#116189) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_