|
Subject:
|
Access problem ªª
|
|
Posted By:
|
love_spy74
|
Post Date:
|
10/30/2006 3:16:25 PM
|
Hi...im receive the next message when i was running a program...I acces to SQL with VB
ERROR '-2147217900(80040e14)
The number name 'quantitydbo.BCSMF_SalesPerson.agentfirstnamedbo.BCSM_SPERSON> contain more than the maximum number of prefixes. the maximum is 3
the code is next
rsorders.Open "select BCSMF_Orders.orderdate,BCSMF_Orders.orderid,dbo.BCSMF_Ordersdetails.productID" & _
"productprice,quantity" & _
"dbo.BCSMF_SalesPerson.agentfirstname" & _
"dbo.BCSMF_SPersonxSCenter.ScenterID" & _
"dbo.BCSMF_SupplyingCenter.ScenterName" & _
"dbo.BCSMF_SupplyingCenter.CityID" & _
"dbo.BCSMF_City.CityName" & _
"dbo.BCSMF_Customers.StateID" & _
"dbo.BCSMF_state.StateName" & _
"From dbo.BCSMF_Orders" & _
"dbo.BCSMF_OrdersDetails" & _
"dbo.BCSMF_SalesPerson" & _
"dbo.BCSMF_SPersonxSCenter" & _
"dbo.BCSMF_SupplyingCenter" & _
"dbo.BCSMF_City" & _
"where signname is null " & _
"and orderdate BETWEEN #" & Format(CldInicial.Value, "MM/DD/YYYY") & "#" & _
"and #" & Format(CldFinal.Value, "MM/DD/YYYY") & "#" & _
"and dbo.BCSMF_Orders.orderid=dbo.BCSMF_Ordersdetails.orderid" & _
"and dbo.BCSMF_SalesPerson.SPersonID=dbo.BCSMF_Orders.SPersonID" & _
"and dbo.BCSMF_Orders.SPersonID=dbo.BCSMF_SPersonxSCenter.SPersonID" & _
"and dbo.BCSMF_SPersonxSCenter.ScenterID = dbo.BCSMF_SupplyingCenter.ScentrID" & _
"and dbo.BCSMF_SupplyingCenter.CityID= dbo.BCSMF_City.CityID" & _
"order by orderdate", MASTER, adOpenDynamic, adLockOptimistic, adCmdText
|
|
Reply By:
|
gbianchi
|
Reply Date:
|
10/30/2006 3:46:45 PM
|
hi there.. you forgot all the "," between the names of the fields ;)
"dbo.BCSMF_SalesPerson.agentfirstname, " & _
"dbo.BCSMF_SPersonxSCenter.ScenterID, " & _
"dbo.BCSMF_SupplyingCenter.ScenterName, "
and so on...
HTH
Gonzalo
|
|
Reply By:
|
love_spy74
|
Reply Date:
|
10/30/2006 4:59:24 PM
|
Sorry this isnt te solution....
|
|
Reply By:
|
gbianchi
|
Reply Date:
|
10/31/2006 8:24:06 AM
|
ok.. this is the solution, but not the only problem..
you have problems with spaces inside the string, for example:
"and dbo.BCSMF_Orders.orderid=dbo.BCSMF_Ordersdetails.orderid" & _
"and dbo.BCSMF_SalesPerson.SPersonID=dbo.BCSMF_Orders.SPersonID" & _
this line with produce this string:
and dbo.BCSMF_Orders.orderid=dbo.BCSMF_Ordersdetails.orderidand dbo.BCSMF_SalesPerson.SPersonID=dbo.BCSMF_Orders.SPersonID
look that is has a space missing betwenn orderid and the word "and"... the & will not add any space nor , nor anything to the string, it will just paste one string to the other.
you have also missing "," in the form clause, the from, where and order are not separated by space from they previous line, and I'm not sure about this, but i think you cannot use the "is null" in the where, you have to use isnull function (but not sure about this)....
so separate each field with ,
leave spaces between each name of field...
HTH
Gonzalo
|
