How do I update a field on a form automatically using a query an

Resolved · High Priority · Version 2003

Wed 22 Dec 2010, 16:53 Delegate Hayley said...

Hayley has attended:
Access Intermediate course
Access Advanced course

How do I update a field on a form automatically using a query an

Help!!

I have a field within my database that I would like to be updated once another field in the form is updated.

What I need it to do is the following:
The database is basically a log of all paperwork that my department receives from clients. There are different types of paperwork and for each is a specific service level agreement that the paperwork is completed by.

So when the Date Received is entered into the Date Received field, this should then update the Expected Date of Completion field.
type 1 of the paperwork type should be completed in 56 days from the date the paperwork was first received.
type 2 within 10 days
type 3 within 31 days and so on...

Can anyone help me to set this up?

I have tried to create a query but can't seem to get the functions right. Am I even on the right road with this? or should I be trying something else?

I would really appreciate anyone's help with this.
Many thanks
Hayley

Thu 23 Dec 2010, 12:37 Trainer Nafeesa said...

RE: How do I update a field on a form automatically using a quer

Hi Hayley,

Thank you for your question.

The Access trainer will get back to you with a response as soon as possible.

Regards,

Nafeesa

Microsoft Office Specialist Trainer

Mon 19 Sep 2011, 11:50 Trainer Simon said...

RE: How do I update a field on a form automatically using a quer

Hi Hayley,

I am going through the forum questions today trying to resolve any outstanding questions.

Did you manage to resolve the question?

If not, how many types of paperwork do you have?

You could add a new text box on the form which will hold the completion date. Then you would set the Control Source in the Properties using the Expression Builder.

I imagine you would need an IIF function which tested if the Paperworktype field was equal to a certain type, if it does then use the DateAdd function to add the specifed number of days to the Date Received field on the form.

Regards

Simon

Mon 19 Sep 2011, 12:27 Trainer Simon said...

RE: How do I update a field on a form automatically using a quer

Hi Hayley,

I have attached an Access database for you with a formula that tests 8 different PaperWork types and then adds a specified number of days to the Date Received field to auto calculate the completion date depending on which type of paperwork has been specified.

The formula is a nested IIF function using the DateAdd function in the true and false answer.

I am sure there would possibly be a neater or more sophisticated method but that would involve Access VBA coce

I hope this answers your question.

Regards

Simon

Attached files...

ForumAnswerDatabase.mdb

Mon 26 Sep 2011: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Access tip:

How To Find All Overdue Accounts?

To find overdue accounts create a filter that compares today's date with the Invoice Date in the table. To do this:

1. Open the Query in Design View
2. Select the field for the filter and in the criteria row enter:

<Date()

This filter returns records where the Invoice Date is before today's date.

This filter can be manipulated if, for instance, Invoices are due 15 days after the Invoice Date. For this the filter would be:

<Date()-15

This filter returns records where the Invoice Date is 15 days before today's date.

View all Access hints and tips