1. ## Formula Question (2000)

Hi!

I am using the following version of a formula Pat from this lounge gave me for another database:

AmountRemaining: tblTimeSheet.[total Billed]-DSum("[Amount Paid]","tblPaymentAllocations","PayID=" & [AutoNumber])

The forumula is working great. There is one small change I'd like to make and I've yet to come up with the appropriate code. Presently, the query that runs this forumla shows either shows the amount remaining after payment has been applied. If no payment has been applied to the invoice the query result is blank. I'd like the result to = [total billed] in instances where no payment has been applied, instead of being blank, but I have not been able to come up with the correct code. Any suggestions would be greatly appreciated!

Thanks,
Leesha

2. ## Re: Formula Question (2000)

Try

AmountRemaining: tblTimeSheet.[total Billed]-Nz(DSum("[Amount Paid]","tblPaymentAllocations","PayID=" & [AutoNumber]),0)

The Nz function normally returns the first argument (DSum in this case), but if that is Null (blank), it returns the second argument (0 in this case.)

3. ## Re: Formula Question (2000)

Hi Hans,

Thanks for the help and the info on how NZ works. I love to understand the "why" behind how something works.

Speaking of working, the formula did the trick except for on minor/major thing. The first time it is run, it deletes the [Total Billed] in the first record in the tblTimeSheet. If I go into tblTimesheet and enter in [Total Billed] and re-run the query on frmAccountsReceivable, it happens every time. If I leave it blank, the I seem fine for the remainder of the changes.

Any ideas? frmAccountsReceivable is bound to tblTimesheet

Thanks,
Leesha

4. ## Re: Formula Question (2000)

Leesha,

There must be something else interfering, because using the expression just calculates a value, it doesn't change anything in a table, with or without Nz. What kind of query are you running?

5. ## Re: Formula Question (2000)

Hi Hans,

I believe its a select query. It isn't a specific query, but a query that is in the rowsource (if that makes sense). The rowsource doesn't look back to a query. The SQL is as follows:

PARAMETERS [forms]![frmAccountsReceivable]![Account Name] Text ( 255 ), [forms]![frmAccountsReceivable]![fromdate] DateTime, [forms]![frmAccountsReceivable]![Todate] DateTime;
SELECT tblTimeSheet.AutoNumber, tblTimeSheet.Date, tblEmployee.[Employee Name], tblAccountDemographics.[Account Name], tblClientDemographics.[Client Name], tblTimeSheet.[Total Billed], tblTimeSheet.[total Billed]-Nz(DSum("[Amount Paid]","tblPaymentAllocations","PayID=" & [AutoNumber]),0) AS AmountRemaining, tblEmployee.EmployeeNameID, tblAccountDemographics.AccountID
FROM tblEmployee INNER JOIN (tblClientDemographics INNER JOIN (tblAccountDemographics INNER JOIN tblTimeSheet ON tblAccountDemographics.AccountID = tblTimeSheet.AccountId) ON tblClientDemographics.ID = tblTimeSheet.ClientID) ON tblEmployee.EmployeeNameID = tblTimeSheet.EmployeeID
WHERE (((tblTimeSheet.Date) Between [forms]![frmAccountsReceivable]![FromDate] And [forms]![frmAccountsReceivable]![ToDate]) AND ((tblAccountDemographics.[Account Name])=[forms]![frmAccountsReceivable]![Account Name]))
ORDER BY tblTimeSheet.Date, tblAccountDemographics.[Account Name];

This is an old database that I'm updating with all the stuff I've learned here in the past few months. It has a LONG way to go. I'm begining to wish I hadn't tried tee hee!!

Leesha

6. ## Re: Formula Question (2000)

Leesha,

The SQL you posted is for a select query indeed. Select queries never change data in the underlying tables, so if a field in tblTimesheet is really being modified, there must be something else (a bit of code?) that causes this; it can't be the select query.

7. ## Re: Formula Question (2000)

Hi Hans,

I can't find anything in the code but then again what do I know!! I'm enclosing a stripped down version that I've saved as 97 so it will make the file requirements. If you could take a minute when to look at it when you get a chance to see what I'm missing I'd appreciate it.

Thanks,
Leesha

8. ## Re: Formula Question (2000)

I don't see Total Billed being cleared; I think i've tried everything you can do on the forms.

There were two records in tblTimeSheet whose Total Billed value was blank in the database as posted by you. I entered a value in those records; whatever I did after that, Total Billed wasn't cleared in these or any other records. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

9. ## Re: Formula Question (2000)

<img src=/S/confused.gif border=0 alt=confused width=15 height=20> Now its my turn to be confused. I used the same database I sent you. I made sure that there were no blank areas in tblTimesheet. I'm still getting the problem. For purposes of repeating it on your end, this is what I did:
1. Click on Connecticut College in the "Account Name" on frmAccountsreceivable.
2. Enter the date range of 4/1/03 - 4/1/03.
3. Click "Refresh List"
4. Click the first row - #1624
5. Click "Post Payment" command button
6. Enter 100.00 in "Amount Paid" on the Payment form
7. Under "Date Worked" select #1624
8. Enter the total as per the list (mine says 55.00 but might be different on yours if this was one of the blank ones you filled in)
9. Click the save button

On my form, Total Billed and Amount Remaining both disappear.
When I repeat the process for the next one on the list, # 1623 the same thing happens. When I skip to 1619, same thing, disappears.

Where I'm really confused is that prior to stripping down this database, only [totatl billed] for 1616 was disappearing, now it seems to be happening across the board.

FYI, I cleared all entries in tblPayment and tblPaymentAllocations before starting.

I'm really stumped as to why its happening for me and not you?? Did you convert the DB to 2000 from 97?? Would that have anything to do with it?? Is is the links between the subforms??? I'm just starting to work with subforms so I wasn't sure if that could be it.

Thanks so much,
Leesah

10. ## Re: Formula Question (2000)

One more thought, I'm getting the error from frmAccountsReceivable. Is that the form you were using???

Leesha

11. ## Re: Formula Question (2000)

Ah yes, I see now. When you click cmdSave, you set Billed Total:

Me.Total_Billed = Me.TOTAL - Me.Discount

But TOTAL and Discount are empty (Null), so Total Billed is set to Null too.

12. ## Re: Formula Question (2000)

OMG. I would never have known that to figure it out. So, if I set the default value in both of those as \$0.00 will the issue still result??

Leesha

13. ## Re: Formula Question (2000)

The problem is that TOTAL and Discount are unbound fields, so unless the user has clicked Edit Timesheet, and edited something in the part of the form, they won't be populated. (And once the user clicks the Recalculate Total button, the controls become invisible again without the possibility to see or edit them again. The Edit Timesheet button stays invisible, even when moving to a different account name.) If you set the default value of TOTAL and Discount to 0, the value of Total Billed will be set to 0 too. Is that what you want?

14. ## Re: Formula Question (2000)

>>the value of Total Billed will be set to 0 too. Is that what you want?

Nope. However, Total is unbound, but Discount is bound to discount in tblTimesheet. I was thinking if I set the default value of discount to zero as well as to Total, it would not be null, and would hopefully eliminate the problem. I think I'll just take the code out to eliminate the issue.

Thanks!
Leesha

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•