Macro or Formula?

[data808]

Let me start by saying I am very new to Access and am trying to learn as I go. I am not sure if what I need is a macro or formula.



I have a table with records. I created a form to reflect and to enter more records because these records were exported from another database program called Alpha version 4. I now have the form designed structurally the way I want it. In this form I have some date and number fields that represent the amount of days a person will be on a trip. The clerk will enter this information, however, for the date fields, if possible, I would like these to auto-fill the date of when they will return.

Example:

In field A I will enter the date of departure (1/7/2014). In field B I will enter (90) which is the amount of days I will be gone. Then in field C I would like it to auto-fill the date of when I will return which is (3/7/2014) 90 days later. So basically I want it to calculate from the start date to however many days I enter to give me the end date of when I will return.

Any help in how to get this done would be great. Thank you for your time in advance.

[June7]

Are you saving the number of days into table? If so, it is not necessary to save the end date into table. Calculate it whenever needed: [StartDate] + [DaysGone] - 1. The -1 is to account for the StartDate as day 1.

If you must save the calculated value, will require code (macro or VBA). I use only VBA.

[data808]

Yes I am saving the number of days. How would I go about doing the calculation along with making some kind of field box that displays the end date?

[rpeare]

on your form you'd just have an unbound control that would be

=[StartDate] + [DaysGone] - 1

in a query you'd have

ReturnDate: [StartDate] + [DaysGone] -1

[data808]

What is an unbound control and where do type this code in the form?

[June7]

An unbound control is a data control (textbox, combobox, listbox, checkbox) that does not have its ControlSource property set to a field of form RecordSource. It has an expression instead.

Access Help has info about using expressions in controls and queries.

[rpeare]

an unbound control is a textbox or other object that is not tied directly to a data source

SO if your form is based on a table, if you just added a textbox that was NOT coming from your field list it should say UNBOUND on it in design view.

From there it's no different than any other text box or control you can refer to it by name, you'll notice in your BOUND controls the control source of the text box will reflect the name of a field within your query or form
if you want a formula instead in your unbound control that's where you'd put it.

[data808]

Ok I got it to work. The problem I am having now is that some of the fields are not filled in on the older records which I transferred over. Is there a way to assign the field to a default value such as 0 if nothing is entered so that the sum calculation can process the data to give the auto-fill end date? I don't want to have to go through all the old records just to enter a zero in the fields that weren't entered to give me the end date. Right now the end date will only appear if all fields are filled in.

[ItsMe]

You can adjust the properties of a field's default value at the table level while in design view. Alternatively, you can affect the value a field in a table by setting the properties of a bound control. In other words, bind a control to a field. Adjust that control's default value. Now, when you open the form or navigate to a new record within the form's recordset, the associated field will update or populate using the default value of the control.

Having said that, you can not assign 0 to a date field. When a new record is created, a date/time field will remain as null until after you initialize it with a valid date. So, you will have to test for null to decipher whether or not the record's date field has been updated.

There may be an instance or two that a default value of, say, 01/01/1900 would be appropriate. If doing this does not offer an obvious advantage, stick with null.

[data808]

I need it to hold the value of 0 if nothing is entered into the field. The date fields are ok. I'm not concerned with those. Its the number fields that I may or may not enter a number into it. So if I don't enter a number, I would like it to be 0 so the calculation can finish without me entering anything into the number field. The calculation is one of the date fields that will give me the date of when I will return after it sums the date of departure plus the number of days I will be gone.

[June7]

If you show 0 in the number of days field then the ReturnDate will calculate and subtract 1 from the StartDate. If there is a StartDate entered then shouldn't the number of days be a minimum of 1? Can set the number textbox DefaultValue property to 1.

If StartDate is 1/5/2014 and days gone is 6 (1/5/2014 - 1/10/2014), do you want the calculated ReturnDate to be the last day gone (1/10/2014) or the following day (1/11/2014)? Answer determines whether the -1 should be in the expression.

[rpeare]

if you want the value of a field to be 0 if there is no data you can also use the nz function

for instance:

ItemTotal: nz(Qtyx, 0) * nz(Qtyy, 0)

the NZ function will assume 0 if QtyX or QtyY are unavailable (null)

You don't have to populate the table with 0's to get your calculations to produce a result.