Data Type Mismatch in Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know there are about 5,000 posts about this but it's Friday and my brain
has shut off. Here is the code for the function:

Public Function Letter(strBOB As String, strOutcome As String) As String
Dim strBOBType As String

strBOBType = DLookup("AccountType", "tblAccounts", "Account= '" & strBOB &
"'")

If strBOBType = "HP" Then
Letter = "Yes"
ElseIf strOutcome = "ABD" Or Right(strOutcome, 3) = "ABD" Or
Right(strOutcome, 11) = "ABD w/ Auth" Then
Letter = "Yes"
Else
Letter = "No"
End If

End Function

The code works on its own but it will not allow me to limit it to "Yes" in a
query.
 
Is Account a numeric field or a text field? If it's numeric, remove the
single quotes you're putting around strBOB
 
I think that 'datatype mismatch' just means that you need to make sure
that the datatypes are the same

if it's an integer on one side; it needs to be an integer on the other
 
Oh, are you trying to use this in a query?

What are you putting as the criteria: "yes" in quotes, or no quotes? Since
you're passing a string, you need the quotes.

Alternatively, you could change the declaration to

Public Function Letter(strBOB As String, strOutcome As String) As Boolean

and set Letter to True or False (no quotes)

However, you'd likely be better joining tblAccounts to the other table and
use SQL to limit the code, rather than pass values to a function each time.
 
The Boolean didn't work either. I know very little about SQL and don't even
know where to being with that.
 
What's the actual SQL of your query? (If you're not familiar with SQL, open
the query in design view, and select View | SQL View from the menu to see
what SQL Access has generated for you)
 
SELECT tblAll.DueDate, tblAll.Doctor, tblAll.ScheduleDate, tblAll.Time,
tblAll.Accounts, tblAll.Review, tblAll.MemberName, tblAll.MemberID,
tblAll.FacilityMD, tblAll.ContactNumber, tblAll.[2ndContact], tblAll.Outcome,
tblAll.Situs, tblAll.Appeal, tblAll.LetterSent, tblAll.ReceivedDate,
IIf(Weekday([DueDate])=7,DateAdd("d",-1,[DueDate]),IIf(Weekday([DueDate])=1,DateAdd("d",-2,[DueDate]),[DueDate]))
AS MM, Letter([Accounts],tblAll!Outcome) AS Expr1
FROM tblAll INNER JOIN tblAccounts ON tblAll.Accounts = tblAccounts.Account
ORDER BY tblAll.Appeal, tblAll.DueDate;
 
What are you using as a criterion? Yes or "Yes"? The former is an alias for
the Boolean value True (-1); the latter is a text string, which is what your
function returns.

John W. Vinson [MVP]
 
You're already including tblAccounts in the query, therefore it should
already know the AccountType for each entry.

To only get those rows for which your function returns "yes", you should be
able to use:

SELECT tblAll.DueDate, tblAll.Doctor, tblAll.ScheduleDate, tblAll.Time,
tblAll.Accounts, tblAll.Review, tblAll.MemberName, tblAll.MemberID,
tblAll.FacilityMD, tblAll.ContactNumber, tblAll.[2ndContact],
tblAll.Outcome,
tblAll.Situs, tblAll.Appeal, tblAll.LetterSent, tblAll.ReceivedDate,
IIf(Weekday([DueDate])=7,DateAdd("d",-1,[DueDate]),IIf(Weekday([DueDate])=1,DateAdd("d",-2,[DueDate]),[DueDate]))
AS MM
FROM tblAll INNER JOIN tblAccounts ON tblAll.Accounts = tblAccounts.Account
WHERE tblAccounts.AccountType = "HP"
OR tblAll!Outcome LIKE "*ABD"
OR tblAll!Outcome LIKE "*ABD w/ Auth"
ORDER BY tblAll.Appeal, tblAll.DueDate;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Melissa said:
SELECT tblAll.DueDate, tblAll.Doctor, tblAll.ScheduleDate, tblAll.Time,
tblAll.Accounts, tblAll.Review, tblAll.MemberName, tblAll.MemberID,
tblAll.FacilityMD, tblAll.ContactNumber, tblAll.[2ndContact],
tblAll.Outcome,
tblAll.Situs, tblAll.Appeal, tblAll.LetterSent, tblAll.ReceivedDate,
IIf(Weekday([DueDate])=7,DateAdd("d",-1,[DueDate]),IIf(Weekday([DueDate])=1,DateAdd("d",-2,[DueDate]),[DueDate]))
AS MM, Letter([Accounts],tblAll!Outcome) AS Expr1
FROM tblAll INNER JOIN tblAccounts ON tblAll.Accounts =
tblAccounts.Account
ORDER BY tblAll.Appeal, tblAll.DueDate;


Douglas J. Steele said:
What's the actual SQL of your query? (If you're not familiar with SQL,
open
the query in design view, and select View | SQL View from the menu to see
what SQL Access has generated for you)
Click to expand...
Click to expand...
 
Back
Top