Data type mismatch in criteria expression?

[Les Isaacs]

Hi All
I have a query which runs fine until I set a criteria (of True) in the field

chase_it: prevwd([practice_bacs_submission_date])<Date()

So without the criteria, I have

SELECT prevwd([practice_bacs_submission_date])<Date() AS chase_it, practice_bacs.practice_bacs_submission_date
FROM practice_bacs
WHERE (((practice_bacs.practice_bacs_submission_date)>#1/31/2013#));

and in the query results I see 0 and -1 as expected for the 'chase_it' expression
BUT
When I add True (or -1, or 0) as a criteria for 'chase_it', I get the "Data type mismatch in criteria expression" error
So the sql that fails is

SELECT prevwd([practice_bacs_submission_date])<Date() AS chase_it, practice_bacs.practice_bacs_submission_date
FROM practice_bacs
WHERE (((prevwd([practice_bacs_submission_date])<Date())=True) AND ((practice_bacs.practice_bacs_submission_date)>#1/31/2013#));

In case it's relevant, my function prevwd is:

Function prevwd(dt As Date) As Date
10        On Error GoTo prevwd_Error
20        dt = dt - 1
30        While Weekday([dt]) = 1 Or Weekday([dt]) = 7 Or IsBankHoliday(dt)
40            dt = dt - 1
50        Wend
60        prevwd = dt
70        On Error GoTo 0
80        Exit Function
prevwd_Error:
90        Call WriteErrors(Erl, Err.number, Err.Description, "prevwd", "Module", "PayrollCalc safe 2000 2001")
End Function

and this function is used extensively and always works perfectly. Before anyone asks, I have tried using DateAdd instead of dt = dt - 1, but that made no difference.
I've tried all sorts, but am now at a loss :banghead:
Hope someone can help.
Many thanks
Les

 

[pr2-eugin]

Try this query !

SELECT practice_bacs.practice_bacs_submission_date
FROM practice_bacs
WHERE (
        (practice_bacs.practice_bacs_submission_date > #01/31/2013#)
        AND
        (prevwd(practice_bacs.practice_bacs_submission_date) < Date())
       );

Also make sure the date is actually a date not Null or String values. That will be a problem !

 

[pbaldy]

Are there any records where the date field is Null? The error may be coming from the function.

 

[bob fitz]

WHERE (((prevwd([practice_bacs_submission_date])<Date())=True) AND...

If prevwd returns a date value and that value is less than today's date, what is the =True for.

 

[Les Isaacs]

Hi Paul, Paul and Bob
Many thanks for your replies.
I have tried Paul's suggested query, but got the same result
I tried adding 'Is Not Null' to the criteria for 'practice_bacs_submission_date':

SELECT practice_bacs.practice_bacs_submission_date
FROM practice_bacs
WHERE (((practice_bacs.practice_bacs_submission_date) Is Not Null And (practice_bacs.practice_bacs_submission_date)>#1/31/2013#) AND ((prevwd([practice_bacs].[practice_bacs_submission_date]))<Date()));

but still same result
Bob: the criteria

prevwd([practice_bacs_submission_date])<Date() = True

is like that because I had the expression

prevwd([practice_bacs_submission_date])<Date()

in the design grid, and then added 'True' to the criteria row for that expression: when switching to sql view, it comes out as

prevwd([practice_bacs_submission_date])<Date() = True

Given that

Function prevwd(dt As Date) As Date

explicitly requires a date as input parameter, and outputs a date, and the field [practice_bacs_submission_date] is a date field and that I am filtering out any records where this date field is empty, I just can't see why access is complaning:banghead:
Is this the 2nd Gates bug I've discovered in as many weeks
If so, I guess I just have to get on witrh my life as Paul suggested last time - but this time I will have to find a workaround
If anyong has any more ideas I'd be so grateful.
Thanks again
Les

 

[pr2-eugin]

Is it possible for you to upload a Stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[Les Isaacs]

Hi Paul
OK - I've done it the other way round - created a new accdb, imported the relevant tables, query and module, deleted most records, anonomised the rest, compacted and uploaded: so now 'query1' is the problem query, for you/others hopefully to play with
Thanks as ever.
Les

 

[pr2-eugin]

In the copy there is no field practice_bacs.practice_bacs_submission_date? Can you make sure you give a copy with relevant information please !

 

[Les Isaacs]

Aagh - sorry Paul - I was obviously carried away deleting things
New db uploaded with that field restored.
Les

 

[pr2-eugin]

I was obviously carried away deleting things

I am sure no one is going to market your code, it would be helpful if you could give us the modules involved with this query ! BankHoliday table is not found ! So it runs in a long and annoying error messages. Could you please test the version before you upload? :banghead:

 

[Les Isaacs]

Pual
I'm really sorry about this: I honestly did test the query in db1, and got the same "Data type mismatch in criteria expression" error - despite not having added the BankHoliday table. Now, having added that table, the query runs fine
I need to investigate the difference between db1 and my live system - I'll post back shortly.
Les

 

[Les Isaacs]

Right!

When I tested the query and got the error, this was before I had deleted most of the records: sorry about that

I now find that if I delete all records from the table practice_bacs where the field 'practice_bacs_submission_date' is empty (for want of a better word!!), the query runs fine.

In the live system I cannot delete these records, but sought to exclude them by having 'Is Not Null' as a criteria for that field - but this seems to to work. I have also tried using the criteria IsDate(practice_bacs_submission_date]) = true, or len([practice_bacs_submission_date]) >0, but neither of these succeeds in filtering out the records where 'practice_bacs_submission_date' is empty.

I had assumed that I could get round this by substituting the table 'practice_bacs' in the query with a pre-query which has 'Is Not Null' as a criteria for that field, but I have tried this, and no joy:banghead:

In the revised db1 that I have uploaded,

• 'query1' is the original attempt,
• 'qry BACS subdates chaseupPreQuery' is the pre-query that successfully filters out the records where 'practice_bacs_submission_date' is empty
• 'query2' is the attempt that uses 'qry BACS subdates chaseupPreQuery' instead of the table 'practice_bacs'.

I think that's everything, and hopefully it's enough for you - or someone - to help.
Thanks again
Les

 

[Les Isaacs]

Hi Folks - just wondering - is anyone still looking at this, or shall I re-post?
Thanks hopefully
Les

 

[pbaldy]

I can avoid the error by declaring the input variables as Variant and adding this to the second function:

  If Not IsDate(dtPayDate) Then
    IsBankHoliday = False
    Exit Function
  End If

Though the resulting query returns no records. I also had to comment out the error handler since the tables don't exist in this db.

 

[Les Isaacs]

Hi Paul
Yep - that seems to have done the trick!!
Very many thanks.
Les

 

[pbaldy]

Happy to help!