Too few parameters error 1

[merlynsdad]

I'm moving data from Oracle to Access via vba, and am having trouble with a WHERE stmt. The code basically says:

db.execute "INSERT INTO LOCALTBL (fields) " _
& "SELECT ORACLEFIELDDATE As [Date], " _
& "ORACLEFIELDVARIOUS, " _
& "FROM ORACLETABLE " _
& "WHERE "ORACLEFIELD.DATE > LOCALFIELD.DATE;"

Everything works fine until I get to the WHERE clause. This had a hard coded date, but we're going to use a date in a local table date instead. And that's where the error pops up. It's saying I have too few parameters in the WHERE clause, and I don't know what I'm missing.

If the square peg won't fit in the round hole, sand off the corners.

 

[Andrzejek]

[tt]db.execute "INSERT INTO LOCALTBL (fields) " [/tt]

Are you inserting just 1 field called 'fields'?
Because in your SELECT statement you have 2 fields: Date (bad, bad name for the field, reserved word) and ORACLEFIELDVARIOUS

You alse have a comma before FROM part of your Select.

Try something like:

Dim str As String

str = "INSERT INTO LOCALTBL (fields) " _
& "SELECT ORACLEFIELDDATE As [Date], " _
& "ORACLEFIELDVARIOUS " _
& "FROM ORACLETABLE " _
& "WHERE "ORACLEFIELD.DATE > LOCALFIELD.DATE;"

Debug.Print str

db.execute str

Have fun.

---- Andy

 

[merlynsdad]

Same error message using strSQL= as I got using db.execute. There's obviously more to the code than I posted, but it all has been working fine down through the FROM statement. What I'm trying to do now is instead of having a hard date in the WHERE stmt which worked, I'm trying to compare the Oracle db.table.field with a local table.field. The following code works just fine with the hard date:

& "WHERE " & txtDatabase & "_DTA_DAGENT.uday > 110722;"

When I try and compare "uday" which is an oddly formatted date with another "uday" date, it should work, as in the following:

& "WHERE " & txtDatabase & "_DTA_DAGENT.uday > store_dagent.uday_date;"

"txtDatabase" is one of a dozen identical databases representing sites that the end user selects ahead of time. "DTA_AGENT.uday" is an oddly formatted date field, but I should be able to compare it with "store_dagent.uday_date" in the local db. What I don't understand is what parameter is missing in the WHERE field.

If the square peg won't fit in the round hole, sand off the corners.

 

[Andrzejek]

Make sure both fields: "DTA_AGENT.uday" and "store_dagent.uday_date" are declared as DATE

Have fun.

---- Andy

 

[dhookom]

You can't use "LOCALFIELD.DATE" unless LOCALFIELD is JOINed in the FROM clause. If the table is joined into the FROM clause, the join must be on primary/foreign key fields.

Duane
Hook'D on Access
MS Access MVP

 

[merlynsdad]

There is a problem with data types here, in that the Oracle "uday" dates are number, and all the dates in the local db, incl "uday_date", are dates. Problem is, I can't change the data types in the tables in either db, so I think I'm stuck with trying to convert the Oracle data types on the fly. My SQL stmt actually does that in the SELECT stmt

& "SELECT DateSerial(2000+mid(" & txtDatabase & "_DTA_DAGENT.uday,2,2),0+mid(" & txtDatabase & "_DTA_DAGENT.uday,4,2),0+right(" & txtDatabase & "_DTA_DAGENT.uday,2))AS [Date], " & txtDatabase & "_DTA_DAGENT.uday AS uday_date, '" & txtDatabase & "' AS Location, " _

but I need to make the comparison in the WHERE clause of this same SQL stmt and I can't do that. I've tried breaking up the SQL stmt into two parts to convert the date in the first stmt, then using another SQL stmt to bring the rest of the expression over, then making the comparison in the WHERE clause of the second stmt, but I'm getting the same parameters error there too.



If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

Can you provide samples of how ORACLE is storing the date?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Back to the beginning:
What is LOCALFIELD ?

Why not posting the WHOLE SQL code ?

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

 

[merlynsdad]

Code is considered confidential info, so I can't post the entire SQL stmt. Here's the part I can post:

db.Execute "INSERT INTO TEMP_DAGENT ([date], uday_date, location, ext_num, signon) " _
& "SELECT DateSerial(2000+mid(" & txtDatabase & "_DTA_DAGENT.uday,2,2),0+mid(" & txtDatabase & "_DTA_DAGENT.uday,4,2),0+right(" & txtDatabase & "_DTA_DAGENT.uday,2))AS [Date], " & txtDatabase & "_DTA_DAGENT.uday AS uday_date, '" & txtDatabase & "' AS Location, " _
& " " & txtDatabase & "_DTA_DAGENT.EXT_NUM," _
& " " & txtDatabase & "_DTA_DAGENT.DUR_SIGNON," _
& "FROM " & txtDatabase & "_DTA_DAGENT " _
& "WHERE " & txtDatabase & "_DTA_DAGENT.uday > 1110512;"

"& txtDatabase &" represents any of a dozen identical databases for sites. We determine which db the user wants to access prior to this. What this code does in the SELECT clause is convert " & txtDatabase & "_DTA_DAGENT.uday, which Oracle stores as a number, to a date in Access.

