SQL Question

8 Mar 2006 4:25 PM

Pinto1uk

Hi, i posted a question a few weeks ago, regarding a report i wanted to
create that should show the products sold within the current month only. i am
using data reports within VB6, and using the recordset object to create the
report.

the problem i am having is with the sql statement. Somone from my previous
reply (thank you very much for your help) suggested the following statements:

SELECT Product.ProductID, Product.ProductName,
OrderItem.Quantity, Orders.`Date`
FROM OrderItem
INNER JOIN Product ON OrderItem.ProductID = Product.ProductID
INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') = MONTH
(GETDATE())

but with this sql, i am getting the following error:

syntax error: missing operator in query expression 'INNER JOIN Product ON
OrderItem.ProductID = Product.ProductID
INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID'

also, does vb recognise the the where clause:

WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') = MONTH
(GETDATE())

can anyone help.
regards

8 Mar 2006 4:33 PM

Bob Butler

Show quote Hide quote

"Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
news:F775D8B6-C7BD-42DA-B15C-CC114EC0A93C@microsoft.com
> Hi, i posted a question a few weeks ago, regarding a report i wanted
> to create that should show the products sold within the current month
> only. i am using data reports within VB6, and using the recordset
> object to create the report.
>
> the problem i am having is with the sql statement. Somone from my
> previous reply (thank you very much for your help) suggested the
> following statements:
>
> SELECT Product.ProductID, Product.ProductName,
> OrderItem.Quantity, Orders.`Date`
> FROM OrderItem
> INNER JOIN Product ON OrderItem.ProductID = Product.ProductID
> INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
> WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date')
> = MONTH (GETDATE())
>
> but with this sql, i am getting the following error:
>
> syntax error: missing operator in query expression 'INNER JOIN
> Product ON OrderItem.ProductID = Product.ProductID
> INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID'

What is the backend database? SQL Server, Access/Jet, Oracle, ??

> also, does vb recognise the the where clause:
>
> WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date')
> = MONTH (GETDATE())

VB doesn't recognize any of it; VB puts the text of the query into a String
and sends the whole thing off to the database engine to figure it out.

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

8 Mar 2006 4:55 PM

Pinto1uk

Hi, the back end database is access. if vb does not recognise the statements,
how do i achieve my objectives?

regards

Show quoteHide quote

"Bob Butler" wrote:

> "Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
> news:F775D8B6-C7BD-42DA-B15C-CC114EC0A93C@microsoft.com
> > Hi, i posted a question a few weeks ago, regarding a report i wanted
> > to create that should show the products sold within the current month
> > only. i am using data reports within VB6, and using the recordset
> > object to create the report.
> >
> > the problem i am having is with the sql statement. Somone from my
> > previous reply (thank you very much for your help) suggested the
> > following statements:
> >
> > SELECT Product.ProductID, Product.ProductName,
> > OrderItem.Quantity, Orders.`Date`
> > FROM OrderItem
> > INNER JOIN Product ON OrderItem.ProductID = Product.ProductID
> > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
> > WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date')
> > = MONTH (GETDATE())
> >
> > but with this sql, i am getting the following error:
> >
> > syntax error: missing operator in query expression 'INNER JOIN
> > Product ON OrderItem.ProductID = Product.ProductID
> > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID'
>
> What is the backend database? SQL Server, Access/Jet, Oracle, ??
>
> > also, does vb recognise the the where clause:
> >
> > WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date')
> > = MONTH (GETDATE())
>
> VB doesn't recognize any of it; VB puts the text of the query into a String
> and sends the whole thing off to the database engine to figure it out.
>
> --
> Reply to the group so all can participate
> VB.Net: "Fool me once..."
>
>

8 Mar 2006 5:20 PM

Bob Butler

"Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
news:A1858FC5-E83F-4586-A1D1-0194B49EE0D6@microsoft.com
> Hi, the back end database is access. if vb does not recognise the
> statements, how do i achieve my objectives?

