Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2014
    Posts
    4

    Unanswered: Data type mismatch in criteria expression

    Hello,

    I have created a user defined function that takes start date, start time, end date, and end time that calculates the difference between the start date/time and end date/time to the minute but is returned in units of days as data type double. The function uses a holidays table to exclude weekends and holidays from the calculation. I added a field to a query where the function is called for all the rows of the query (all rows include necessary parameters for the equation). I have tested many of the calculations and they are correct. When I try to add criteria to the new query to find only the rows where the date/time difference is >=3 days I cannot run the query and I get the error "Data type mismatch in criteria expression." I can't quite figure out where the problem is or how to solve it.

    Any help is appreciated, thanks!
    Last edited by blutigr; 09-29-14 at 16:59.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the SQL expression of the query?
    Have a nice day!

  3. #3
    Join Date
    Sep 2014
    Posts
    4
    Here is the SQL

    SELECT HolidayWorkDayDiff([startdate],[enddate],[starttime],[endtime]) AS [Work Day Difference]
    FROM tblDescription RIGHT JOIN (qryDistribution RIGHT JOIN tblErrors ON qryDistribution.Branch = tblErrors.Branch) ON tblDescription.code = tblErrors.code
    GROUP BY tblErrors.enddate, tblErrors.endtime, HolidayWorkDayDiff([startdate],[enddate],[starttime],[endtime])
    HAVING (((tblErrors.enddate) Is Not Null) AND ((tblErrors.endtime) Is Not Null)) OR (((HolidayWorkDayDiff([startdate],[enddate],[starttime],[endtime]))>=3))
    ORDER BY HolidayWorkDayDiff([startdate],[enddate],[starttime],[endtime]) DESC;

  4. #4
    Join Date
    Sep 2014
    Posts
    4

    Function Code

    Hello,

    Here is the code for my function. I am fairly certain that this function is returning a data type Double, which is making me think that the "Data type mismatch in criteria expression" error is happening in the query design.

    Option Compare Database

    Public Function HolidayWorkDayDiff(LoadDate As Date, CloseDate As Date, LoadTime As Date, CloseTime As Date) As Double

    HolidayWorkDayDiff = DateDiff("d", LoadDate, CloseDate) - DateDiff("ww", LoadDate, CloseDate, 1) * 2 + IIf(Weekday(LoadDate, 1) = 7, 1, 0) - DCount("*", "tblHolidays", "[Holiday Date] Between " & Format$(LoadDate, "\#mm\/dd\/yyyy\#") & " And " & Format$(CloseDate, "\#mm\/dd\/yyyy\#"))
    HolidayWorkDayDiff = CDbl((((HolidayWorkDayDiff * 1440) + DateDiff("n", LoadTime, CloseTime)) / 1440))

    'Lines for testing
    'Call HolidayWorkDayDiff(#8/24/2014#, #9/3/2014#, #8:00:00 AM#, #8:00:00 AM#)
    'MsgBox (HolidayWorkDayDiff)

    End Function

  5. #5
    Join Date
    Sep 2014
    Posts
    4

    Function Code

    I created a test table with some example sets of load date, close date, load time, and close time and sent them through the function in the query, and added the criteria I wanted to use and it worked. so now I feel like this might be something wrong with the data I am trying to use. Is it possible that the data could be corrupted? I cannot figure out what is wrong with the set up.
    Last edited by blutigr; 10-02-14 at 15:38.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •