Access query: Where IIF function -

i have query

select      invoice.number,     invoice.amount,     invoice.paid,      iif (invoice.paid='y', max(payments.datepay), '') invoice_paid      payments      inner join      invoice     on (invoices.number = payments.numberdoc , payments.type = 'invoice')      payments.type= 'invoice' group     number,      amount,     paid 

runs fine.

sample results:

number   |  amount  | paid | invoice_paid  00000003 |  347,94€ |   y |    23/05/2014  00000004 |  462,65€ |   n |   00000005 |  462,65€ |   y | 13/08/2014  00000006 |  453,88€ |   y | 03/09/2014  00000007 |  155,57€ |   y | 04/09/2014  00000008 |  500,00€ |   n |  

but need filter "invoice_paid" date.

if execute this:

select      invoice.number,     invoice.amount,     invoice.paid,      iif (invoice.paid='y', max(payments.datepay), '') invoice_paid      payments      inner join      invoice     on (invoices.number = payments.numberdoc , payments.type = 'invoice')      payments.type= 'invoice' , invoice_paid=#01/01/2014# group     number,      amount,     paid 

display window: "introduce parameter invoice_paid".

how can filter results result returned iif function?

the other answers accurate, let me try clarify you, ask question...

the column computing "invoice_paid" can't used in clause it's alias result column, have use raw original date table itself, such as

where payments.type= 'invoice' , payments.datepay=#01/01/2014# 

and can't use aggregate such max( payments.datepay ) in clause either, have in having clause after grouping clause noted user idevlop.

you have filter specific date (based on = ), yet none of data shows such date. so, not not return results (or little) either or having method. if looking payments specific date, query directly , show.

now, question. want 1 date or did mean has invoice max( datepay ) greater or equal jan 1, 2014? if so, change equality test >= #01/01/2014#