Access problem ÂªÂª

love_spy74 · October 30th, 2006, 04:16 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.B CSM_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.BC SMF_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.o rderid" & _
       "and dbo.BCSMF_SalesPerson.SPersonID=dbo.BCSMF_Orders.S PersonID" & _
       "and dbo.BCSMF_Orders.SPersonID=dbo.BCSMF_SPersonxSCent er.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
:(

gbianchi · October 30th, 2006, 04:46 PM

hi there.. you forgot all the "," between the names of the fields ;)

Code:

   "dbo.BCSMF_SalesPerson.agentfirstname, " & _
    "dbo.BCSMF_SPersonxSCenter.ScenterID, " & _
    "dbo.BCSMF_SupplyingCenter.ScenterName, "

and so on...

HTH

Gonzalo

love_spy74 · October 30th, 2006, 05:59 PM

Sorry this isnt te solution....

gbianchi · October 31st, 2006, 09:24 AM

ok.. this is the solution, but not the only problem..

you have problems with spaces inside the string, for example:

Code:

"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.o rderidand dbo.BCSMF_SalesPerson.SPersonID=dbo.BCSMF_Orders.S PersonID

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