Control Source on condition

[BobNTN]

I have a text box that the Control Source is the field Lastdebit.
Is there a way of controlling it based on another field(Blacat) to 'if Blcat = M1 ?
I want to display the date if Blcat = M1 in one text box then display the date if Blcat = Q1 in another.

Can't seem to make it work.

 

[pbaldy]

Try

=IIf(Blcat = "M1", Lastdebit, "")

 

[BobNTN]

Thanks Paul,
that works great on Q1 but it shows nothing on M1.

I copied the cell, checked all properties, checked the query it comes off, everything is identical except the Q1 and M1.

Really strange

 

[pbaldy]

Should work for either. Can you post a sample db?

 

[pbaldy]

Maybe I've misunderstood the goal. I turned on the navigation buttons and scrolled through records, and the date would show in one box or the other, depending on the value in Blcat. What are you expecting?

 

[BobNTN]

I (she) runs a monthly debit that adds the monthly charge to the balance. That query takes the date it was ran and I am updating the field Lastdebit in the table so I can display it next to the button so she'll be aware of the last time she debited accounts. I was trying to do the same for quarterly debiting.
However, I just realized that way is not choosing a specific record since it doesn't involve one. I can't do it on any one record.
Geez, back to the drawing board.

 

[pbaldy]

Gotcha; post back if you get stuck. I'll mention that I'm not a fan of the philosophy of adding an amount to the balance, or storing the balance at all. You have no history on what makes up the balance. I'd have a transaction table where each charge would be recorded.

By the way, I would delete that attachment for now, as it appears to have personal data in it.

 

[BobNTN]

Gotcha; post back if you get stuck. I'll mention that I'm not a fan of the philosophy of adding an amount to the balance, or storing the balance at all. You have no history on what makes up the balance. I'd have a transaction table where each charge would be recorded.

By the way, I would delete that attachment for now, as it appears to have personal data in it.

That's what I thought I was doing based on your advice.
The balance thing has driven me nuts so I went to a balance field that is a running charge then I sum all payments.
Apparently I don't or can't grasp your idea of how to do it. I still have to have an 'actual' balance for customers who call in at any time.
Geez, I was ready to start this Monday.

 

[pbaldy]

My advice where? It wouldn't be the first time I said something in a confusing way, but I would never recommend a static field like that. I apologize if something I said led you down the wrong path. It's like storing quantity on hand for inventory, which I also lean against. I've written both AR type applications and inventory applications. In both, I calculated balance/quantity on hand from transactions.

In your case, I'd have both charges and payments in a transaction table (like your payments table), with payments being a negative number. Some might have a charges table and a payments table, and I wouldn't argue against them. I did it that way in an inventory db. In either case, the customer's balance on any given date is the sum of transactions before or equal to that date (or charges less payments in the 2 table method).

Don't worry, we can still bat this thing out by Monday!

 

[BobNTN]

I THOUGHT I understood what you had told me. You didn't mislead me.
I understand what you are saying but I just don't have the know-how to do all that at this late date.
This is the only access db I've done and without doubt will be the last.
Just trying to get my sister updated to a windows program and keep the learning curve WAY DOWN. Not to mention with my limited ability.

 

[BobNTN]

So if I add say, Dbtdate and Dbtamount to my Tblmisc (which has my extra pickup charges) I could use it for all debits just like the payments table for my credits ?
Then my debit query would be an append to those fields instead of an update ?

 

[pbaldy]

Sure, though since that looks like it's for special items, I might have either a separate table for regular charges, or a field for charge type or something. What you don't normally want is 2 different amount fields, which is what you'd end up with if you just added Dbtamount to that table (Dbtamount and xtraPickup). In a one-table solution, I'd have fields like:

ARID (an autonumber)
CustID (your customer ID)
TranDate
TranType (invoice, payment, adjustment, etc)
TranAmount (positive or negative, as appropriate)
Comments (might include the check number for payments, or that could be another field)
Any other necessary fields

If you wanted to go with separate charges/payments tables, you wouldn't have to worry about the positive/negative but you'd have to sum two tables to get a balance. Either way, each month you'd have a process that created a charge record for each active customer. Payments and adjustments/special charges would be recorded as appropriate.

 

[BobNTN]

That sounds great, but, working with what I already have, I have no idea how to do the Trantype and TranAmount. I would have to set it all up on one form for input and be able to select which input type it would be. Ain't got a clue how to do that.
Then I suppose the debit (invoice) part would be a query ? There are 2900 customers and couldn't be done manually.

 

[BobNTN]

ok, trying the two table method.
got the "charges' table set up , the debit queries working, and the input form for any charges set up.
seems like this will make the db grow somewhat quicker but what the heck.

still need a way to display that last debit date next to the button without depending on specific records to do it.

 

[pbaldy]

It will certainly grow faster, but Access should be able to handle it fine. If I were a customer that called for my balance, you told me $200, and I disagreed, with your previous design you'd have had no way to show me the transactions that made up my balance. I can tell you from experience that will happen, so I feel simply updating a balance field would have backfired on you in the long run.

Are you saying you want to display when you last processed the charges? You could either use a query or DMax to get the latest date from the charges table, either overall or by customer. I'd use DMax if you just wanted the last date, a query if you wanted it by customer (more efficient).

 

[BobNTN]

Yes, I agree this is better. Just having to redo a lot, but it's worth it.
No, I have buttons to debit ALL monthly customers and quarterly customers. I want the date the debit was last ran next to the button so she KNOWS when it was last ran just as a precaution. So it isn't single record associated.

 

[pbaldy]

That's right, you'll be doing 2 different types. You can either create a small 1-record table with 2 date fields and update the appropriate one when you debit customers, or since you do all of each type together, we can basically find the last charge date for any monthly customer and the last charge date for any quarterly customer.

 

[BobNTN]

If I get them from "specific" record(cust), then they (the customer) cancel ......... ????
So I need another table with two fields ? The problem with that is I can't 'update' to that table from an append query which is what I am running for the debits. ????

also, I got my balance and debit queries working except I can't get them to utilize >0 for Invoices on the 'RunBalance' expression. Is there a way I can filter out records at the report that have a zero or less balance (which the field is coming from the expression in the query) ??

 

[pbaldy]

Since presumably you're firing off the append query from a form button, you'd just update the date table immediately after running the append query. For the other method, I wasn't really thinking of using a specific customer so much as randomly picking an active customer with the appropriate code, then finding their last charge. The dates table is fine too though.

Can you post another sample with the query in it? You should be able to filter out the zero balance customers. Either that or don't worry about it and use a wherecondition when you open the report that filters them out. That might be better anyway, since then you can use the same report/query for any combination of customers. Now that I say it, that's what you asked anyway. Yes, something like

DoCmd.OpenReport "ReportName",,,"BalanceField > 0"

 

[BobNTN]

Ok, where does that line of code go ? In an event ?

Also, how do i pick a random record on the dates for the query ?

Other than those two things, looks like everything else is working. so far