Help with simple code getting frostrated

  • Thread starter Thread starter moshe via AccessMonster.com
  • Start date Start date
M

moshe via AccessMonster.com

My table named "paid" has the following fields,
PaidId
CustomerId
Date1
PledgeAmount
PaidAmount
I want a main form should lookup the last pledge, from the customer, and
deduct any PaidAmount afterwards, I have the following code
Private Sub Form_Current()
Dim lastdate
Lastdate = dlast("date1","paid","pledgeAmount"<>0)
'enter data calculation as new balance
!NewBalance = Dsum (0 + "PledgeAmount"-"PaidAmount","Paid","CustomerId="& Me!
CustomerId &" And Date1 >= #"& Lastdate & "#" )
End sub
but when it should calculate i get an error
Syntex error 'customerId = 2 And date1 >=#07/10/2007#'
Could some one help find the right code?
i'm runing access 2007 but i tried it also access 2003
I'll also have to add to this code that if it is more then a year from today
it should of remove the balance
thanks in advance
 
Moshe, I don't think dsum works that way. Try this:
Do a Dlookup for PledgeAmount and another Dlookup for PaidAmount
Add them together to get !NewBalance

UpRider
 
As for the second part of your question about removing the balance after a
year:
Don't store the balance in a table in the first place. Calculate it when you
need it. After a year, make it calculate to zero.

UpRider

UpRider said:
Moshe, I don't think dsum works that way. Try this:
Do a Dlookup for PledgeAmount and another Dlookup for PaidAmount
Add them together to get !NewBalance

UpRider
Click to expand...
 
My table named "paid" has the following fields,
PaidId
CustomerId
Date1
PledgeAmount
PaidAmount
I want a main form should lookup the last pledge, from the customer, and
deduct any PaidAmount afterwards, I have the following code
Private Sub Form_Current()
Dim lastdate
Click to expand...

You should specify a datatype - the default is Variant, sometimes useful but
often inefficient. Dim LastDate As Date would be better.
Lastdate = dlast("date1","paid","pledgeAmount"<>0)
Click to expand...

This will find the value of date1 in the last record in table Paid *IN DISK
STORAGE ORDER* - an order over which you have no control! The search criterion
is incorrect as well. You need a nested lookup - look up the largest value of
Date1 in THIS CUSTOMER'S records, and find the PledgeAmount for that date:

LastDate = DMax("[Date1]", "[paid]", "[CustomerID] = " & Me.[CustomerID])

assuming that this code is on a form.
'enter data calculation as new balance
!NewBalance = Dsum (0 + "PledgeAmount"-"PaidAmount","Paid","CustomerId="& Me!
CustomerId &" And Date1 >= #"& Lastdate & "#" )
Click to expand...

This will attempt to perform arithmetic operations on the text strings (NOT
the fields) "PledgeAmount" and "PaidAmount". Not going to work!

Try:

Dim PledgeAmount As Currency
PledgeAmount = DLookUp("[PledgeAmount]", "[Paid]", _
"[CustomerID] = " & Me.CustomerID & " AND Date1 = #" _
& Format(LastDate, "mm/dd/yyyy") & "#")
Me.NewBalance = PledgeAmount - DSum("[PaidAmount]", "[Paid]", _
"[CustomerID] = " & Me.CustomerID & " AND Date1>= #" _
& Format(LastDate, "mm/dd/yyyy") & "#")
End sub
but when it should calculate i get an error
Syntex error 'customerId = 2 And date1 >=#07/10/2007#'
Could some one help find the right code?
i'm runing access 2007 but i tried it also access 2003
I'll also have to add to this code that if it is more then a year from today
it should of remove the balance
Click to expand...

I'm sorry, I can't parse this sentence. You are not storing the balance
anywhere (at least you shouldn't be!). Where do you want to "remove" it from?

John W. Vinson [MVP]
 
Thanks to UpRider and John W. Vinson.
Now it works good, I just had to add add a little code before the "delookup"
and before the "dsum" nz(...,0), and then add some code that if there is no
record it shouldn't calculate, but now it works perfect.
thanks again both of you.
My table named "paid" has the following fields,
PaidId
Click to expand...
[quoted text clipped - 6 lines]
Private Sub Form_Current()
Dim lastdate
Click to expand...

You should specify a datatype - the default is Variant, sometimes useful but
often inefficient. Dim LastDate As Date would be better.
Lastdate = dlast("date1","paid","pledgeAmount"<>0)
Click to expand...

This will find the value of date1 in the last record in table Paid *IN DISK
STORAGE ORDER* - an order over which you have no control! The search criterion
is incorrect as well. You need a nested lookup - look up the largest value of
Date1 in THIS CUSTOMER'S records, and find the PledgeAmount for that date:

LastDate = DMax("[Date1]", "[paid]", "[CustomerID] = " & Me.[CustomerID])

assuming that this code is on a form.
'enter data calculation as new balance
!NewBalance = Dsum (0 + "PledgeAmount"-"PaidAmount","Paid","CustomerId="& Me!
CustomerId &" And Date1 >= #"& Lastdate & "#" )
Click to expand...

This will attempt to perform arithmetic operations on the text strings (NOT
the fields) "PledgeAmount" and "PaidAmount". Not going to work!

Try:

Dim PledgeAmount As Currency
PledgeAmount = DLookUp("[PledgeAmount]", "[Paid]", _
"[CustomerID] = " & Me.CustomerID & " AND Date1 = #" _
& Format(LastDate, "mm/dd/yyyy") & "#")
Me.NewBalance = PledgeAmount - DSum("[PaidAmount]", "[Paid]", _
"[CustomerID] = " & Me.CustomerID & " AND Date1>= #" _
& Format(LastDate, "mm/dd/yyyy") & "#")
End sub
but when it should calculate i get an error
Click to expand...
[quoted text clipped - 3 lines]
I'll also have to add to this code that if it is more then a year from today
it should of remove the balance
Click to expand...

I'm sorry, I can't parse this sentence. You are not storing the balance
anywhere (at least you shouldn't be!). Where do you want to "remove" it from?

John W. Vinson [MVP]
Click to expand...
 
Back
Top