Using format date with multiple date fields in Query to display in one field

[neelsfer]

I have two dates in my tblAcquire table.
It is called OrderDate and Invoicedate.

I would like to display the month from the "Orderdate" field but if that is unavailable, then use the "Invoicedate" or vice versa, to determine the month from the same record(row).
ie if MonthOrder isnull then display Monthinvoice and vice versa.
My code in the Query displays one date only currently correctly

MonthOrder: Format([OrderDate],"mm/yyyy")
MonthInvoice: Format([Invoicedate]"mm/yyyy")
 
Month: IIf([MonthOrder] Is Null Or [MonthOrder]="",[MonthInvoice]) Or IIf([MonthInvoice] Is Null Or [MonthInvoice]="",[MonthOrder])
 

It does not add the month in the new field - only add -1
I need to filter on the Month field, in the criteria

Any suggestions please?

[Seth Schrock]

You might be running into problems because Month is a reserved name. Also, from my research, you can't use the alias name in the WHERE clause directly. However, you can do it as a sub query.

SELECT * FROM
(SELECT Format(OrderDate, "mm/yyyy") As MonthOrder
, Format(InvoiceDate, "mm/yyyy") As MonthInvoice
, IIF(MonthOrder Is Null, MonthInvoice, MonthOrder) As EntryMonth
FROM tableName)
WHERE EntryMonth > #2/3/2013#

This works because the main query is receiving the information using the aliases from the subquery. This might have a performance impact however.

[neelsfer]

This code add the Month from either "MonthOrder" or "MonthInvoice" if one is blank but not when both Months are displayed. In this case i would like the "monthOrder" to show up as default.

Months: IIf([MonthOrder] Is Null Or [MonthOrder]="",[MonthInvoice],IIf([MonthInvoice] Is Null Or [MonthInvoice]="",[MonthOrder]))

Suggestions

[neelsfer]

Thx Seth. I think i got it working by pure trial and error and accident now, with this code.

Months: IIf([MonthOrder] Is Null Or [MonthOrder]="",[MonthInvoice],IIf([MonthInvoice] Is Null Or [MonthInvoice]="",[MonthOrder],IIf([MonthOrder] Is Not Null,[MonthOrder])))

[Seth Schrock]

Okay. Glad you got it working. I didn't pay much attention to your iif statement as I was focusing on using the alias in the WHERE clause.