[AccessD] Access to SQL

Be careful when using the converter of its tendency to convert numbers
to BIT types.  Eventually, someone will put a null value in the field,
and Access gets mad.  Also, make sure if you are using SQL 2000 backend
with an Access 2003 front end that you watch out for the bug that
updates the wrong records in subforms if you base your query on a form.
With the nasty dates, I convert them in Access before updating to SQL to
avoid those headaches. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
Foust
Sent: Thursday, August 18, 2005 11:27 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Access to SQL

There is no DISTINCTROW keyword in SQL because you don't need it.  Just
drop that from your SQL statement and check the results.

Charlotte Foust


-----Original Message-----
From: Johncliviger at aol.com [mailto:Johncliviger at aol.com]
Sent: Thursday, August 18, 2005 4:40 AM
To: accessD at databaseadvisors.com
Subject: [AccessD] Access to SQL


Hi all
 
I've moved Access XP FE to ADP FE and the tables to SQL 2000 BE. I now
find 
that Select DistinctRow thats in Access XP has no equivalent in SQL
2000. 
What is the equivalent syntax?
 
for example have the following sql string works in Access without a
problem. 
Load it into ADP ...and its a no no.
 
Any suggestions?
 
SELECT DISTINCTROW ApplicantPersonalDetails.*,
OrderDetails.CourseCounter, 
OrderDetails.OrderID, Orders.ClientID,  Orders.OrderDate, 
Orders.InvoiceRequired, ClientPurchaser.ClientCode,
ClientPurchaser.Purchasers, 
ClientPurchaser.FirstName, ClientPurchaser.LastName,
PostBasicCourseDetails.CourseID, 
PostBasicCourseDetails.Date,  PostBasicCourseDetails.Course, 
PostBasicCourseDetails.Level,  PostBasicCourseDetails.Location,
PostBasicCourseDetails.Code,  
PostBasicCourseDetails.Length, PostBasicCourseDetails.Places,  
PostBasicCourseDetails.CourseLeader1,
PostBasicCourseDetails.CourseLeader2,  
PostBasicCourseDetails.Cost, PostBasicCourseDetails.Selection

FROM          PostBasicCourseDetails INNER  JOIN
(((ClientPurchaser INNER  JOIN
Orders ON ClientPurchaser.ClientID = Orders.ClientID) INNER  JOIN
OrderDetails ON Orders.OrderID = OrderDetails.OrderID) INNER  JOIN
ApplicantPersonalDetails ON OrderDetails.OrderDetailID =  
ApplicantPersonalDetails.OrderDetailID) ON  
PostBasicCourseDetails.CourseCounter = OrderDetails.CourseCounter;
 
Regards
johnc
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com