[RESOLVED] VB6, MS Access 2000 BE and parameter error
I have a program that was working at one point. I made no changes at my end. I received a new Access database to work on and now I get the error
Quote:
Run-time error '-2147217904 (80040e10)': No value given for one or more required parameters.
The code running is the first option for the USA corp and the error is on the last line see comment in code
Code:
strStart = Format(CStr(dtStartDate), "MM/dd/yyyy")
strEnd = Format(CStr(dtEndDate), "MM/dd/yyyy")
If frmMain.chkCountry.Value = vbChecked Then 'USA corp
AccCMD.CommandText = "SELECT Accounts.ID, Accounts.CompanyDBAName, " _
& "Orders.OrderID, Orders.OrderDate, Orders.Amount, " _
& "Orders.Shipping, Orders.InvoiceByCorporation, " _
& "Transactions.TransactionID, Transactions.ProductID, " _
& "Transactions.Sold, Transactions.UnitPrice, Transactions.Price, " _
& "Transactions.GST, Transactions.PST, Orders.Terms, Orders.Currency, " _
& "Orders.ShipGST, Orders.ShipPST " _
& "FROM Accounts INNER JOIN (Orders " _
& "INNER JOIN Transactions ON Orders.OrderID = Transactions.OrderID " _
& ") ON Accounts.ID = Orders.BillToID " _
& "WHERE Orders.InvoiceByCorporation = '" & strCountry & "' " _
& "AND (Orders.OrderDate >= #" & strStart & "# " _
& "AND Orders.OrderDate <= #" & strEnd & "#) " _
& "ORDER BY Orders.OrderID, Transactions.TransactionID"
Else 'Can Corp
AccCMD.CommandText = "SELECT Accounts.ID, Accounts.CompanyDBAName, " _
& "Orders.OrderID, Orders.OrderDate, Orders.Amount, " _
& "Orders.Shipping, Orders.InvoiceByCorporation, " _
& "Transactions.TransactionID, Transactions.ProductID, " _
& "Transactions.Sold, Transactions.UnitPrice, Transactions.Price, " _
& "Transactions.GST, Transactions.PST, Orders.Terms, Orders.Currency, " _
& "Orders.ShipGST, Orders.ShipPST " _
& "FROM Accounts INNER JOIN (Orders " _
& "INNER JOIN Transactions ON Orders.OrderID = Transactions.OrderID " _
& ") ON Accounts.ID = Orders.BillToID " _
& "WHERE Orders.InvoiceByCorporation = '" & strCountry & "' " _
& "AND (Orders.OrderDate >= #" & strStart & "# " _
& "AND Orders.OrderDate <= #" & strEnd & "#) " _
& "AND Orders.Currency = '" & strCountry & "' " _
& "ORDER BY Orders.OrderID, Transactions.TransactionID"
End If
Debug.Print AccCMD.CommandText
AccRS.Open AccCMD 'ERROR ON THIS LINE
I am not sure why it wouldn't be working unless there is something new in the data for some reason. The Debug.Print looks ok to me
Quote:
SELECT Accounts.ID, Accounts.CompanyDBAName, Orders.OrderID, Orders.OrderDate, Orders.Amount, Orders.Shipping, Orders.InvoiceByCorporation, Transactions.TransactionID, Transactions.ProductID, Transactions.Sold, Transactions.UnitPrice, Transactions.Price, Transactions.GST, Transactions.PST, Orders.Terms, Orders.Currency, Orders.ShipGST, Orders.ShipPST FROM Accounts INNER JOIN (Orders INNER JOIN Transactions ON Orders.OrderID = Transactions.OrderID ) ON Accounts.ID = Orders.BillToID WHERE Orders.InvoiceByCorporation = 'USA' AND (Orders.OrderDate >= #05/19/2008# AND Orders.OrderDate <= #06/02/2008#) ORDER BY Orders.OrderID, Transactions.TransactionID
so I am pretty sure all parameters are working correctly and properly filled.
Yes there are records for the date range (about 295). I do have to copy the data from one database (password protected at the database level not user-level security, to another unsecured file, but have copied and confirmed I copied all records from each of Accounts, Orders and Transactions properly.
Does anyone know of any other reasons this message may pop up? I believe the parameters are filled properly and formatted properly for Access. Any help is appreciated TIA rasinc
Re: VB6, MS Access 2000 BE and parameter error
Is the new Access database a different version?
Re: VB6, MS Access 2000 BE and parameter error
Thread moved to Database Development forum - which is where SQL questions belong
There are several causes of this error, as what is considered to be a "parameter" (in the context of the error message) is very wide-ranging.
The first thing I would check, which is basically an extension to Hack's question, is does it work if you remove the Where clause (or at least part involving dates)?
If not, the next thing to check is that all of the table and field names are correct.
Re: VB6, MS Access 2000 BE and parameter error
Try to copy the SQL (output by Debug.Print) into Access SQL textbox then switch to Query Design to see what happens. Any misspelling name can be found easier.
Re: VB6, MS Access 2000 BE and parameter error
Database is still the same version and is actually the same database. I've copied data into the same mdb file in the same tables. I've just deleted the original data that was there. This was supposed to be a one-time only conversion utility and the client is driving up the wall with delays in implementing the data changes, so I have to keep doing it again. I'll try the suggestions as soon as I can get back to the office.
Thanks
Re: VB6, MS Access 2000 BE and parameter error
Ok, you guys are very good. Turns out I don't remember what I did after I finished it. I didn't just copy the records to the new table like I did the last two times, I reimported the tables. One of the field names was renamed from ID to ID#. This caused my problems.
So a followup question if I could. In case I come across it in the future, how do you account for a # in a field name so that Jet doesn't think a date follows it? Is there some escape code I should use? I tried SELECT 'Accounts.ID#', SELECT Accounts.ID&#, etc to escape or somehow identify the field as not a date delimiter. Any ideas?
TIA
Re: VB6, MS Access 2000 BE and parameter error
The best thing to do is to change the field name, as it then solves the issue everywhere, rather than just in this one SQL statement.
If that isn't possible (perhaps a program you don't have the code to also uses that database) or practical (as you will get sent updated versions of the database regularly), what you can do is enclose the field name in square brackets, eg: Accounts.[ID#]
Re: VB6, MS Access 2000 BE and parameter error
Thanks Si. The renaming is what I did to get it to run but you are correct, I do not have access to the original programmers who are still updating the database. I don't expect this project to last longer than this week (fingers crossed) so it is not a big issue here. I was just thinking about what happens in the future if I come across it.
I tried your idea
Code:
AccCMD.CommandText = "SELECT Accounts.[ID#],...
But it didn't work. I still get the same message. I tried other variations also. I am going to mark this one resolved at this point. Thanks for the answers. I tried to rate everyone but the forum wouldn't let me.
