EXCEL Error# 3464 Data Type Mismatch in Criteria Expression

[Mike Knight]

I have a problem with a VBA query that uses files on an AS400 machine.
The Excel file containing the code is saved on a server.

The query works on a PC using Windows 98 and Excel 97. Microsoft DAO
3.51 Object Library is referenced.

This code executes in less than one minute (Database name and File
name changed for security).

Option Explicit

Dim DB1 As Database
Dim RS1 As Recordset
Dim QryStr As String
Dim x As Integer

Const DatabaseName As String = "AS400 XXXXXX"
Const ConnectionStr As String = "ODBC; DSN=DATABASENAME;"

Sub Run_Query()
QryStr = "SELECT PRAN8, PRDCTO FROM FXXXX WHERE PRMATC='1' AND " _
& "PRDCTO NOT IN ('OT','OJ','OU') AND PRUOPN <> 0 AND PRDGL <=
104031"

Set DB1 = OpenDatabase(DatabaseName, dbDriverCompleteRequired,
True, ConnectionStr)
DB1.QueryTimeout = 0
Set RS1 = DB1.OpenRecordset(QryStr)

'Read in the field names
With ActiveSheet
For x = 0 To RS1.Fields.Count - 1
.Cells(ActiveCell.Row, x + 1).Value = RS1.Fields(x).Name
Next
End With
'Copy the data
ActiveCell.Offset(1, 0).Select
ActiveCell.CopyFromRecordset RS1
DB1.Close
End Sub


If I open this file on a PC with Windows 2000 and Excel 2000, and I
change the reference from DAO 3.51 to DAO 3.6, the query gives me
Error# 3464.

Using trial and error methods, I determined that the problem was in
the query with the "Where" statement that reads PRUOPN <> 0 . PRUOPN
is a quantity field on the AS400, and does not contain any NULL
values. If I remove this item, the query executes correctly.

I can also change the statement to read (PRUOPN * 1) <> 0 and the
query will execute, but takes a very long time.

I am asking for help resolving this issue.