Call a function in an Update Query

[jastek]

1st time trying an update query. . .

I am trying create an update query, which will update a calculated field that shows the number of days between two dates on the same form. I created a public function that performs a DateDiff calculation for the field.

How do I call this function from the update query? I cannot seem to get it to work. The query runs, but it just blanks out the calculated field.

I have attached a sample db showing what I have done.

 

[Mile-O]

One question: why don't you just calculate this in a normal query? It's unnecessary to store, as I'm sure I've said before, a calculated value in a table.

 

[jastek]

Yes, you are right. The field should be unbound.

I do not know why I am using an update query. What is the best way to go about this? I need to be able to update all the records at once, as well as be able to run a report showing the updated values.

 

[Rich]

Why would you need to update the calculation unless you're changing one of the values, in which case the calculated control will update automatically ?

 

[Mile-O]

What is it you need to update? You keep referring to updating something.
As I've read it you only want to calculate something.
As you mention an unbound textbox that implies you want a calculation on a form. A form, however, can only display one record at a time.
If you want to do the calculation in a query then all you need to do is create a new field.

The new field, as an example calculation can be two fields multiplies together: Quantity and Price. The field will be called Total.

i.e.

NewField: [Quantity]*[Price]

And, when the query is run, a new field called Total will appear that does the calculation.

What's your calculation.

 

[jastek]

Because the number of days changes every day. The calculated field not only shows the number of days it took to complete, it also shows the number of days as work in progress if it is not finished.

 

[Mile-O]

So, you have a field also for the date completed?

If so:

=IIf(IsNull([EndDate]), "-",DateDiff("d", [StartDate], [EndDate]))

 

[jastek]

I guess I am not explaining this clearly.

I have a form that has 2 date fields; [dtInitialized] and [dtFinished]).

I also have a field (unbound) that calculates the number of days between [dtInitialized] and [dtFinished]. If there is no entry in [dtFinished], then the calculated field shows the number of days between [dtInitialized] and today.

What I want to happen is for each record that does not have a date in[dtFinished] to be updated to reflect the new number of days between [dtInitialized] and today.

 

[Mile-O]

This will automatically calculate that for you.

=DateDiff("d", [dtInitialized], IIf(IsNull([dtFinished]), Date(), [dtFinished]))

 

[jastek]

Where would I put this code?

 

[Mile-O]

Where would I put this code?

It's not code - it's an expression.

It would go in the ControlSource of a textbox. Or in a query. Or a report. It's up to you.

 

[jastek]

Back to my original question; how would I call a function from a query?

The example I am using in this thread is much more simplified than the db I am working on. The number of days calculation involves 3 date fields and multiple conditions, which has to be done by a function.

In looking at the example used in this thread, how would I put your "EXPRESSION" in a function and call that function from a query, which will be run every time the main form is opened?

 

[Rich]

NewField:YouFunction(Field1,Field2) etc.

 

[jastek]

My form is based on a table and not a query. I know this is not correct, but I started this db before I realized that.

Is there still a way to do this, even if my form is based on a table?

 

[Rich]

It doesn't take two minutes to create a query from your table and switch the forms data source to it, the fields will more or less still be the same

 

[jastek]

OK. I have created the new query and based my form on it, and created a new field that calls the function.

When I open the form, I get an error "You entered an expression that has no value".

What am I doing worng now? I think it has to do with the way I call the function in the query. . .

I have attached the database so you can see what I have done.