Subquery and Yes/No field

From: Simon Bowyer (nospam_at_nospam)
Date: 08/25/04

  • Next message: Del Murray: "Re: How to Handle a Timeout error" 
    Date: Wed, 25 Aug 2004 13:37:32 +0100

    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

  • Next message: Del Murray: "Re: How to Handle a Timeout error"