DSum Criteria Question

[MF Scheetz]

I've got one table that lists contract payments for multiple monthly
payments. Each record has contract number, payment date, payment amount

In another table I have all the contract info: contract number, contract
name, contract city, contract description, etc.

The total yearly amount per city of all the contracts has a cap, $25mil. So
I want to run an If..Else..(I'll take care of that) for any new contracts
that go over the cap for any city/fiscal year they are added to.

I am trying to create a dsum() where the criteria includes both the payment
year (I've got that down) AND also the current record's city (from the other
table).

Right now I've got:

Dim Year As Integer 'Starting with first payment's year
Dim City As String
Dim YrTot as Integer 'Yearly total for selected city

Year = Format(DMin("dteBegin", "tblDR", "[txtLoanNo]='" & Me.cboLoanNo &
"' AND [intDR]=0"), "YYYY")
City= DLookup("txtCity", "tblGenLoanInfo", "[txtLoanNo]='" &
Me.cboLoanNo & "'")

Now how would I make:
YrTot = DSum("intPayment","tblDR", "[dteBegin] Like '*'&" & Year & " AND
[tblGenLoanInfo].[txtCity]= '"& City &"'") - I know this doesn't work


Any help is greatly appreciated.

-Matt

 

[Ofer Cohen]

First Year is a reserved word in Access , so use aother word

Dim MyYear As Integer

Then try instead of using like

YrTot = DSum("intPayment","tblDR", "Year([dteBegin]) = " & MyYear & " AND
[tblGenLoanInfo].[txtCity]= '" & City & "'") - I know this doesn't work

 

[MF Scheetz]

Thanks. But that still doesn't help solve the real problem, Getting the
DSum() based on data from 2 tables.

I know I can create a query to run the DSum() off of, but I was wondering if
there was another way to make this work.

-Matt

First Year is a reserved word in Access , so use aother word

Dim MyYear As Integer

Then try instead of using like

YrTot = DSum("intPayment","tblDR", "Year([dteBegin]) = " & MyYear & " AND
[tblGenLoanInfo].[txtCity]= '" & City & "'") - I know this doesn't work

--
Good Luck
BS"D

I've got one table that lists contract payments for multiple monthly
payments. Each record has contract number, payment date, payment amount

In another table I have all the contract info: contract number, contract
name, contract city, contract description, etc.

The total yearly amount per city of all the contracts has a cap, $25mil. So
I want to run an If..Else..(I'll take care of that) for any new contracts
that go over the cap for any city/fiscal year they are added to.

I am trying to create a dsum() where the criteria includes both the payment
year (I've got that down) AND also the current record's city (from the other
table).

Right now I've got:

Dim Year As Integer 'Starting with first payment's year
Dim City As String
Dim YrTot as Integer 'Yearly total for selected city

Year = Format(DMin("dteBegin", "tblDR", "[txtLoanNo]='" & Me.cboLoanNo &
"' AND [intDR]=0"), "YYYY")
City= DLookup("txtCity", "tblGenLoanInfo", "[txtLoanNo]='" &
Me.cboLoanNo & "'")

Now how would I make:
YrTot = DSum("intPayment","tblDR", "[dteBegin] Like '*'&" & Year & " AND
[tblGenLoanInfo].[txtCity]= '"& City &"'") - I know this doesn't work


Any help is greatly appreciated.

-Matt