Data Type Mismatch

[GraeagleBill]

I'm closing this thread because an acceptable solution is found in support of the app. However, the solution to the OP is not really satisfied in that there's no reason found as to why the Boolean return from the functions does not work.

[pbaldy]

Did you verify that there were no Null or invalid dates?

[Welshgasman]

Easy enough to filter out Null dates I would have thought?, or use the NZ() function?

[pbaldy]

Sure, but I didn't see the issue being raised.

[Welshgasman]

It is puzzling that the code of the function works directly in the query?
I would be trying the NZ() or looking for bad data.
The data set could have changed since it worked directly in the query?

[Micron]

Sure, but I didn't see the issue being raised.

Surely someone edited their post then, because I started to suggest that but before posting I went back and reviewed. I seem to recall that based on that review I deleted my post because it had been addressed. Here's what I saw when I hit the restore button:


I also could not get an error in the immediate window by comparing ?Left("",5) = Left("",5) (was True) so it didn't seem reasonable that it would raise that error as a returned function value. That it works as a calculated field but not as a function call suggests to me that as a call, it must be returning something the function can't handle, and that certainly could be nulls:
?left(null,5) = left(null,5)
Null
and a boolean cannot be null. I'd use something other than Boolean.

Now I think I should have made that post.

[pbaldy]

Surely someone edited their post then

That's possible. I searched for "Null" in the thread and didn't see it prior to posting.

[Edgar]

First reply in this thread is me saying the data is wrong if the function is right. OP seems to have not done any data validation test so far, so, my suggestion remains: check the underlying data. If it's too much data, then check it out by chunks until any chunk throws an error and then keep isolating and reducing until the record(s) with invalid data is found.

[GraeagleBill]

A lot of re-tracing here trying to answer WHY the "Birthday" function failed. So, here's what happened. I have several apps, two of which are of a similar nature, quite old I might add. One of the apps has a feature that had been requested to be included in the second. Some code from a general module got copied from the 1st app to the 2nd app. The 1st app had a Public Function "Birthday", but the 2nd app, unbeknown to me, also had a Public Function "Birthday". The difference was that the functions, though similar, performed different functions and where one returned Boolean, the other returned String. It all went down-hill from there. The worst part of it all is the amount of time I wasted on all those that followed the thread. I am terribly sorry.

[Welshgasman]

I thought a compile highlighted that fact?
I am sure I have copied some code over and then found I l aready had it in another module?

[GraeagleBill]

I thought a compile highlighted that fact?

Yes, normally one would rely on that, but those of us that have unique ways of screwing things up know how to avoid that.

[pbaldy]

Compile?!? We don't need no stinking compile!!

[Micron]

Are you saying that
a) you never ran a compile when this started (assuming you didn't copy the code over)
b) you never put a break point on the code with the intention of examining the data type of the function's return value? This would have been a dead give away because your never would have encountered the boolean function break point if instead the string function ran. Troubleshooting 101, day 1.

I for one am amazed that you didn't get "Ambiguous name detected" error regardless. Unless you called the wrong function name (not realizing there is a slight difference) but posted code from the correct one I can't see how that error didn't arise at run time. I think I'd be importing everything into a new db if that is the case. I could be wrong, but I can't see how Access didn't raise that error at run time, even without a compile.