Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Stupmed - Data Type Mismatch In Criteria Expression

    Hello,

    I'm having an issue with a query that previously was working - not sure what I changed to make it not work anymore.

    First, I have a module that creates a function "GetBusinessDay." This is the code for that module:

    Code:
    Function GetBusinessDay(datStart As Date, intDayAdd As Integer)
    On Error GoTo Error_Handler
    
    Dim rst As DAO.Recordset
    Dim DB As DAO.Database
    
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset("SELECT [BMWHoliday] FROM tbl_Holidays", dbOpenSnapshot)
    
    If intDayAdd > 0 Then
        Do While intDayAdd > 0
            datStart = datStart + 1
            rst.FindFirst "[BMWHoliday] = #" & datStart & "#"
            If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday Then
                If rst.NoMatch Then intDayAdd = intDayAdd - 1
            End If
        Loop
        
    ElseIf intDayAdd < 0 Then
    
        Do While intDayAdd < 0
            datStart = datStart + 1
            rst.FindFirst "[BMWHoliday] = #" & datStart & "#"
            If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday Then
                If rst.NoMatch Then intDayAdd = intDayAdd + 1
            End If
        Loop
        
    End If
    
        GetBusinessDay = datStart
        
    
    Exit_Here:
        rst.Close
        Set rst = Nothing
        Set DB = Nothing
        Exit Function
        
    Error_Handler:
        MsgBox Err.Number & ": " & Err.Description
        Resume Exit_Here
    End Function
    Here is the query. The field 30Day calculates 30 working days, with a where expression (>=Date())to narrow down the results to only those records equal to today or in the future.

    I have started getting a Data Type Mismatch In Criteria Expression error for this where statement. I have tried everything I can think of, but from what I can tell the module should keep it as a date field but it is becoming text, so I am comparing text to date. I tried using a datevalue on the field but I still get the error. Tried using an If statement to change it to string, and still get an error. Help please!

    Here is the query:

    Code:
    SELECT qry_EmployeeDbList.EmployeeName, qry_EmployeeDbList.HireDate, GetBusinessDay([HireDate],30) AS 30Day, qry_EmployeeDbList.Position
    FROM qry_EmployeeDbList
    WHERE (((GetBusinessDay([HireDate],30))>=Date()) AND ((qry_EmployeeDbList.Position)<>"Floater"));

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Try declaring a return datatype for the function
    By not specifying the datatype it will be variant.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2009
    Posts
    204
    Changing this

    Code:
    Function GetBusinessDay(datStart As Date, intDayAdd As Integer)
    to this

    Code:
    Function GetBusinessDay(datStart As Date, intDayAdd As Integer) As Date
    had no effect.

    Am I missing something?

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    I take that back, it had an effect...The fieldf now behaves and sorts as a date, but if I try to include the WHERE >=Date() I still get the data type mismatch.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so which line is throwing the error
    are the values supplied to that line sane?

    it could be a problem with the call to the function

    check that the values supplied to the function are of the expected datatype
    make certain Hiredate is a datetime value
    make certain there is a datetime value in it (IE no NULLS) or trap for NULLS in your function

    if the code used to work and you haven't changed version of Access or done soemthing stupid like letting your network trolls update Access for you then its probably a data error
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2009
    Posts
    204
    That was it

    The [HireDate] field was set as date but included Null values
    I filled those with a dummy date and all works again

    I guess in the userform that now needs to be a required field.

    Thanks for your help!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK
    change your data first
    but change your table design to disallow NULLS on HireDate
    change your form (put some code in the hiredate control 's after update event to ensure that a date is supplied

    AND/OR change your function to trap NULL values, and replace at runtime with a 'sensible' default
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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