Data type mismatch in criteria expression

[chaver]

I have an SQL query that brings up the error message. I am an experienced programmer but this one has me mystified. Here is a simple copy of the SQL statement that works OK:

SELECT SalesOrderLineItems.Despatched, findWKNo([Despatched]) AS WK
FROM SalesOrderLineItems
WHERE (((SalesOrderLineItems.Despatched) Is Not Null));

Despatched is a date field and findWKNo is a function that returns the week number as an integer from the date. This statement works fine showing both the date and the week number. If I now limit the Week number to less than 53 as in the following statement I get the error message.

SELECT SalesOrderLineItems.Despatched, findWKNo([Despatched]) AS WK
FROM SalesOrderLineItems
WHERE (((SalesOrderLineItems.Despatched) Is Not Null) AND ((findWKNo([Despatched]))<53));

WK is displayed as a number but does not accept the numeric restriction.
I have tried adding the limit to WK on a second query based on the first SQL statement but it still gives the error message.

Any suggestions please?

 

[plog]

My guess is that Dispatched contains Null values and it is screwing up fndWKNo.

Let me respond for you:

"But I have the Is Not Null criteria on Despatched"

To which I reply:

Yes, but that's not how WHERE clauses work. They aren't evaluated in the chronological order in which they appear. Since you now have findWkNo in the WHERE its going to be evaluated no matter what Despatched is.

So, the solution is to either modify fndWkNo to return -1 for invalid data. Or build another query on top of the one you have where you apply Wk<53 criteria

 

[chaver]

I have already put a trap in the function to check for a Null Date - there are none. I have also used the first query as the input to a second query with <53 and that fails.

Mystified.

 

[sneuberg]

Please post the code of findWKNo

 

[Galaxiom]

What have you defined as the data type of the return value for findWKNo()?
What does it do if it receives a Null parameter?

If you have not defined a return datatype then it will return a Variant. If the Null parameter (or anything else it can't handle) cause the function to simply exit without setting a return value then that Variant will be Empty, which is Null.

 

[chaver]

Hi

Here is the findWKNo function

Public Function findWKNo(Dat As Date) As Integer
Dim xs As Variant

If IsNull(Dat) Then
findWKNo = -1
Exit Function
End If

xs = Int((Dat - Weekday(Dat, vbMonday) + 11 - DateValue("jan 01," & Year(Dat + 4 - Weekday(Dat, vbMonday)))) / 7)

If IsNull(xs) Then
findWKNo = -1
Else
findWKNo = xs
End If

End Function

------------------

I have created the following as Query3

SELECT SalesOrderLineItems.Despatched, findwkno([Despatched]) AS WK
FROM SalesOrderLineItems
WHERE (((SalesOrderLineItems.Despatched) Is Not Null))
ORDER BY findwkno([Despatched]);

There are nearly 5000 records and WK contains a positive integer value throughout i.e. there are no Null or negative values.

I then created Query4

SELECT Query3.Despatched, Query3.WK
FROM Query3
WHERE (((Query3.WK)<52))
ORDER BY Query3.WK;

This query still returns the same error.

 

[Galaxiom]

Public Function findWKNo(Dat As Date) As Integer
Dim xs As Variant
    
    If IsNull(Dat) Then

A Date argument cannot accept a Null parameter. Only a Variant can be Null.

 

[chaver]

Hi Galaxiom

By golly I think you have solved it!

I have changed the top of the function to read as follows:

Public Function findWKNo(ByVal Dat1 As Variant) As Integer
Dim xs As Variant
Dim Dat As Date

If IsNull(Dat1) Then
findWKNo = -1
Exit Function
End If

Dat = Dat1

and the problem has gone away.

Thanks for your help
chaver