Value reset on January 1

[davidbodhi]

I'm building a form for a clinic.

Insured patients have deductables and copays that usually reset on January 1st.

To be clear, a deductable of $500 is reduced each time the patient pays, but jumps back to $500 at the beginning of the next year.

The table containing patient records has a deductable field, where the form gets the initial value. I'll be making a running calculation of how much remains after each visit, but that calculation has to start over again each year.

Can anyone suggest code to do this or point me at something similar?

 

[ScottGem]

You are going about this wrong. The balance remaining shouldn't be stored. it should be calculated:

=DSum("[charges]","table","[PatientID] = " & Me.Patient & " AND [FeeDate] > #" & DateSerial("Year(Date()),1,1 & "#")-500

This will give you a negative figure until the total charges for the year exceed $500.

 

[davidbodhi]

Scott - You misunderstood me. I'm not storing the calculated value. What is being stored is the INITIAL value, as set by each person's insurer, and which may change if the policy changes. I'm wanting a running calculation of the remaining deductable, as I said.

Thanks for the pointer. I'll see what I can do about tweaking the code you wrote to fit my specifics.

In looking it over to parse it, tell me if I understand correctly.
You're saying DSUM my control where total charges appear, my table the form refers to, where patient name = selected variable AND feedate (my control where the date of each transaction appears?) is greater than #, then proceed to define # using a DateSerial function and finally subtract the person's deductable?

There are an odd number of " in the statement. Is one missing? There is a fair amount of syntax I don't understand, yet, in this line of code. I'll be looking up syntax for DateSerial, for example.

Another question: Is there a programmatic reason for DSUMming first and subtracting the deductable, as opposed to starting with the deductable and subtracting the DSUM? So far, I don't see something checking for the result to hit zero, so does it matter which way the subtraction goes? Or does the hash mark, #, refer to zero? So far it looks like # refers to a date.

 

[ScottGem]

The octothorpe (#) is used to denote a date value in an expression.

What I'm saying is that you should sum your charges and then subtract the initial value from that sum. The reason I suggest subtracting the initial from the sum is that until the sum exceeds the initial value the formula will result in a negative value.

 

[davidbodhi]

Thanks, Scott -

I've tried your suggestion and am getting the #Name? error, so it looks like the way I'm referring to my controls is wrong.

According to the Access help files, DSUM's "[expression]" can refer to a field in a table or a control on a form, while "domain" can only refer to a table or query. "Criteria" is optional, so I ought to be able to leave it out.

To try to narrow down where my syntax error is, I've just tried to DSUM the amount owed, without even specifying who owes it. Doing this, I should only have to refer to the form control showing the calculated total [txtTotal_Due] and the table tblTransactions.

My understanding is that this would be written as follows and should provide a sum of all the calculated [txtTotal_Due] controls:
DSUM("[txtTotal_Due]","tblTransactions")

What it's actually giving me is Me.txtTotal_Due*Number_Of_Records.
Every time I add a record, the above DSUM statement multiplies the on-screen txtTotal_Due times the number of records existing in tblTransactions.

If I change the syntax to the following, I get the same result:
DSUM("Forms![frmCollect_At_Time_Of_Visit]![txtTotal_Due]","tblTransactions")

If I further change the syntax, adding a criteria where I specify a certain patient ID, by number as follows, I get the same result, but only on the records where that ID is in the specified control:
DSUM("[txtTotal_Due]","tblTransactions","txtPatient_ContactID=18")

When I try to make the patient ID more generic, to refer to whomever might be the patient, as follows, I get #error in all cases:
=DSum("[txtTotal_Due]","Transactions","txtPatient_ContactID = & Me.txtPatient_ContactID &")
Am I misusing the ampersands? I find no mention of how to use '&' in the Access help files.

Can you further enlighten me on how DSUM is parsing this statement?
It's clearly finding the control on the form. Why is it multiplying by the number of records?

Is my REAL problem that I'm trying to make this work on a control instead of a field?

 

[ScottGem]

I think you misunderstood something. The expression part of the DSUM needs to be a field in a table or a column in a query or some calculation based on those. It cannot be a control on a form.

In your case it needs to be the the field in the transaction table that holds the amount of the transaction.

Ad you do need a criteria, since you only want to to sum the transactions for a specific patient and period. The only way you wouldn't need criteria, is if your domain was a filtered query that listed only a specific person and period of time.

 

[davidbodhi]

You're right, Scott, about DSUM needing to be applied only to a field or column.

I'd misread the help screen. It says you can *include* a control on a form in <expr>. I'd misread that to mean it can refer to a control.

I know I need criteria for what I'm trying to do. I meant, in attempting to isolate what I was doing wrong, I can leave out criteria, since it's optional. I was reducing the number of variables.

I am a little confused about normalization, based on your comment, though.

Since the transaction total is a calculated amount, based on item and service prices and on taxes, I'm not supposed to be storing it. Why should I have a field that contains it?

 

[ScottGem]

Ok, I was going by earlier things you said. The DSum sums a column. It coulde be an expression (i.e. DSUM([Price]*[Qty]...) But it would be easier if you did your calcs (and some filtering) in a query, and then run the DSUM off the query.