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.

 

[dhookom]

Dim strSQL as String
Dim datMaxDate as Date
datMaxDate = DMax("uday_date","store_dagent")
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 OracleDateToAccess(uDay) > #" & datMaxDate & "#"
debug.Print strSQL
db.Execute strSQL

Duane
Hook'D on Access
MS Access MVP

 

[merlynsdad]

I think there's a problem with the function side of the WHERE clause. I've never used MOD and don't quite understand how you're using it, but when I use the function in the left side of the WHERE clause with #" & datMaxDate & "# OR a fixed date e.g. > 1110512 on the right side, I get no data at all. I know the data is there; it's just not being pulled.

If I don't use the function and go back to the original code, it works with a fixed number on the right side of the comparison. However, if I use #" & datMaxDate & "# on the right side with the original code on the left, for some reason I'm getting an ODBC call failure. It should be calling the same Oracle table in both instances, so I need to figure out what it doesn't see when we compare it with store_dagent.

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

 

[merlynsdad]

It just worked, using the function on the left and the #" & datMaxDate & "# on the right side of the comparison. I have no idea why, but success is success!

Thanks.

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

 

[merlynsdad]

Can you please walk me through the Function OracleDateToAccess? I'm not really sure what it's doing. Thanks.

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

 

[dhookom]

Public Function OracleDateToAccess(lngODate As Long) As Date
[green]    '1110512 = May 12, 2011
    'want to find the Year Month and Day from the oracle date value lngODate[/green]
    Dim intYear As Integer    [green]'for the year[/green]
    Dim intMonth As Integer   [green]'for the month[/green]
    Dim intDay As Integer     [green]'for the day of month[/green]
[green]    'Mod returns the remainder when one integer is divided by another
    '  1110512 Mod 100 = 12  (the day)[/green]
    intDay = lngODate Mod 100
[green]    '  (1110512 - 12) = 1110500
    '   1110500 / 100 = 11105
    '  11105 mod 100 = 5  (the month)[/green]
    intMonth = ((lngODate - intDay) / 100) Mod 100
[green]    '  1110512 \ 10000 = 111 (this is integer division using \ rather than /)
    '  111 Mod 100 = 11
    '  11 + 2000 = 2011 (the Year)[/green]
    intYear = (lngODate \ 10000) Mod 100 + 2000
[green]    '  combine the year, month, and day using DateSerial()[/green]
    OracleDateToAccess = DateSerial(intYear, intMonth, intDay)
End Function

Duane
Hook'D on Access
MS Access MVP