Jet gets upset if you do multiple joins without using () around the first
join; you also need to lose the tick marks around Date since that's for
mysql, not jet; finally, Jet doesn't recognize getdate(), that's sql server.
try this:

dim s as string
s="SELECT Product.ProductID, Product.ProductName," & _
"OrderItem.Quantity, Orders.Date " & _
"FROM (OrderItem INNER JOIN [Product] ON OrderItem.ProductID =
Product.ProductID) " & _
"INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID " & _
"WHERE YEAR(Orders.Date) = " & Cstr(Year(Date)) & _
" AND MONTH(Orders.Date) = " & cstr(Month(Date))

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

8 Mar 2006 4:35 PM

JP Bless

Are you using DAO or ADO. ADO barks like a hell when you use keywords as
field name without enclosing the feildname in brackets. So If Date is a
field which I assume it is then enclose Date in brackets like [Date] not in
quotes.

Show quoteHide quote

"Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
news:F775D8B6-C7BD-42DA-B15C-CC114EC0A93C@microsoft.com...
> Hi, i posted a question a few weeks ago, regarding a report i wanted to
> create that should show the products sold within the current month only. i
am
> using data reports within VB6, and using the recordset object to create
the
> report.
>
> the problem i am having is with the sql statement. Somone from my previous
> reply (thank you very much for your help) suggested the following
statements:
>
> SELECT Product.ProductID, Product.ProductName,
> OrderItem.Quantity, Orders.`Date`
> FROM OrderItem
> INNER JOIN Product ON OrderItem.ProductID = Product.ProductID
> INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
> WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') =
MONTH
> (GETDATE())
>
> but with this sql, i am getting the following error:
>
> syntax error: missing operator in query expression 'INNER JOIN Product ON
> OrderItem.ProductID = Product.ProductID
> INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID'
>
> also, does vb recognise the the where clause:
>
> WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') =
MONTH
> (GETDATE())
>
> can anyone help.
> regards
>
>

8 Mar 2006 4:56 PM

Pinto1uk

I am using ADO, and date is a data field. but the error is poiting towards
the inner join statements.
regards

Show quoteHide quote

"JP Bless" wrote:

> Are you using DAO or ADO. ADO barks like a hell when you use keywords as
> field name without enclosing the feildname in brackets. So If Date is a
> field which I assume it is then enclose Date in brackets like [Date] not in
> quotes.
>
> "Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
> news:F775D8B6-C7BD-42DA-B15C-CC114EC0A93C@microsoft.com...
> > Hi, i posted a question a few weeks ago, regarding a report i wanted to
> > create that should show the products sold within the current month only. i
> am
> > using data reports within VB6, and using the recordset object to create
> the
> > report.
> >
> > the problem i am having is with the sql statement. Somone from my previous
> > reply (thank you very much for your help) suggested the following
> statements:
> >
> > SELECT Product.ProductID, Product.ProductName,
> > OrderItem.Quantity, Orders.`Date`
> > FROM OrderItem
> > INNER JOIN Product ON OrderItem.ProductID = Product.ProductID
> > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
> > WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') =
> MONTH
> > (GETDATE())
> >
> > but with this sql, i am getting the following error:
> >
> > syntax error: missing operator in query expression 'INNER JOIN Product ON
> > OrderItem.ProductID = Product.ProductID
> > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID'
> >
> > also, does vb recognise the the where clause:
> >
> > WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') =
> MONTH
> > (GETDATE())
> >
> > can anyone help.
> > regards
> >
> >
>
>
>

8 Mar 2006 5:11 PM

JP Bless

Have a look at this

