Access MS Access - Changing content of a calculated field based on value of checkbox-Help!

[aaldridge]

I'm building a database for a social service organization to track their rental/utilities assistance program, in which they track pledges clients receive from local churches and other organizations. Part of the year, they also have a program called Gift of Warmth to help with utilities. If clients are eligible for GOW, then GOW matches the amount of pledges they receive from the churches. So basically, I have a form with the controls:
[GOW] which is a yes/no checkbox
[Total Amount Needed]
[Total Client Payments] (calculated field from an underlying query)
[Total Other Funding](calculated field from an underlying query)
[Total Pledges Received](calculated field from an underlying query)
[GOW Amount](the field I need to work with)
[Amount Still Needed]{=[Total Amount Needed]-([Total Client Payments]+[Total Other Funding]+[Total Pledges Received]+[GOW Amount])}

So basically, if [GOW]=no then the field [GOW amount] needs to be set to zero, but if [GOW]=yes, then [GOW Amount]=[Total Pledges Received]

I really hope someone can help me with this. Other than this problem, the database is coming along nicely, and I want to be able to start testing it in real use soon, as they are using a shared excel workbook to track this now and are have tons of problems with it. They can't really afford to hire a professional database designer to do it, so i volunteered (or was volunteered, anyway).

Thanks so much,

Amanda

 

[cvo-aalst]

I'm building a database for a social service organization to track their rental/utilities assistance program, in which they track pledges clients receive from local churches and other organizations. Part of the year, they also have a program called Gift of Warmth to help with utilities. If clients are eligible for GOW, then GOW matches the amount of pledges they receive from the churches. So basically, I have a form with the controls:
[GOW] which is a yes/no checkbox
[Total Amount Needed]
[Total Client Payments] (calculated field from an underlying query)
[Total Other Funding](calculated field from an underlying query)
[Total Pledges Received](calculated field from an underlying query)
[GOW Amount](the field I need to work with)
[Amount Still Needed]{=[Total Amount Needed]-([Total Client Payments]+[Total Other Funding]+[Total Pledges Received]+[GOW Amount])}

So basically, if [GOW]=no then the field [GOW amount] needs to be set to zero, but if [GOW]=yes, then [GOW Amount]=[Total Pledges Received]

I really hope someone can help me with this. Other than this problem, the database is coming along nicely, and I want to be able to start testing it in real use soon, as they are using a shared excel workbook to track this now and are have tons of problems with it. They can't really afford to hire a professional database designer to do it, so i volunteered (or was volunteered, anyway).

Thanks so much,

Amanda

Amanda,

There are many solutions, its depends of you use a form ore not.

When you use a form then the following code will help you

chkGow is the checkbox that's is bound to the GOW field

If chkGow Then
txtGOWamount.Value = txtTotalPledgesReceived
Else
txtGOWamount.Value = 0

End If
the control txtGOWAnmount is bound to the field = [GOW amount]
the control txtTotalPledgesReceived is bound to the field= [Total Pledges Received]
​

Is it a query
Than you can try the following code

[GOW amount] = IIF([GOW], [Total Pledges Received],0)
​

A tip:
Try to avoid spaces in the names of tables, query's, forms, enz...
Use a underscore of better use the Camel notation example: firstName.

This tip will make your code and filed names easier to use.

 

[aaldridge]

Thanks so much! You are the access guru. ::bows down and grovels::

 

[cvo-aalst]

I am always glad when I can help someone