Data type mismatch in criteria expression 1

[gdervage]

Hi,
I hope someone can help with this.

I am calling the following function as an expression field in a access query.

This is the BOM function
Function BOM(dteInput As Date) As Date
' input - a date
' return - a date equal to the first of the month for the input date
'Written July 2001 by Guy Dervage 178738

Dim intDayPart As Integer

intDayPart = CInt(Day(dteInput))
BOM = DateAdd("d", -(intDayPart - 1), [dteInput])

End Function

This is my calling line.
expateAdd("m",13,(BOM([APD_DOI])
So far so good. Every thing works as expected. If I try to use <Now() as a criteria I get an error dialog box which says that there is a data type mismatch in the criteria expression.

However this expression-
exp2ateAdd(&quot;m&quot;,13,[APD_DOI])
works fine with the criteria of <Now().

If I put in an expression exp3:IsDate(BOM([APD_DOI])
it returns a true (-1).

What is going on? Why doesn't this work?

Thanks,
Guy Dervage

 

[THoey]

Is this due to using NOW () which returns date and time instead of DATE () which just returns the date? Terry M. Hoey

 

[gdervage]

Terry,

Using Date() didn't help. However I copied the query out of the project data base and pasted it into a new data base and Emailed it to my home. At home I replaced the function call in the query field with the following expression:

Expired: DateAdd(&quot;d&quot;,-(DatePart(&quot;d&quot;,[APD_DOI])-1),DateAdd(&quot;m&quot;,13,[APD_DOI]))

This worked with either Now() or Date() in the criteria field. Great I thought. I Emailed the test data base back to work and had my boss put it on the system. Worked there to. Life is good. He then cut the query out of the test data base and copied it into the project data base. Guess what? Data type mismatch error. What can be different about the two .mdb files? The libraries that you select with the 'Tools / References' menu selection were not quite the same. The original .mdb file used Micorsoft DAO 2.51 Object Library while the test .mdb uset the Micorsoft DAO 3.51 Object Library. On my system at home I don't have the Micorsoft DAO 2.51 Object Library what I have is a Micorsoft DAO 2.51 / 3.51 Compatability Object Library. When I use this library on my home system there is no change in the symptoms. Could these libraries be the problem? I hesitate to turn off the 2.51 library on the project data base because I don't want to break other things.

Thank you,

Guy Dervage