Deadline and I need some assistance please

[cnut1]

I created a form using a text boxes by using the field list icon and dragging the field I wanted to make into the text box onto the form. In the properties of that text box, under the format tab I set the format for "short date". Under the data tab in the properties of the text box I put in the control source this forumla =DateSerial(Year([Date of Service]),Month([Date of Service])+10,Day([Date of Service])). "Date of Service" on my form is the name of the source field it calculates from. What the forumla does is calculates from the date entered into my Service Date field and whatever date is in that field it adds 10 months to it. This field with this formula in it caluclates the date of my contacting the customer to setup an annual service for them. I also have another field that calculates the annual service date using the same formula only changing the +10 to +12.

When the Date of Service it entered the Contact Date field automatically adds 10 months to the date entered into the Date of Service field. Same of the Date of Next Service field only instead of 10 months it adds 12. So my formulas are correct. However when I go to my table I can not see any of the caluculated dates entered into the table. I can see it in the datasheet view of my form. My question is how do I create a query to pull only contacts dates from this form for a specific time frame? When I attempt to create a query I can only see table fields which do not contain the formulas for calculating the dates. Or can the fields that calculate the dates on my form be linked so the calculated dates are linked into the table and updated on the table when I update the form?

Keep in mind I am new to access so please give very detailed and specific instructions on a solution for this issue.

 

[ejstefl]

Generally, you do not want to store calculated values in a table, as this violates rules of normalization and creates assorted other problems. If you need to create a query to pull for a specific time frame, you can add a calcualted field to your query.

As an example, you might type DateSerial(Year([Date of Service]),Month([Date of Service])+10,Day([Date of Service])) in the Field row on your query. This will create another field that will add 10 months to your service date. Make sense?

 

[cnut1]

Yes that does make sense and was a huge help. Thanks so much. I was able to update the table using the formula you posted.

I did notice that now when I do the data entry to the form using the "Date of Service" my "Contact Date" and "Date of Next Service" fields are automatically updated. However when I click the update query button I created, (let's say in record 20 on the form) it does not update the current record #20. I have to enter into the next record (21 on the form) do the data entry, click the update query button on record 21. What it does at that point is updates record 20 on the table. However I do have an update table button on my switchboard that is sourced back to the same query and does performs the same function as the button on the form. If I click the update button from the switchboard the table is updated instantly but not from the form. Any ideas on that?

Another question I could use some assistance with...what sort of query and formula would I need to use to query out just particular dates? Example: I want to query out only the contacts dates for June 2006 in the table.

 

[ejstefl]

It sounds like you need to requery your form after your update query. However, I would question what your update query does - you shouldn't be storing the results of the calculation in your table. You should just calculate them as needed. That way, you don't need to worry about running an update query when you change your dates. Search for "normalization" for more on this concept.

To seelct a range of dates in a query, you can use the BETWEEN function in the criteria field. For example, you can type "BETWEEN #6/1/06# AND #6/30/06#" (without the quotes) in the criteria field of Date Of Service to see only records with dates of service in June '06.