-
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
-
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 :-)
-
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
>
>
>
>
-
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
>
>
>
>
-
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
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|