SELECT statement not working in ADO (SQL). Worked fine with DAO (Access)


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: SELECT statement not working in ADO (SQL). Worked fine with DAO (Access)

Hybrid View

  1. #1
    T. Bradley Dean Guest

    SELECT statement not working in ADO (SQL). Worked fine with DAO (Access)

    Hello all,

    I recently upgraded an app from using DAO with an Access 97 db to ADO with
    SQL 7. This statement is now not working:

    SELECT DISTINCTROW Count(*) AS [Count Of tblPurchaseItems],
    tblPurchaseItems.ItemID, First(tblPurchaseItems.ItemName) AS FirstOfItemName
    FROM tblPurchases INNER JOIN tblPurchaseItems ON tblPurchases.OrderNumber =
    tblPurchaseItems.OrderNumber WHERE (((tblPurchases.OrderStatus)="completed"
    Or (tblPurchases.OrderStatus)="Completed" Or
    (tblPurchases.OrderStatus)="COMPLETED") AND ((tblPurchases.OrderDate)
    Between #08/31/2000# And #09/30/2000#)) GROUP BY tblPurchaseItems.ItemID;

    There are two tables, tblPurchases and tblPurchaseItems. What I need is a
    count of all the items in tblPurchaseItems that have a coresponding order
    that matches OrderStatus=Complete and the date period. I hope that makes
    sense...

    Any ideas?

    Thanks in advace.

    --
    Bradley Dean





  2. #2
    Gonzalo Medina Guest

    Re: SELECT statement not working in ADO (SQL). Worked fine with DAO (Access)


    "T. Bradley Dean" <Bradley.Dean@InfoDish.com> wrote:
    >Hello all,
    >
    >I recently upgraded an app from using DAO with an Access 97 db to ADO with
    >SQL 7. This statement is now not working:
    >
    >SELECT DISTINCTROW Count(*) AS [Count Of tblPurchaseItems],
    >tblPurchaseItems.ItemID, First(tblPurchaseItems.ItemName) AS FirstOfItemName
    >FROM tblPurchases INNER JOIN tblPurchaseItems ON tblPurchases.OrderNumber

    =
    >tblPurchaseItems.OrderNumber WHERE (((tblPurchases.OrderStatus)="completed"
    >Or (tblPurchases.OrderStatus)="Completed" Or
    >(tblPurchases.OrderStatus)="COMPLETED") AND ((tblPurchases.OrderDate)
    >Between #08/31/2000# And #09/30/2000#)) GROUP BY tblPurchaseItems.ItemID;
    >
    >There are two tables, tblPurchases and tblPurchaseItems. What I need is

    a
    >count of all the items in tblPurchaseItems that have a coresponding order
    >that matches OrderStatus=Complete and the date period. I hope that makes
    >sense...
    >
    >Any ideas?
    >
    >Thanks in advace.
    >
    >--
    >Bradley Dean
    >

    This is a long SQL statement, but I can see the DISTINCTROW, the SQL Server
    does not have keyword. Try to re-do your SQL Statement with DISTINCT.

    Let me know if I'm wrong, we can talk about it.

    Have fun :-)


  3. #3
    Eric Renken Guest

    Re: SELECT statement not working in ADO (SQL). Worked fine with DAO (Access)

    Try changing the "#" around the dates to a single quote. I have noticed
    that SQL doesn't like this, and I have had to change my code to use single
    quotes.

    Eric Renken

    "T. Bradley Dean" <Bradley.Dean@InfoDish.com> wrote in message
    news:39eb4d3c$1@news.devx.com...
    > Hello all,
    >
    > I recently upgraded an app from using DAO with an Access 97 db to ADO with
    > SQL 7. This statement is now not working:
    >
    > SELECT DISTINCTROW Count(*) AS [Count Of tblPurchaseItems],
    > tblPurchaseItems.ItemID, First(tblPurchaseItems.ItemName) AS

    FirstOfItemName
    > FROM tblPurchases INNER JOIN tblPurchaseItems ON tblPurchases.OrderNumber

    =
    > tblPurchaseItems.OrderNumber WHERE

    (((tblPurchases.OrderStatus)="completed"
    > Or (tblPurchases.OrderStatus)="Completed" Or
    > (tblPurchases.OrderStatus)="COMPLETED") AND ((tblPurchases.OrderDate)
    > Between #08/31/2000# And #09/30/2000#)) GROUP BY tblPurchaseItems.ItemID;
    >
    > There are two tables, tblPurchases and tblPurchaseItems. What I need is a
    > count of all the items in tblPurchaseItems that have a coresponding order
    > that matches OrderStatus=Complete and the date period. I hope that makes
    > sense...
    >
    > Any ideas?
    >
    > Thanks in advace.
    >
    > --
    > Bradley Dean
    >
    >
    >
    >




  4. #4
    T. Bradley Dean Guest

    Re: SELECT statement not working in ADO (SQL). Worked fine with DAO (Access)

    Thanks Gonzalo & Eric! You were both right. I had to change DISTINCTROW to
    DISTINCT, I had to change # to ". I also had to take out
    "First(tblPurchaseItems.ItemName) AS FirstOfItemName" (I can live without
    it)

    Thanks again, you guys saved the day!

    --
    Bradley Dean


    T. Bradley Dean <Bradley.Dean@InfoDish.com> wrote in message
    news:39eb4d3c$1@news.devx.com...
    > Hello all,
    >
    > I recently upgraded an app from using DAO with an Access 97 db to ADO with
    > SQL 7. This statement is now not working:
    >
    > SELECT DISTINCTROW Count(*) AS [Count Of tblPurchaseItems],
    > tblPurchaseItems.ItemID, First(tblPurchaseItems.ItemName) AS

    FirstOfItemName
    > FROM tblPurchases INNER JOIN tblPurchaseItems ON tblPurchases.OrderNumber

    =
    > tblPurchaseItems.OrderNumber WHERE

    (((tblPurchases.OrderStatus)="completed"
    > Or (tblPurchases.OrderStatus)="Completed" Or
    > (tblPurchases.OrderStatus)="COMPLETED") AND ((tblPurchases.OrderDate)
    > Between #08/31/2000# And #09/30/2000#)) GROUP BY tblPurchaseItems.ItemID;
    >
    > There are two tables, tblPurchases and tblPurchaseItems. What I need is a
    > count of all the items in tblPurchaseItems that have a coresponding order
    > that matches OrderStatus=Complete and the date period. I hope that makes
    > sense...
    >
    > Any ideas?
    >
    > Thanks in advace.
    >
    > --
    > Bradley Dean
    >
    >
    >
    >




  5. #5
    Danny J. Lesandrini Guest

    Re: SELECT statement not working in ADO (SQL). Worked fine with DAO (Access)

    I have a tool that you can use to reformat Access SQL into
    SQL Server format. It's not perfect, but saves me time and
    catches some characters I sometimes miss.

    It's at my download site and is named SQL Parser.
    --
    Dan Lesandrini
    www.dea.com/datafast/downloads.asp
    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    ODBC Tutorial Utility
    Compact Access DB with VB Script
    Create VB/ADO Class From Stored Procedure
    www.swynk.com/friends/Lesandrini/
    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


    "T. Bradley Dean" <Bradley.Dean@InfoDish.com> wrote in message news:39eb4d3c$1@news.devx.com...
    > Hello all,
    >
    > I recently upgraded an app from using DAO with an Access 97 db to ADO with
    > SQL 7. This statement is now not working:
    >
    > SELECT DISTINCTROW Count(*) AS [Count Of tblPurchaseItems],
    > tblPurchaseItems.ItemID, First(tblPurchaseItems.ItemName) AS FirstOfItemName
    > FROM tblPurchases INNER JOIN tblPurchaseItems ON tblPurchases.OrderNumber =
    > tblPurchaseItems.OrderNumber WHERE (((tblPurchases.OrderStatus)="completed"
    > Or (tblPurchases.OrderStatus)="Completed" Or
    > (tblPurchases.OrderStatus)="COMPLETED") AND ((tblPurchases.OrderDate)
    > Between #08/31/2000# And #09/30/2000#)) GROUP BY tblPurchaseItems.ItemID;
    >
    > There are two tables, tblPurchases and tblPurchaseItems. What I need is a
    > count of all the items in tblPurchaseItems that have a coresponding order
    > that matches OrderStatus=Complete and the date period. I hope that makes
    > sense...
    >
    > Any ideas?
    >
    > Thanks in advace.
    >
    > --
    > Bradley Dean
    >
    >
    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center