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

[T. Bradley Dean]

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

[Gonzalo Medina]

"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 :-)

[Eric Renken]

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
>
>
>
>

[T. Bradley Dean]

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
>
>
>
>

[Danny J. Lesandrini]

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
>
>
>
>