Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Unanswered: Type mismatch error in criteria of select query

    I am getting a type mismatch when I try to set a criteria for the intervaltest in the following (sql view) of my query.

    SELECT tblServiceWorkOrders.WorkOrder, tblServiceWorkOrders.DateOpened, tblServiceWorkOrders.TimeOpened, IIf((DateDiff("n",[dateopened] & " " & [timeopened],Now()))>300,True,False) AS IntervalTest, tblServiceWorkOrders.CallStatus, tblServiceWorkOrders.TechnicianAcknowledgment
    FROM tblServiceWorkOrders
    WHERE (((tblServiceWorkOrders.DateOpened) Is Not Null) AND ((tblServiceWorkOrders.TimeOpened) Is Not Null) AND ((IIf((DateDiff("n",[dateopened] & " " & [timeopened],Now()))>300,True,False))=False) AND ((tblServiceWorkOrders.CallStatus)<>"Closed") AND ((tblServiceWorkOrders.TechnicianAcknowledgment)=F alse))

    I have tried a number of things including using the datediff and the integer returned by that value as the criteria, iif statements with true/false - which is what I normally like to do, adding int(), adding cbool(), and a few others all to no avail.

    I am quite certain I have done this (or something very like it) in the past; I am wondering if there is some kind of setting or or other that could be causing it.

    Attached is a jpg of the select query view. Any ideas?

    PS, if I do a valtype() I can see that it is returning an integer (when I use int) or boolean (when I use cbool), but then I still get the error.

    PPS, if in my iif statement I return "old" and "new" depending on the values of the datediff, I definately return strings, but if I try to set any criteria, say to "new" I get a type mismatch.
    Attached Thumbnails Attached Thumbnails Queryview.JPG  
    Last edited by jdostie; 08-22-07 at 19:21.

  2. #2
    Join Date
    Oct 2003
    OK, so it was simple, it did not like the way I combined date and time, though in every other way it worked fine (datediff worked fine etc).

    It seems the "correct way" to combine date and time is just to add them. I should have known this, but somehow it did not seem right to me. And having tried it the way I had "seemed to work" . . .

    Thanks anyway.

Posting Permissions

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