Having trouble with syntax with multiple vriteria with DMAX (1 Viewer)

atrium

Registered User.
Local time
Today, 22:37
Joined
May 13, 2014
Messages
336
My statement is
Code:
     Me.LastPaymentDateFld = DMax("DueDate", "DdSchedDDTrans", "DdSchedId = " & Me.DdSchedIdFld) & " AND ([TransCode] = 13 " & " Or [TransCode]=50 " & " Or [TransCode] = 20) " & " AND ([Status] = 'Paid' OR [Status] = 'Submitted')),0)

Me.NextPaymentDateFld = DMin("DueDate", "DdSchedDDTrans", "DdSchedId = " & Me.DdSchedIdFld) & " AND ([TransCode] = 13  & [Status] = 'Not Paid')),0)"

The module is to gain the last paid and the next to be paid dates from a list of transactions for a payment schedule
The first one above is finding the duedate of the last paid or submitted payment
The second above is finding the next duedate that is Not Paid

Any help from a fresh view would be really appreciated
 

June7

AWF VIP
Local time
Today, 04:37
Joined
Mar 9, 2014
Messages
4,459
Remove the ) from middle of expression. Why the ),0) at end of each? If you want to force 0 if result is Null, need Nz(). Too much unnecessary concatenation. Are there only 3 status options?
Code:
Me.LastPaymentDateFld = Nz(DMax("DueDate", "DdSchedDDTrans", "DdSchedId = " & Me.DdSchedIdFld & " AND ([TransCode] = 13 Or [TransCode]=50 Or [TransCode] = 20) AND [Status] <> 'Not Paid'"), 0)

Me.NextPaymentDateFld = Nz(DMin("DueDate", "DdSchedDDTrans", "DdSchedId = " & Me.DdSchedIdFld & " AND [TransCode] = 13 AND [Status] = 'Not Paid'"), 0)
Why saving these calculated values to table?
 
Last edited:

atrium

Registered User.
Local time
Today, 22:37
Joined
May 13, 2014
Messages
336
Thanks June7 They worked as required

The options are SchedId, trans code 13 (Direct Debit Payment) , Transcode 20 (Manual Payment), Transcode 50 (a Credit to their payments) .

The status of the transaction could be Paid, submitted or Not Paid.

May I ask - What does AWF means and where is the great Land?

Thanks again
 

plog

Banishment Pending
Local time
Today, 07:37
Joined
May 11, 2011
Messages
11,009
Since you're in vba I would build the criteria in a variable, then supply that variable to the dmax. That way you can debug.print it when it goes sideways. And it's most likely going sideways again even if you get the syntax right. Mixing ANDs and ORs is tricky and does require parenthesis.
 

Users who are viewing this thread

Top Bottom