yes/no format

[lamarrgodwyn]

I have a subform that has cost imformation in like: date, part number, amount of part, date paid, amount paid, waived, paid in full, wty replacement. The waived field is in yes/no format. i have a field that total all charges and i would like for the waived field box if check to subtract that charge amount from the total field. Can anyone help me with this.

 

[John Big Booty]

A Yes/No field returns a value of -1 or True for "Yes" (checked) on 0 or False for "No".

You can you a logical test (If Then statement) to check your Yes/No field and return the appropriate result.

 

[lamarrgodwyn]

I kind of new at this can you give me some examples

 

[John Big Booty]

You will need to determine when to allocate the totals value and then run some code that looks something like;

If Me.WaivedCheckBox = 0 Then
     Me.TotalsField = Whatever code you currently use to return the total
Else
     Me.TotalsField = Whatever code you currently use to return the total less the waived value
End If

Given that you also have a sub form involved check this link (bookmark it for future reference) and adjust the code accordingly to account for the relative locations of the various fields involved.

 

[lamarrgodwyn]

You will need to determine when to allocate the totals value and then run some code that looks something like;

If Me.WaivedCheckBox = 0 Then
     Me.TotalsField = Whatever code you currently use to return the total
Else
     Me.TotalsField = Whatever code you currently use to return the total
Else less the waived value
End If

Given that you also have a sub form involved check this link (bookmark it for future reference) and adjust the code accordingly to account for the relative locations of the various fields involved.

So where would I put that code in at

 

[lamarrgodwyn]

So where would i put this code in access

 

[John Big Booty]

You will need to put that code in the form's On Current Event and your check box's On Click event at the very least, you would need to fire the code at any time the underlying values are changed as well.

A slightly simpler method would be to put a modified version of the code in the Totals Text Box Control Source, something along the lines of;

= Iif ([WaivedCheckBox] = 0, Whatever code you currently use to return the total, Whatever code you currently use to return the total less the waived value)

 

[lamarrgodwyn]

I just want to thank you for all of your efforts so far but i'm still have a little troulbe getting the code to work and I have played around with if for atlease 6 hours now and still can't get it to work. Here is the code that i have been playing around with.

= IIf ([Waived] ="0",=Sum([ECPS Cost]*[Quantity])-Sum([Amount Paid]),=Sum([ECPS Cost]*[Quantity])-Sum([Amount Paid])-Sum([Extended Cost]))

When put it in as given above i get an invalid syntax error. If i put it in is as give below it will it total up my cost but will not acknowledge the check box being check or unchecked. Do you have any idea what im doing wrong. I can post the database if that will help.

=IIf([Waived]="0",Sum([ECPS Cost]*[Quantity])-Sum([Amount Paid]),Sum([ECPS Cost]*[Quantity])-Sum([Amount Paid])-Sum([Extended Cost]))

 

[John Big Booty]

You don't need to have the Double Quotes (") around the zero in the Iif test simply put;

=IIf([Waived]= 0,Sum([ECPS Cost]*[Quantity])-Sum([Amount Paid]),Sum([ECPS Cost]*[Quantity])-Sum([Amount Paid])-Sum([Extended Cost]))

 

[lamarrgodwyn]

Hello again thanks a lot but i'm still having trouble trying to get this code to work. I really new to access and do not really understand how coding suppose to work. Please forgive me. How ever I have made this changes to try and make this database more useful. So I have attached the database if you dont mind taking a look at the code to see where I am going wrong. I have tried both way via on current, on click and control source. I can seem to get it to work.

 

[John Big Booty]

First up avoid using spaces and other special characters in Object and control names as this only make life hard for you once you start writing code.

Also consider implementing a naming protocol such as; FRM_FormName, TBL_TableName, QRY_QueryName etc. in this way when you refer to a control in code you will know straight away the nature of the object the code is referring to.

For example you DB has a table and a form both called Student-Damage now when you refer to that object are you referring to the form or the table

 

[John Big Booty]

You have code in you DB that is not working, so I will once again refer you to the link I posted in post #4.

 

[John Big Booty]

Your code looks like;

If Me.Waived = 0 Then
     Me.Text30 = sum([ECPS Cost] * [Quantity]) - sum([Amount Paid])
Else
     Me.Text30 = sum([ECPS Cost] * [Quantity]) - sum([Amount Paid])
Else
    less sum([Extended Cost])

End If

But should look like;

If Me.Waived = 0 Then
     Me.Text30 = sum(Me.ECPS_Cost * Me.Quantity) - sum(Me.Amount_Paid)
Else
     Me.Text30 = sum(Me.ECPS_Cost * Me.Quantity) - sum(Me.Amount_Paid) - sum(Me.Extended_Cost)

End If

 

[lamarrgodwyn]

I know its been a while but I'm back at it again. The code you gave me last where would I put it?

 

[lamarrgodwyn]

I know its been a while but I'm back at it again. I did figure out where to put the code however I'm still get an error. I get sub or function not defined. Can you tell me what could be causing this. I have played around with if for about 2 hours and could no figured it out.

 

[John Big Booty]

I think you'll need to put it in the On Click event of the check box and the On Current event of the form.

 

[gemma-the-husky]

you could add a column into the query

myvalue: iif(flagfield, somevalue, 0)

then in your subform, you can just sum the myvalue control directly