BorlandTalk.com

Simon Bowyer · Guest

Using the query below I am getting a strange result in the sub query on line
7. It returns an integer rather than a yes/no data type when run against an
Access database. The MessageRead field is a yes/no but is returning 255 if
set to yes. When using a SQL Server backend it returns the correct bit
datatype. We want our application to be able to use either so I need the
results to be consistant. I get a data type mismatch error at the moment as
the persistant field is a TBooleanField and I'm using AsBoolean.

Thanks for any help
Simon

SELECT DISTINCT qryMessageOutlook.MessageId, qryMessageOutlook.Importance,
qryMessageOutlook.HasAttachments, qryMessageOutlook.EnquiryId,
qryMessageOutlook.EnquiryNumber, qryMessageOutlook.CustomerName,
qryMessageOutlook.SiteName, qryMessageOutlook.Subject,
qryMessageOutlook.iEnquiryId, qryMessageOutlook.CreatedBy,
qryMessageOutlook.CreatedOn, qryMessageOutlook.Initials,
qryMessageOutlook.EmployeeName, qryMessageOutlook.ActionedBy,
qryMessageOutlook.ActionedOn, qryMessageOutlook.ActionedByName,
qryMessageOutlook.Action, qryMessageOutlook.Deleted,
qryMessageOutlook.DeletedBy, qryMessageOutlook.DeletedOn,
(SELECT MessageRead FROM MessageRecipients WHERE MessageId =
qryMessageOutlook.MessageId AND EmployeeId = :UserId) AS MessageRead
FROM qryMessageOutlook
LEFT OUTER JOIN qryMessageRecipientsActual
ON qryMessageRecipientsActual.MessageId = qryMessageOutlook.MessageId
WHERE
(qryMessageRecipientsActual.EmployeeId = :UserId2
OR qryMessageRecipientsActual.EmployeeId IN
(SELECT LinkedEmployeeId FROM EmployeeMessageSecurity
WHERE EmployeeMessageSecurity.EmployeeId = :UserId3
AND ViewMessages = -1))
AND
(
(CreatedOn >= :StartDate and CreatedOn <= :EndDate)
OR
(Action is null)
OR
(MessageRead = 0)
)
AND
(
Deleted = 0
)
ORDER BY qryMessageOutlook.CreatedOn DESC