Data Type Mismatch

[GraeagleBill]

I have a query (see design screenshot below) that executes correctly UNTIL I add True or False as the criteria for the "IsToday". All elements of the criteria are Boolean, so what's the beef?

Public Function BirthDay(AnnDate As Date) As Boolean

BTW, the expression seen in the design view for "IsToday:" is used successfully in another of my queries, so I'm not suspicious of the function.

[Edgar]

If you're not suspicious of the function, the underlying data has issues. Try to isolate chunks of data to find the rogue record(s). Once you find that, you can clean up the data.

[GraeagleBill]

If I omit the function value from the criteria, the query runs perfectly and displays the function values as expected. The DB does not seem to be corrupted in any way, so I've not yet found the issue. (The date value set in the record done via Allen's date function)
Screenshot snippet:

[June7]

Try: =-1

Which date function do you mean - SQLDate() found on http://allenbrowne.com/ser-36.html ?

[GraeagleBill]

Yes, I've tried to set criteria with both "0" and "-1" in lieu of TRUE & FALSE but same results.
Allen wrote a calendar function years ago that I continue to use that sets the value of text boxes bound to date fields.

Public Function CalendarFor(txt As TextBox, Optional strTitle As String)
'On Error GoTo Err_Handler
    'Purpose:   Open the calendar form, identifying the text box to return the date to.
    'Arguments: txt = the text box to return the date to.
    '           strTitle = the caption for the calendar form (passed in OpenArgs).
    
    Set gtxtCalTarget = txt
    DoCmd.OpenForm "frmCalendar", windowmode:=acDialog, OpenArgs:=strTitle
    
Exit_Handler:
    Exit Function


Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation, "CalendarFor()"
    Resume Exit_Handler
End Function

I've moved the target records around in the DB such that the query would be operating on different records in the DB but nothing changed.

[June7]

That procedure just opens a form. It is declared as a function but returns nothing. It could just as well be a Sub. Some code behind that form does the real work.

I am not finding anything about this on Allen's website.

What is code for BirthDay() function?

[GraeagleBill]

Doesn't amount to much:

Public Function BirthDay(AnnDate As Date) As Boolean
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  We just need to isolate and compare the month and day based on
'  sobriety date versus today's date.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
BirthDay = False
If left(Format(AnnDate, "mm/dd/yyyy"), 5) = left(Format(Date, "mm/dd/yyyy"), 5) Then BirthDay = True
End Function

I could just as well put the code (adjusted) directly into the query.

[June7]

Could. And that would be informative.

I tested this function with my date field and it works with True/False criteria.

[GraeagleBill]

I agree, and I'll do that as soon as I can. Family awaits at the moment.

[GraeagleBill]

Putting the test directly into the query design view works properly.

What puzzles me the most is that I have another query that uses the function return as criteria without any issues.

[Welshgasman]

Why are you even bothering around with the year?

? format(date,"mm/dd")
10/09

[GraeagleBill]

No need, force of habit I guess.

IIf((Left(Format([SOBRTYDATE],"mm/dd"),5)=Left(Format(Date(),"mm/dd"),5)),-1,0)

One, of course, needs the parentheses pair with the Date function or Access thinks it's a missing record field.

[pbaldy]

Test email notifications for Bill.

[GraeagleBill]

Notification went in the spam folder.

[pbaldy]

The first rule you learn in IT is "reboot". The second is "check your spam folder".