By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,347 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,347 IT Pros & Developers. It's quick & easy.

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

100+
P: 547
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
Expand|Select|Wrap|Line Numbers
  1. MonthOrder: Format([OrderDate],"mm/yyyy")
  2. MonthInvoice: Format([Invoicedate]"mm/yyyy")
  3.  
  4. Month: IIf([MonthOrder] Is Null Or [MonthOrder]="",[MonthInvoice]) Or IIf([MonthInvoice] Is Null Or [MonthInvoice]="",[MonthOrder])
  5.  
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?
Feb 3 '14 #1
Share this Question
Share on Google+
4 Replies


Seth Schrock
Expert 2.5K+
P: 2,941
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM
  2. (SELECT Format(OrderDate, "mm/yyyy") As MonthOrder
  3. , Format(InvoiceDate, "mm/yyyy") As MonthInvoice
  4. , IIF(MonthOrder Is Null, MonthInvoice, MonthOrder) As EntryMonth
  5. FROM tableName)
  6. 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.
Feb 3 '14 #2

100+
P: 547
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.
Expand|Select|Wrap|Line Numbers
  1. Months: IIf([MonthOrder] Is Null Or [MonthOrder]="",[MonthInvoice],IIf([MonthInvoice] Is Null Or [MonthInvoice]="",[MonthOrder]))
Suggestions
Feb 3 '14 #3

100+
P: 547
Thx Seth. I think i got it working by pure trial and error and accident now, with this code.
Expand|Select|Wrap|Line Numbers
  1. Months: IIf([MonthOrder] Is Null Or [MonthOrder]="",[MonthInvoice],IIf([MonthInvoice] Is Null Or [MonthInvoice]="",[MonthOrder],IIf([MonthOrder] Is Not Null,[MonthOrder])))
Feb 3 '14 #4

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Feb 3 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.