Error: You entered an expression that has no value

[jastek]

I am trying to use a function in a query to calculate a field's value. When I open the form, which is based on the query, I get the error "You entered an expression that has no value"

I think my syntax in the query grid may be incorrect:

DayCountResult: CountDaysToFinish()


Here is the function:

Public Function CountDaysToFinish()

If (IsNull(Forms!Main!dtFinished) Or Forms!Main!dtFinished = "") Then
Forms!Main!DaysToFinish = DateDiff("d", Forms!Main!dtInitialized, Now)

ElseIf (Not IsNull(Forms!Main!dtFinished) Or Forms!Main!dtFinished <> "") Then
Forms!Main!DaysToFinish = DateDiff("d", Forms!Main!dtInitialized, Forms!Main!dtFinished)
End If

End Function

The db is attached, in case you want to see what I have.

Any help?

 

[Mile-O]

As I've already said on another thread, you are approaching this the wrong way. I've put the expression I gave you on the other thread into a query and that's it.

 

[Rich]

Your Function isn't structured correctly
Public Function CountDaysToFinish(DteA As Variant,DteB As Variant) As Integer
etc

 

[jastek]

Mile-O-Phile -

The example db I posted is much more simplified than my actual db. The if statement is much more complicated and needs to be done in a function.

Assume, for the pursposes of this thread, that I cannot simply put the calculation directly in the query grid and I must use a function. How would I do that?

 

[Mile-O]

If you give me a clue as to what all the other complications are I'll have a go at just writing the function.

 

[jastek]

Rich -

I was typing my reply at the same time you were, so I did not see your post. I will try it as you indicated.

Thanks.

 

[jastek]

Rich - That did not work. That gave me a whole new set of errors.

Mile-O-Phile - I have extracted the relavent parts of my actual db and set up a db showing what I have (Attached).

If you can fix this, that would be great. You may find that I have totally screwed this up and there is no way to do what I want, which is to calculate the value for the LostDays field.

If nothing else, this problem has confirmed for me that I have a long way to go before I can tackle a project of this scope.

Thanks for taking the time to have a go at this.