How to create an Iif field in Access 2013

[buick1910]

I have an Access 2013 database file with many fields. One of them, I'll call it Field 3, is a box which I would like to either leave blank or put a check mark in it. I would like to be able to create an Iif field for Field 3 where a check mark is automatically inserted in Field 3 IF Field 1 - Field 2 <or= 0, otherwise I'd like Field 3 to remain empty. How do I do this?



I'm very new to Access so please try and make your explanation clear for a newbie. Thanks

[ItsMe]

The simple answer is

Me.chkAnswer = IIf((Me.txtTwo.Value - Me.txtOne.Value <= 0), -1, 0)

Here you would have a form. On the form you would have an unbound CheckBox named "chkAnswer". You would also have two other controls that are bound to a field. One, named txtTwo and the other named txtOne.

This example will work fine as long as there is a value in both txtOne and txtTwo. You just need to find a good place/event to place the code.

[buick1910]

ItsMe
Thank you for your very prompt reply but unfortunately I'm still at a loss. When I said that I was a newbie I wasn't kidding. I have been sent the Access file and am comfortable with inputting data and looking at the various forms, queries, etc. but I have never created a "form" nor do I have any idea as to where I would find a "good place/event to place the code". Thanks for trying to help - I really do appreciate it.

[ItsMe]

No problem. The fact of the matter is I can not offer more code or direction without first understanding more about your situation.

Basically, the code there will do the math and change a check box on a form to either be checked as "Yes" or checked as "No". Like you mentioned, you do not know where to put the code. Now we have to determine a good place.

What are the default values in your Field 1 and Field 2?

If the user did not change either of these fields or the fields simply = 0 in the current record, do you still want the "Result" to be displayed in the checkbox?

[buick1910]

Boy you are quick. Both Field 1 and Field 2 are fields with data inputted by the user. Field 1 is actually called "Total Fees" and Field 2 is called "Amount Paid". Field 3 is called "Paid in Full" and is just a check box. At the present time all 3 of these fields have to be entered by the user but, what I'm trying to accomplish is having the check mark automatically inserted in the Paid in Full box when the Amount Paid is equal to or exceeds the Total Fees.

In answer to your last question if the Total Fees = 0 I do not want the "Result" displayed but if if the Total Fees >0 and Amount Paid >= 0 I do want the Result displayed in the checkbox.

Hopefully this answers your questions.

[ItsMe]

A basic rule of them for databases is not to store calculated values. So, you would not want to store a clients running balance in a table. Having said that, it is not the worst thing in the world to have a Boolean or Yes/No field in a table to indicate if the account is "Paid in Full". The problem is ensuring that the table reflects the correct information.

Odds are there is more than one way to view, edit, and add data to the table. Any one of these actions can affect weather or not the account is paid in full. Storing this calculated value requires that you revise its value for EVERY event that may affect its value.

The next problem comes if you are trying to display the balance in a continuous form or in a form during data sheet view. Unbound controls don't work so well in these circumstances.

In a standard form in form view, the code I provided will work well in a form's current event. You can go to the form's property window and click the "Event" tab. There, you will see a field named "Current". Click the ellipses (...). This will open the VBA editor. A new "Current" sub will be available for you to place the IIf statement into.

Sorry for the long and drawn out answers/questions. There is some chance for goofing things up when working with DB's. Often times things that seem to be a small issue are instead, rather involved.

[buick1910]

Thanks - I'll give your suggestoin a try. I'm going out right now so I won't be able to work on this for a few hours. I'll get back to you with my success or failure.

[buick1910]

I was so curious to see if I could get things to work I decided to delay leaving and try your suggestion.

When I went into the form's property window, selected the checkbox field and then clicked on the "Event" tab I did not find a field named "Current". There was a field called "On Click" with the ellipses (...) so I tried entering the IIF function found below in the On Click location. When I clicked on the (...) and box named Choose Builder opened up which gave me three choices and I choose the middle one which said Expression Builder.

The IIF function I entered was as follows:

IIF([TotalFees] - [AmountPaidTextBox] <=0, -1,0)

I then saved the layout, closed the file and opened it again. Following that I entered an Amount Paid value to be the same as the Total Fees for a particular record hoping that a Check Mark would be inserted against Paid in Full. Unfotrunately it didn't appear, the Check Box remained empty. Where did I go wrong?

Sorry I'm so uninformed about all this but I must admit I find Access very confusing. I've worked with Filemaker for a number of years and find it far more intuitive. Probably because I'm used to it :-)

[ItsMe]

The event I mentioned is associated with the form itself, not a control. And I took a look and it is at the top and is "On Current". So, if you select the puldown you can switch from the form's properties to the name of a control, to another control, and back to the form. Each item has its own "Property"

.


edit: And I also forgot to mention that you need to choos the "Code Builder", which is the third option. Sorry...

Placing the code in the form's "On Current" event will fire the code when you navigate from one record to another or open the form.

Now that I understand you will be updating information, you may want to place the code in multiple areas. You can put it in the form's "On Current" event and also the after update event for each of the two text boxes you are entering data into. After you lose focus or move the cursor out of the textbox, the after update event will fire (so long as the data was changed).

If the CheckBox is bound to a field in a table, it will not be necesary to place it in the form's "On Current" event.

[buick1910]

Thanks - I'll give it a try and see how things go.