--0-2138225802-1009563265=:61488
Content-Type: text/plain; charset=us-ascii


Try your join statment like this
 strSQL = strSQL & "INNER JOIN OrderLines ON CONVERT(char(10),o.Id) = OrderLines.OrderID "

  Michael Bunger <michael@r...> wrote: Hi, A customer of ours runs a website with a shopping cart software.  It uses ASP
and an ACCESS 2000 database.  There is an ORDERS table with PK "id" and an ORDERLINES table with a FK "OrderID".  Believe it or not,
they are defined as different datatypes with id being defined as an Autonumber of type Long Integer and OrderID being defined as
type Text with length 10.  I'm making an upgrade to the sytem and I need to JOIN the two tables.  I've tried many things such as
CASTing and aliasing in the JOIN statement, but I can't seem to get anything to work.  Note that all of the SQL statement code is in
the ASP file.   Any ideas?  Below is a portion of the code I'm trying to get to work.   strSQL = ""
 strSQL = strSQL & "SELECT  "
 strSQL = strSQL & "o.Id "
 strSQL = strSQL & "FROM Orders o "
 strSQL = strSQL & "INNER JOIN OrderLines ON o.Id = OrderLines.OrderID "
 strSQL = strSQL & "WHERE o.Date >= #07/01/2001# "
 Set rs = Server.CreateObject("ADODB.Recordset")
 rs.Open strSQL,Conn,3,3
Note, with this test code, no records are returned (which is my problem); however, if I comment out the line with the INNER JOIN
statement, I get one record returned (which is correct, but I also need related information from the OrderLines table -- which is my
problem). Any suggestions/comments will be very much appreciated. Sincerely,Michael Bunger P.S.:  It looks like from the design of
their software, the never needed to do a JOIN on these fields before.---


---------------------------------
Send your holiday cheer with Yahoo! Greetings.