In the WHERE clause, 1110512 is an oddly formatted date field (5/12/11) stored in Oracle as a number. I would like to substitute a date field in an Access table for this number, but all of the date fields in Access are stored as dates. So I have to convert the Oracle "number date" to an Access "date date" before I can compare it.

Now in theory I already did this with the code above, so I tried to break up the SQL stmt and download only the [Date}, since it's now in Access as a date, then download the remainder of the SQL stmt and do the comoparison. That gave me the same "too few parameters" error that I've been getting all along, and I don't really want to break up the SQL stmt anyway. If it's the only way to do it, and I can get it to work, I'm in, but it seems like there should be a better way to do it.

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

I would start by creating a small user-defined function to convert the Oracle value to a real date.

Public Function OracleDateToAccess(lngODate As Long) As Date
    '1110512 = May 12, 2011
    Dim intYear As Integer
    Dim intMonth As Integer
    Dim intDay As Integer
    intDay = lngODate Mod 100
    intMonth = ((lngODate - intDay) / 100) Mod 100
    intYear = (lngODate \ 10000) Mod 100 + 2000
    OracleDateToAccess = DateSerial(intYear, intMonth, intDay)
End Function

This allows you to use the function anywhere in your code or queries or forms.

It looks like your SQL has an extra comma after DUR_SIGNON.

You have stated "I would like to substitute a date field in an Access table for this number" but we don't know if there is a single record in the table or if you have a specific record in mind. You may need to use DLookup() to get the date field value from the table.

You should be able to clean up your SQL to something like:

Dim strSQL as String
strSQL = "INSERT INTO TEMP_DAGENT ([date], uday_date, " & _
"location, ext_num, signon) " _
& "SELECT OracleDateToAccess(uday), uday, '" & txtDatabase & "' , " _
& EXT_NUM, DUR_SIGNON " _
& "FROM " & txtDatabase & "_DTA_DAGENT " _
& "WHERE " & txtDatabase & "_DTA_DAGENT.uday > 1110512;"
debug.Print strSQL
db.Execute strSQL

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Duane, are you sure you can use an UDF in the SQL code of an Execute method ?

 

[dhookom]

PH,
It works for me. I create a small table with OraDate (values like suggested) and AccDate (nulls). I then ran this code:

Function TestSQLExe()
    Dim strSQL As String
    strSQL = "UPDATE tblOraAccDates SET AccDate = OracleDateToAccess(OraDate)"
    CurrentDb.Execute strSQL
End Function

Teh AccDates were updated to the appropriate dates.
[tt]
OraDate AccDate
1110512 5/12/2011
1110603 6/3/2011
1110321 3/21/2011
1110608 6/8/2011
[/tt]


Duane
Hook'D on Access
MS Access MVP

 

[PHV]

OK, sorry.
I've confused with Recordset ...

 

[merlynsdad]

This looks like it should work, though I haven't run it yet. The extra comma comes from truncating the code; there are 13 more fields that download in the actual SQL.

The Access date field that I'm comparing to is an existing maxDate in an Access table, or I have the option of taking the maxDate from a form, if that runs faster. Let me get a chance to play with it a bit and I'll let you know how it works. Thanks much!

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

If the query was a pass-through, it would not work with a user-defined function in Access.

We might need to know more about LOCALFIELD.DATE as has been asked. What is the format (Oracle or Access)?



Duane
Hook'D on Access
MS Access MVP

 

[merlynsdad]

LOCALFIELD.DATE would be a local Access table.field as date type. This table.field stores data for an historical time period, and I'm comparing the newly downloaded data with the maxDate in that date field, so I'm only downloading data newer than that maxDate.

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

So, do you have a table named "LOCALFIELD" and a field in it named "DATE"?

Did you test the code I suggested?

Duane
Hook'D on Access
MS Access MVP

 

[merlynsdad]

Yeah, the function works great. I had to look up the MOD as I haven't used that before. It's cool; thanks!

The Access table is store_dagent and the field I need to compare is named uday_date, but is a date type (as opposed to uday in Oracle which is a number type). I know it's confusing, but I didn't write it.

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

I'm not understanding why you used "LOCALFIELD.DATE" when it should have been store_dagent.uday_Date. It's a cause for confusion when you provide limited or misleading information :-(

Did you attempt to pull the date using DMax() either in the query or in the code?

Would it make sense to create another public function that converts a real date to an Oracle date?

Duane
Hook'D on Access
MS Access MVP

 

[merlynsdad]

Sorry aobut the confusion. The fields in the Access db were named the same as those in the Oracle db, but aren't necessarily the same, which creates unneeded confusion. I shortcut the names, rather than using the real ones, which didn't help either. There's also no naming convention at all.

We're working with phone data from call sites. The maxDate data is already in store_dagent.uday_date. That data gets downloaded daily outside of what I'm doing, and the historical period gets incremented by one day. Today the most recent data there is 5/12/11; tomorrow it will be 5/13/11. When my end user pulls data he needs to pull data > maxDate, or more recent than the latest data there. I'm good in the SQL stmt down to the WHERE clause; I just can't figure out the WHERE clause that gets me there.



If the square peg won't fit in the round hole, sand off the corners.