SELECT Product.ProductID, Product.ProductName,
OrderItem.Quantity, [Orders.Date], (DatePart('m',[Orders.Date]) As
CurrentMonth,

(DatePart('yyyy', [Orders.Date]) As CurrentYear FROM OrderItem
INNER JOIN Product ON OrderItem.ProductID = Product.ProductID
INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID

WHERE DatePart('m',[Orders.Date]=" & DatePart('m',Date) & " AND
DatePart('yyyy',[Orders.Date] = " & DatePart('yyyy',Date)

Show quoteHide quote

"Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
news:27D7296B-94EE-40BA-9901-EF34E3BBB115@microsoft.com...
> I am using ADO, and date is a data field. but the error is poiting towards
> the inner join statements.
> regards
>
> "JP Bless" wrote:
>
> > Are you using DAO or ADO. ADO barks like a hell when you use keywords as
> > field name without enclosing the feildname in brackets. So If Date is a
> > field which I assume it is then enclose Date in brackets like [Date] not
in
> > quotes.
> >
> > "Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
> > news:F775D8B6-C7BD-42DA-B15C-CC114EC0A93C@microsoft.com...
> > > Hi, i posted a question a few weeks ago, regarding a report i wanted
to
> > > create that should show the products sold within the current month
only. i
> > am
> > > using data reports within VB6, and using the recordset object to
create
> > the
> > > report.
> > >
> > > the problem i am having is with the sql statement. Somone from my
previous
> > > reply (thank you very much for your help) suggested the following
> > statements:
> > >
> > > SELECT Product.ProductID, Product.ProductName,
> > > OrderItem.Quantity, Orders.`Date`
> > > FROM OrderItem
> > > INNER JOIN Product ON OrderItem.ProductID = Product.ProductID
> > > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
> > > WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') =
> > MONTH
> > > (GETDATE())
> > >
> > > but with this sql, i am getting the following error:
> > >
> > > syntax error: missing operator in query expression 'INNER JOIN Product
ON
> > > OrderItem.ProductID = Product.ProductID
> > > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID'
> > >
> > > also, does vb recognise the the where clause:
> > >
> > > WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') =
> > MONTH
> > > (GETDATE())
> > >
> > > can anyone help.
> > > regards
> > >
> > >
> >
> >
> >

8 Mar 2006 5:26 PM

Bob Butler

"JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
news:%23DVynNtQGHA.5400@TK2MSFTNGP09.phx.gbl
> Have a look at this
>
> SELECT Product.ProductID, Product.ProductName,
> OrderItem.Quantity, [Orders.Date], (DatePart('m',[Orders.Date]) As
> CurrentMonth,
>
> (DatePart('yyyy', [Orders.Date]) As CurrentYear FROM OrderItem
> INNER JOIN Product ON OrderItem.ProductID = Product.ProductID
> INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID

I've never gotten it to work without parens
FROM (OrderItem
INNER JOIN Product ON OrderItem.ProductID = Product.ProductID)
INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID

> WHERE DatePart('m',[Orders.Date]=" & DatePart('m',Date) & " AND
> DatePart('yyyy',[Orders.Date] = " & DatePart('yyyy',Date)

The "m" and "yyyy" there need to have double quotes or VB is going to
complain

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

8 Mar 2006 5:41 PM

JP Bless

the single quote(s) signifies part of database field formula like
ssql = "Select format([Order.Date], 'mm/dd/yyyy') from tbl_Order Where "
& "format([Order.Date], 'mm/dd/yyyy') " & format(Date, "mm/dd/yyyy")

This will go through since the single quotes are passed on literally
to the database (to be resolved by the database) while the double quote in
the later
part of format function will be resolved by VB...

Thanks anyway.

Where I used single quote means there is encapsulating outer double quotes.

Show quoteHide quote

"Bob Butler" <tiredofit@nospam.com> wrote in message
news:%23WltyVtQGHA.1572@tk2msftngp13.phx.gbl...
> "JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
> news:%23DVynNtQGHA.5400@TK2MSFTNGP09.phx.gbl
> > Have a look at this
> >
> > SELECT Product.ProductID, Product.ProductName,
> > OrderItem.Quantity, [Orders.Date], (DatePart('m',[Orders.Date]) As
> > CurrentMonth,
> >
> > (DatePart('yyyy', [Orders.Date]) As CurrentYear FROM OrderItem
> > INNER JOIN Product ON OrderItem.ProductID = Product.ProductID
> > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
>
> I've never gotten it to work without parens
> FROM (OrderItem
> INNER JOIN Product ON OrderItem.ProductID = Product.ProductID)
> INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
>
> > WHERE DatePart('m',[Orders.Date]=" & DatePart('m',Date) & " AND
> > DatePart('yyyy',[Orders.Date] = " & DatePart('yyyy',Date)
>
> The "m" and "yyyy" there need to have double quotes or VB is going to
> complain
>
> --
> Reply to the group so all can participate
> VB.Net: "Fool me once..."
>

8 Mar 2006 6:11 PM

Bob Butler

"JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
news:OJ1SaetQGHA.4384@tk2msftngp13.phx.gbl
> the single quote(s) signifies part of database field formula like
> ssql = "Select format([Order.Date], 'mm/dd/yyyy') from tbl_Order
> Where " & "format([Order.Date], 'mm/dd/yyyy') " & format(Date,
> "mm/dd/yyyy")
>
> This will go through since the single quotes are passed on literally
> to the database (to be resolved by the database) while the double
> quote in the later
> part of format function will be resolved by VB...
>
> Thanks anyway.

I realize that but you also posted code that uses ' as a string delimiter
for a VB function that is NOT part of the SQL text:

>>> WHERE DatePart('m',[Orders.Date]=" & DatePart('m',Date) & " AND
>>> DatePart('yyyy',[Orders.Date] = " & DatePart('yyyy',Date)
>>
>> The "m" and "yyyy" there need to have double quotes or VB is going to
>> complain

The first and third DatePart are being passed to SQL; the second and fourth
are being evaulated by VB

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

8 Mar 2006 6:16 PM

JP Bless

Thanks Bob... you are right... thought the OP will figure the quotes since
his original post did not have right quotes anyway.

Show quoteHide quote

"Bob Butler" <tiredofit@nospam.com> wrote in message
news:%23Y2UBvtQGHA.1264@TK2MSFTNGP10.phx.gbl...
> "JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
> news:OJ1SaetQGHA.4384@tk2msftngp13.phx.gbl
> > the single quote(s) signifies part of database field formula like
> > ssql = "Select format([Order.Date], 'mm/dd/yyyy') from tbl_Order
> > Where " & "format([Order.Date], 'mm/dd/yyyy') " & format(Date,
> > "mm/dd/yyyy")
> >
> > This will go through since the single quotes are passed on literally
> > to the database (to be resolved by the database) while the double
> > quote in the later
> > part of format function will be resolved by VB...
> >
> > Thanks anyway.
>
> I realize that but you also posted code that uses ' as a string delimiter
> for a VB function that is NOT part of the SQL text:
>
> >>> WHERE DatePart('m',[Orders.Date]=" & DatePart('m',Date) & " AND
> >>> DatePart('yyyy',[Orders.Date] = " & DatePart('yyyy',Date)
> >>
> >> The "m" and "yyyy" there need to have double quotes or VB is going to
> >> complain
>
> The first and third DatePart are being passed to SQL; the second and
fourth
> are being evaulated by VB
>
> --
> Reply to the group so all can participate
> VB.Net: "Fool me once..."
>

8 Mar 2006 5:24 PM

JP Bless

Another "crude method" especially since you are using access

sSQL = "SELECT Product.ProductID, Product.ProductName,
OrderItem.Quantity, [Orders.Date], Format([Order.Date],'mm/yyyy') AS
CurrentMonth FROM OrderItem INNER JOIN Product ON OrderItem.ProductID =
Product.ProductID
INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID

WHERE Format([Order.Date],'mm/yyyy') ='" & Format(Date, "mm/yyyy") & "'"

Show quoteHide quote

"Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
news:27D7296B-94EE-40BA-9901-EF34E3BBB115@microsoft.com...
> I am using ADO, and date is a data field. but the error is poiting towards
> the inner join statements.
> regards
>
> "JP Bless" wrote:
>
> > Are you using DAO or ADO. ADO barks like a hell when you use keywords as
> > field name without enclosing the feildname in brackets. So If Date is a
> > field which I assume it is then enclose Date in brackets like [Date] not
in
> > quotes.
> >
> > "Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
> > news:F775D8B6-C7BD-42DA-B15C-CC114EC0A93C@microsoft.com...
> > > Hi, i posted a question a few weeks ago, regarding a report i wanted
to
> > > create that should show the products sold within the current month
only. i
> > am
> > > using data reports within VB6, and using the recordset object to
create
> > the
> > > report.
> > >
> > > the problem i am having is with the sql statement. Somone from my
previous
> > > reply (thank you very much for your help) suggested the following
> > statements:
> > >
> > > SELECT Product.ProductID, Product.ProductName,
> > > OrderItem.Quantity, Orders.`Date`
> > > FROM OrderItem
> > > INNER JOIN Product ON OrderItem.ProductID = Product.ProductID
> > > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
> > > WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') =
> > MONTH
> > > (GETDATE())
> > >
> > > but with this sql, i am getting the following error:
> > >
> > > syntax error: missing operator in query expression 'INNER JOIN Product
ON
> > > OrderItem.ProductID = Product.ProductID
> > > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID'
> > >
> > > also, does vb recognise the the where clause:
> > >
> > > WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') =
> > MONTH
> > > (GETDATE())
> > >
> > > can anyone help.
> > > regards
> > >
> > >
> >
> >
> >

8 Mar 2006 5:55 PM

Pinto1uk

Hi, thank you guys for all your help. much apprecaited. i have inserted this
SQL solution given, but when running the program, i get the following error:

run time error: '2147217900'

syntax error (missing operartor)

OrderItem.ProductID =
Product.ProductID
INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID

any ideas?

sorry for the troubles

Show quoteHide quote

"JP Bless" wrote:

> Another "crude method" especially since you are using access
>
>
>
> sSQL = "SELECT Product.ProductID, Product.ProductName,
> OrderItem.Quantity, [Orders.Date], Format([Order.Date],'mm/yyyy') AS
> CurrentMonth FROM OrderItem INNER JOIN Product ON OrderItem.ProductID =
> Product.ProductID
> INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
>
> WHERE Format([Order.Date],'mm/yyyy') ='" & Format(Date, "mm/yyyy") & "'"
>
>
>
>
>
>
>
> "Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
> news:27D7296B-94EE-40BA-9901-EF34E3BBB115@microsoft.com...
> > I am using ADO, and date is a data field. but the error is poiting towards
> > the inner join statements.
> > regards
> >
> > "JP Bless" wrote:
> >
> > > Are you using DAO or ADO. ADO barks like a hell when you use keywords as
> > > field name without enclosing the feildname in brackets. So If Date is a
> > > field which I assume it is then enclose Date in brackets like [Date] not
> in
> > > quotes.
> > >
> > > "Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
> > > news:F775D8B6-C7BD-42DA-B15C-CC114EC0A93C@microsoft.com...
> > > > Hi, i posted a question a few weeks ago, regarding a report i wanted
> to
> > > > create that should show the products sold within the current month
> only. i
> > > am
> > > > using data reports within VB6, and using the recordset object to
> create
> > > the
> > > > report.
> > > >
> > > > the problem i am having is with the sql statement. Somone from my
> previous
> > > > reply (thank you very much for your help) suggested the following
> > > statements:
> > > >
> > > > SELECT Product.ProductID, Product.ProductName,
> > > > OrderItem.Quantity, Orders.`Date`
> > > > FROM OrderItem
> > > > INNER JOIN Product ON OrderItem.ProductID = Product.ProductID
> > > > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
> > > > WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') =
> > > MONTH
> > > > (GETDATE())
> > > >
> > > > but with this sql, i am getting the following error:
> > > >
> > > > syntax error: missing operator in query expression 'INNER JOIN Product
> ON
> > > > OrderItem.ProductID = Product.ProductID
> > > > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID'
> > > >
> > > > also, does vb recognise the the where clause:
> > > >
> > > > WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') =
> > > MONTH
> > > > (GETDATE())
> > > >
> > > > can anyone help.
> > > > regards
> > > >
> > > >
> > >
> > >
> > >
>
>
>

8 Mar 2006 6:09 PM

Bob Butler

Show quote Hide quote

"Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
news:A78660EE-16D7-4001-B289-E2399DC543C7@microsoft.com
> Hi, thank you guys for all your help. much apprecaited. i have
> inserted this SQL solution given, but when running the program, i get
> the following error:
>
> run time error: '2147217900'
>
> syntax error (missing operartor)
>
> OrderItem.ProductID =
> Product.ProductID
> INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
>
> any ideas?

I think you need to postthe exact code that builds and executes the query

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

8 Mar 2006 6:11 PM

Pinto1uk

Hi, thanks alot guys for all your help. i managed to solve the problem using
bob's solution.

regards

Show quoteHide quote

"Pinto1uk" wrote:

> Hi, thank you guys for all your help. much apprecaited. i have inserted this
> SQL solution given, but when running the program, i get the following error:
>
> run time error: '2147217900'
>
> syntax error (missing operartor)
>
> OrderItem.ProductID =
> Product.ProductID
> INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
>
> any ideas?
>
> sorry for the troubles
>
> "JP Bless" wrote:
>
> > Another "crude method" especially since you are using access
> >
> >
> >
> > sSQL = "SELECT Product.ProductID, Product.ProductName,
> > OrderItem.Quantity, [Orders.Date], Format([Order.Date],'mm/yyyy') AS
> > CurrentMonth FROM OrderItem INNER JOIN Product ON OrderItem.ProductID =
> > Product.ProductID
> > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
> >
> > WHERE Format([Order.Date],'mm/yyyy') ='" & Format(Date, "mm/yyyy") & "'"
> >
> >
> >
> >
> >
> >
> >
> > "Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
> > news:27D7296B-94EE-40BA-9901-EF34E3BBB115@microsoft.com...
> > > I am using ADO, and date is a data field. but the error is poiting towards
> > > the inner join statements.
> > > regards
> > >
> > > "JP Bless" wrote:
> > >
> > > > Are you using DAO or ADO. ADO barks like a hell when you use keywords as
> > > > field name without enclosing the feildname in brackets. So If Date is a
> > > > field which I assume it is then enclose Date in brackets like [Date] not
> > in
> > > > quotes.
> > > >
> > > > "Pinto1uk" <Pinto***@discussions.microsoft.com> wrote in message
> > > > news:F775D8B6-C7BD-42DA-B15C-CC114EC0A93C@microsoft.com...
> > > > > Hi, i posted a question a few weeks ago, regarding a report i wanted
> > to
> > > > > create that should show the products sold within the current month
> > only. i
> > > > am
> > > > > using data reports within VB6, and using the recordset object to
> > create
> > > > the
> > > > > report.
> > > > >
> > > > > the problem i am having is with the sql statement. Somone from my
> > previous
> > > > > reply (thank you very much for your help) suggested the following
> > > > statements:
> > > > >
> > > > > SELECT Product.ProductID, Product.ProductName,
> > > > > OrderItem.Quantity, Orders.`Date`
> > > > > FROM OrderItem
> > > > > INNER JOIN Product ON OrderItem.ProductID = Product.ProductID
> > > > > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
> > > > > WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') =
> > > > MONTH
> > > > > (GETDATE())
> > > > >
> > > > > but with this sql, i am getting the following error:
> > > > >
> > > > > syntax error: missing operator in query expression 'INNER JOIN Product
> > ON
> > > > > OrderItem.ProductID = Product.ProductID
> > > > > INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID'
> > > > >
> > > > > also, does vb recognise the the where clause:
> > > > >
> > > > > WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') =
> > > > MONTH
> > > > > (GETDATE())
> > > > >
> > > > > can anyone help.
> > > > > regards
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >