sql error 2

[Shaves]

I created a query in Access and it runs fine. I copied the sql because I want to run it from Excel. However, I can't get it to run. Below is the sql code I'm using. When I kick it off, I get a "No Value provided for one or more required parameters". The connectionstring and data source have been defined and are correct.

Any suggestions on what is causing the error? Thanks in advance.


vsql = "SELECT HeaderFile.FileDate, Mid(HeaderFile![12DIGITBILLING#],1,2), Mid(HeaderFile![12DIGITBILLING#],3,1), " & _

"HeaderFile!BFSUnit, HeaderFile.BTName, HeaderFile.Address, HeaderFile.CityStateZip,HeaderFile.Loc, HeaderFile.AcctNo, " & _

"HeaderFile.Invoice, HeaderFile.WorkDate, HeaderFile.STName, HeaderFile.STAddress, HeaderFile.STCityStateZip, HeaderFile.StoreNo, HeaderFile.StoreType, " & _

"HeaderFile.StoreCity , HeaderFile.StoreState, HeaderFile.StoreWorkDate, HeaderFile.BilledDate, HeaderFile.COMMENTS, HeaderFile.PO, " & _

"HeaderFile.VEHICLENUM, HeaderFile.GROUPNUM,
HeaderFile.[12DIGITBILLING#], HeaderFile.[FSNE-ID], HeaderFile.STORENAME, " & _

"HeaderFile.ADDRESS1, HeaderFile.ADDRESS2, HeaderFile.PHONENUMBER, HeaderFile.DRIVER, HeaderFile.MILEAGE, HeaderFile.VIN, " & _

"HeaderFile.MAKE , HeaderFile.MODEL, HeaderFile.YEAR, HeaderFile.TRANS, HeaderFile.LICENSE, HeaderFile.SALE, " & _

"HeaderFile.TIRE, HeaderFile.[Tax Amt], HeaderFile.InvExcise, HeaderFile.InvAmt, HeaderFile.DiscAmt, HeaderFile.TruckTire, " & _

"HeaderFile.DueDate , HeaderFile.DAmt, HeaderFile.Bonus, DetailFile.LineNo, DetailFile.Units, " & _

"DetailFile.Article, DetailFile.Descr, DetailFile.Price, DetailFile.PerUnit, DetailFile.Class, DetailFile.TaxCode, DetailFile.ExAmt, DetailFile.RUnits, DetailFile.Factor, " & _

"DetailFile.RPrice , DetailFile.DEAmt, DetailFile.TDescr, DetailFile.TAmt, DetailFile.TEAmt " & _

" FROM HeaderFile INNER JOIN DetailFile ON (HeaderFile.InvoiceNo = DetailFile.InvoiceNo) AND (HeaderFile.AcctNo = DetailFile.AcctNo) AND (HeaderFile.Loc = DetailFile.Loc)" & _

" WHERE HeaderFile.AcctNo = ""00020017"" "

Call Recordset.Open(vsql, ConnectionString, adOpenForwardOnly, adLockReadOnly, CommandTypeEnum.adCmdText)

 

[PHV]

What is this ?
CommandTypeEnum.adCmdText

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

"No Value provided for one or more required parameters"

One or more of the fields (as typed) in your sql is NOT in either of the tables.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[Shaves]

I have checked and all of the field names are spelled correctly? Any other suggestions?

Can you look at the "Mid" expresssions and tell me if something isgoing on there? Thanks

 

[SkipVought]

vsql = "SELECT "
vsql = vsql & "  HeaderFile.FileDate"[b]
vsql = vsql & ", Mid(HeaderFile.[12DIGITBILLING#],1,2)"
vsql = vsql & ", Mid(HeaderFile.[12DIGITBILLING#],3,1)"[/b]
vsql = vsql & ", HeaderFile!BFSUnit"
vsql = vsql & ", HeaderFile.BTName"
vsql = vsql & ", HeaderFile.Address"
vsql = vsql & ", HeaderFile.CityStateZip"
vsql = vsql & ", HeaderFile.Loc"
vsql = vsql & ", HeaderFile.AcctNo"
vsql = vsql & ", HeaderFile.Invoice"
vsql = vsql & ", HeaderFile.WorkDate"
vsql = vsql & ", HeaderFile.STName"
vsql = vsql & ", HeaderFile.STAddress"
vsql = vsql & ", HeaderFile.STCityStateZip"
vsql = vsql & ", HeaderFile.StoreNo"
vsql = vsql & ", HeaderFile.StoreType"
vsql = vsql & ", HeaderFile.StoreCity "
vsql = vsql & ", HeaderFile.StoreState"
vsql = vsql & ", HeaderFile.StoreWorkDate"
vsql = vsql & ", HeaderFile.BilledDate"
vsql = vsql & ", HeaderFile.COMMENTS"
vsql = vsql & ", HeaderFile.PO"
vsql = vsql & ", HeaderFile.VEHICLENUM"
vsql = vsql & ", HeaderFile.GROUPNUM"
vsql = vsql & ", HeaderFile.[12DIGITBILLING#] "
vsql = vsql & ", HeaderFile.[FSNE-ID]"
vsql = vsql & ", HeaderFile.STORENAME"
vsql = vsql & ", HeaderFile.ADDRESS1"
vsql = vsql & ", HeaderFile.ADDRESS2"
vsql = vsql & ", HeaderFile.PHONENUMBER"
vsql = vsql & ", HeaderFile.DRIVER"
vsql = vsql & ", HeaderFile.MILEAGE"
vsql = vsql & ", HeaderFile.VIN"
vsql = vsql & ", HeaderFile.MAKE "
vsql = vsql & ", HeaderFile.MODEL"
vsql = vsql & ", HeaderFile.YEAR"
vsql = vsql & ", HeaderFile.TRANS"
vsql = vsql & ", HeaderFile.LICENSE"
vsql = vsql & ", HeaderFile.SALE"
vsql = vsql & ", HeaderFile.TIRE"
vsql = vsql & ", HeaderFile.[Tax Amt]"
vsql = vsql & ", HeaderFile.InvExcise"
vsql = vsql & ", HeaderFile.InvAmt"
vsql = vsql & ", HeaderFile.DiscAmt"
vsql = vsql & ", HeaderFile.TruckTire"
vsql = vsql & ", DetailFile.Article"
vsql = vsql & ", DetailFile.Descr"
vsql = vsql & ", DetailFile.Price"
vsql = vsql & ", DetailFile.PerUnit"
vsql = vsql & ", DetailFile.Class"
vsql = vsql & ", DetailFile.TaxCode"
vsql = vsql & ", DetailFile.ExAmt"
vsql = vsql & ", DetailFile.RUnits"
vsql = vsql & ", DetailFile.Factor"
vsql = vsql & ", DetailFile.RPrice "
vsql = vsql & ", DetailFile.DEAmt"
vsql = vsql & ", DetailFile.TDescr"
vsql = vsql & ", DetailFile.TAmt"
vsql = vsql & ", DetailFile.TEAmt "
vsql = vsql & vbCrLf
vsql = vsql & "FROM       HeaderFile "
vsql = vsql & "INNER JOIN DetailFile"
vsql = vsql & "   ON (HeaderFile.InvoiceNo = DetailFile.InvoiceNo)"
vsql = vsql & "  AND (HeaderFile.AcctNo    = DetailFile.AcctNo)"
vsql = vsql & "  AND (HeaderFile.Loc       = DetailFile.Loc)"
vsql = vsql & vbCrLf
vsql = vsql & "WHERE HeaderFile.AcctNo = [b]'00020017'[/b]"

NO BANG.between table and field
SINGLE QUOTE delimiting string literals.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[Shaves]

Skip........what does "NO BANG.between table and field" mean? Thanks for all of the help!

 

[Bong]

bang"="!" (exclamation point)

_________________
Bob Rashkin