[RESOLVED] VB6, MS Access 2000 BE and parameter error

[rasinc]

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

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

    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

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

[Hack]

Is the new Access database a different version?

[si_the_geek]

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.

[anhn]

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.

[rasinc]

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

[rasinc]

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

[si_the_geek]

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#]

[rasinc]

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

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.