checkbox dependent IIf function

[jadira]

Hello. I'm needing some help with getting a field to produce either a "0" if a checkbox is marked or calculating a number. Here's the code I tried

IIf( [Never Arrived] , "Yes", "0", "No" = ([End of Billing Cycle]-[Begin Billing Date]))

I'm using information put into a form that then enters into a table. This code I have in a field called Length of Stay. I'm wanting to enter a 0 days if my check box is clicked, and if the check box is not clicked then I want to calculate the actual length of stay.

[ItsMe]

You are working in a form? Why not use an if then else statement?

If Me.CheckBoxName.Value = -1 then
'It is checked
Else if Me.CheckBoxName.Value = 0 then
'It is not checked

else
'It is Null
End if

[jadira]

This part is in a table. I've also tried:

IIf([Never Arrived],"Yes",then = "0", IIf([Never Arrived],"No"=([End of Billing Cycle]-[Begin Billing Date]))

Just started using ACCESS about a week ago.

[ItsMe]

Usually you would use an IIf() function in a query or a form. If you feel more comfortable working only in tables, you may be better served using Excel for this project. The general rule in Access is to use forms and reports as an interface for Users. So, store data in tables, use queries to support forms and reports, use forms as a graphical user interface to the data, and use reports to present historical and statistical data.

[jadira]

I have a checkbox in a form to indicate never arrived, but I'm just having difficulty translating the information I want to convey into my table correctly. I want to be able to make the value 0 in another column on my table if the checkbox is clicked on the form. if it's not, then I want it to subtract end date from begin date to indicate a length of stay.

[ItsMe]

I uploaded an example where a form is bound to a table. On there form are two controls that are bound to this table. One of the controls is a TextBox Control. The other control is a Checkbox control. If you click the Checkbox and change its value, the TextBox control Value changes also.

Behind the form is some VBA that manages the AfterUpdate Event that is triggered when the User changes the value of the Checkbox control.

Open the form in Design View.

You can view the code behind the form by opening the VBA editor. The keyboard shortcut to open the VBA editor is Ctrl+G. Another way to open the editor is to click the ellipses (...) next to the appropriate event of the appropriate control. The Properties sheet has an Event tab where you can find the various events for a given control.

.

[jadira]

maybe it would help if you saw this....I may just be over-complicating things

[ItsMe]

It looks like you built out an expression in your event for one of your checkboxes. Maybe VBA would be easier. Check out my example. It should not be too hard to understand once you look at the code.

[jadira]

turns out all i needed was to a simple code in my length of stay column

IIf([Never Arrived],0,([End of Billing Cycle]-[Begin Billing Date]))

I guess I should follow the KISS rule. (Keep It Simple Stupid)