Solved

vb6/sql Incorrect syntax near the keyword ‘and’

Posted on 2014-02-21
2
1,209 Views
Last Modified: 2014-02-21
I encounter the following error...

Run-time error ‘-2147217900 (80040e14)’:
Incorrect syntax near the keyword ‘and’.

Here's the code...

Select item.Inv_Units_Pack as UnitsPack, Sum(item.QOH) as TotQty, Sum(item.QOH) as Allocated, 0 as UnAllocated
from rpr_dev.dbo.item With (NOLOCK)
left outer join field_option_detail
on item.fo_allocation = field_option_detail.id_field_option_detail
inner join user_comp_permission
on item.id_company = user_comp_permission.id_company
WHERE id_item_header = 11354
Group BY item.inv_units_Pack and user_comp_permission.id_user = 37

 Any Ideas on what is causing this error?

Thanks in advance
0
Comment
Question by:tesla764
2 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39877053
>Group BY item.inv_units_Pack and user_comp_permission.id_user = 37
There are two problems with the above statement:
1  Separate columns with commas , and not the word AND
2  I don't see id_user in the SELECT clause, so it can't be in the GROUP BY.   Also, if your intent is to filter the return sets with only the id_user = 37, than that belongs in the WHERE clause.
Select item.Inv_Units_Pack as UnitsPack, Sum(item.QOH) as TotQty, Sum(item.QOH) as Allocated, 0 as UnAllocated 
from rpr_dev.dbo.item With (NOLOCK) 
left outer join field_option_detail 
on item.fo_allocation = field_option_detail.id_field_option_detail 
inner join user_comp_permission 
on item.id_company = user_comp_permission.id_company 
WHERE id_item_header = 11354 and user_comp_permission.id_user = 37
Group BY item.inv_units_Pack 

Open in new window

0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 250 total points
ID: 39877054
I think you have this bit muddled:
WHERE id_item_header = 11354 
Group BY item.inv_units_Pack and user_comp_permission.id_user = 37

Open in new window

Should probably be:
WHERE id_item_header = 11354  and user_comp_permission.id_user = 37
Group BY item.inv_units_Pack 

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question