Problem with Field

coolcatkelso

Registered User.
Local time
Today, 21:42
Joined
Jan 5, 2009
Messages
279
Hiya

Access 07

I have this invoice form, with some info on like Order Total, Payments, Sales Tax etc just what youd expect on an invoice,

In the Payments (Unbound) I have this code
=IIf(IsNull([WorkorderID]),0,DSum("[PaymentAmount]","Payments","[WorkorderID]=" & [WorkorderID]))

Default value is 0.00

However, if I have just created a new workorder, then nothing shows in the "payments" field, unless I goto my other form "make Payment" and delect the Amount to be paid and put a 0 in

Any help?

All other calculations work perfect, and the reason I need this to work is I have a report that shows un-paid accounts, and if there is nothing in the payment field, it shows it as being unpaid :(

Cheers
CCK
________
Penny stock picks
 
Last edited:
CCK,

Your Null is propagating to the third argument of your IIf. The IIf function always evaluates all 3 of its arguments, so even if the 1st argument evaluates to True, it still evaluates the False (3rd) argument, while attempting to return the True (2nd) argument.

So, try:
=IIf(IsNull([WorkorderID]),0,DSum("[PaymentAmount]","Payments","[WorkorderID]=" & Nz([WorkorderID],0)))

btw - since your IIf will always return a value (even Null) it's redundant to set a Default value in your control properties.

HTH,
John
 
... and before someone else picks up on it, you might be able to eliminate the IIf altogether and simply use:

=Nz(DSum("[PaymentAmount]","Payments","[WorkorderID]=" & [WorkorderID]),0)

Provided you don't have legitimate records with [WorkorderID] = Null
 
My question is, what is someone with the name Cool Cat Kelso doing on this forum? Should be in a smoke-filled club wailing on an alto sax or at least playing an upright bass! :cool:
 
Missinglinq, you know, maybe CCK is doing databases as a side-gig just to make ends meet. Jazz musicians are notorious for barely scraping by...;)

"Did you jazz happen to be around
When Lester left town?"
"He had to split fast.
He heard the forecast."

- some lyrics to the Wayne Shorter tune "When Lester Left Town"

Man, I can just see a Cool Cat Kelso reciting those lyrics jazz-poetry style with an all-star band behind him. :cool::cool:
 
Hiya cheers for the reply

Added your code and it worked no probs, however, I apologise as I didn't explain the full problem,


STAGE ONE
I create a workorder, blah blah and fill out all the fields, I add the materials needed for the job and the labour. Everything adds up correct in the amount due etc, and Payment now shows ?0.00 (Perfect)

STAGE TWO
I have a report that shows when an account has been paid or not, basic report showing the Payment field, either ?0.00 or the amount ?45,000 etc you get the picture. However, I have discovered, that there is a link missing in the above.. In order for me to show the ?0.00 in the payment collum, I need to go BACk into the workorder, and click on Make Payment,
Make Payment automattically shows how much is due to be paid, so I have to CLEAR that amount Due and put in ?0.00 Manually, close the Payments window and the workorder, run the report, and the record is there now.

Its not that it doesn't show the ?0.00 in payments, it just doesn't show the entire workorder.

Not sure if I need to re-arrange something or if its a relationship thing. I have created a relationship and query for it to work, so I'm kinda stumped.

I've included the Dbase if you want a look, I apologise for the mess lol

Instructions...

Open Dbase, Workorders by Customer should show.. Click on the Tableview and choose Workorder...

Problem is the PAYMENT part in the yellow box,, and the Make payment button form..

THe report in question is called Workorder Accounts

Currently everything will show in the report as I have manually made a ?0 payment.

Dates for the records to find, is just 01-01-01 to 01-01-10

Cheers guys

Oh forgot to say.. MSACCESS 2007
________
Dc Medical Marijuana
 

Attachments

Last edited:
Hey CCK,

I don't have A2007, so the only way I could make sense of your situation is if you listed your tables and fields (not that I know much about accounts-receivable type data models), i.e.:

WorkOrders
- WorkOrderID a/n (pk)
- WorkOrderDate date

WorkSpecs
- WorkSpecID a/n (pk)
- WorkSpecTypeID long (fk)
- WorkOrderID long (fk)
- Quantity long

etc.,

HTH,
John
 

Users who are viewing this thread

Back
Top Bottom