Problem with sub query

  • Thread starter Thread starter Simon Bowyer
  • Start date Start date
S

Simon Bowyer

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. Why is Access not returning a yes/no field?

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
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First off, this is not the correct syntax for an Access/JET query. It
looks more like the SQL in an Oracle stored procedure.

That said, the question arises: "Are you using an ODBC connection to the
Access db?" If so, the ODBC connection might be converting the column
value from -1 to 255 to conform to a TINYINT data type. TINYINTs are
byte values and don't have negative values, only 0 to 255.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQTZLLIechKqOuFEgEQJLJQCcDJXPkHsdtiuA3SbwJaz347hPUwgAn38R
ovUtpZSXootGssDUNxs+u+sJ
=kqih
-----END PGP SIGNATURE-----
 
Thanks for the reply. What is it about it that makes it not the correct
syntax? It works fine apart from the wrong data type. My application uses
ADO but the same problem occurs if I run the query in the query builder in
Access. We have converted the Access MDB using the upsize wizard to a SQL
Server database and the query works as expected.

Thanks
Simon
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Using variables like the following is not Access/JET nor MS SQL Server
SQL syntax (probably a better phrase would be "variable declarations"):

WHERE ...
AND CreatedOn >= :StartDate and CreatedOn <= :EndDate

Access/JET & SQL'r don't identify variables using a preceding colon (:).

Access variables are declared in a PARAMETER statement, like this:

PARAMETER myID Long;
SELECT ...
FROM ...
WHERE ID = myID

SQL'r uses variables like this (preceding @ symbol):

DECLARE @myID INTEGER -- or it is a parameter in a stored procedure

SELECT ...
FROM ...
WHERE ID = @myID

====

Re-reading your original post: Are you using Access to read data from
an SQL'r db? It sounds like you're using SQL'r to read data from
Access/Jet tables!?

Access Yes/No values can be -1 (true) and 0 (false). SQL'r uses 1 for
True and 0 for False. The BIT data type is only available in Access/JET
version 4.0; and, only if you've set the ANSI 92 option on (Tools >
Options > Table/Query > SQL Server Compatible Syntax (ANSI 92)).

HTH,

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQTdv1IechKqOuFEgEQLYJACfToZhULQ91WBFg464kQ2bmHlJs7IAnArO
VHwe10ligGZun6sHizZzaKVC
=5ujm
-----END PGP SIGNATURE-----
 
Back
Top