I'm using the following query to download to Excel
Error says it doesn't expect the keyword Between in the IIf statement.
Works fine without the IIf.
Any suggestions?
vQuery = "SELECT PSPSP100.PARNT, PSPSP100.CSEQN, PSPSP100.PSPCL,
PSPSP100.CHILD, PSPSP100.PSCCL, MSPMP100.PRDNO, MSPMP100.DESCP,
IIf(PSPSP100.PSPCL Between 'AA'And 'YM',MSPMP100.S2APH='C10301A',
MSPMP100.S2APH Like '*') FROM-----"


---
Message posted from http://www.ExcelForum.com/

Is yours a Jet data source (MS Access, Excel, etc)? The IIF function
is used in Jet to parse text to construct dynamic SQL. IIF uses VBA
expressions and operators.

It looks like you are trying to use IIF to perform a SQL subquery.
This approach simply can't be made to work. The SQL which appears
between the SELECT and FROM keywords should be a list of columns or
literal values. Your IIF function is trying to conditionally add
either

MSPMP100.S2APH='C10301A'

or

MSPMP100.S2APH Like '*'

to the SELECT cause. Such comparisons should appear in the WHERE
clause e.g.

SELECT <column list
FROM <Table list with joins
WHERE <comparisons

It seems you need subqueries to in your WHERE clause. You need to post
more information (DB schema, sample data and exactly what it is you
want to achieve) but, because you should be looking for a pure SQL
solution, you'll have a better chance if you post in the newsgroup
appropriate for your DBMS.

--

gkelle wrote in message ...
I'm using the following query to download to Excel
Error says it doesn't expect the keyword Between in the IIf statement.
Works fine without the IIf.
Any suggestions?
vQuery = "SELECT PSPSP100.PARNT, PSPSP100.CSEQN, PSPSP100.PSPCL,
PSPSP100.CHILD, PSPSP100.PSCCL, MSPMP100.PRDNO, MSPMP100.DESCP,
IIf(PSPSP100.PSPCL Between 'AA'And 'YM',MSPMP100.S2APH='C10301A',
MSPMP100.S2APH Like '*') FROM-----"


---
Message posted from http://www.ExcelForum.com/

Thanks, I'll move it to the where clause and take yur suggestion to post
to an SQL forum


---
Message posted from http://www.ExcelForum.com/