We help IT Professionals succeed at work.

help with MS Access query.

420 Views
Last Modified: 2012-05-10
i am going blind trying to figure out where i've made a mistake in this Access 2007 expression.

there are two date fields, LastChkDt and Last_PO_Date.  i need to use whichever is greater.  if both have no date, then use today's date.
IIf(IsDate([dbo_qwcs_MPVEND]![LastChkDt])=1,(IIf(IsDate([dbo_qwcs_MPVEND]![Last_PO_Date])=1,(IIf([dbo_qwcs_MPVEND]![LastChkDt]>[dbo_qwcs_MPVEND]![Last_PO_Date],[dbo_qwcs_MPVEND]![LastChkDt],[dbo_qwcs_MPVEND]![Last_PO_Date])),[dbo_qwcs_MPVEND]![LastChkDt])),(IIf(IsDate([dbo_qwcs_MPVEND]![Last_PO_Date])=1,[dbo_qwcs_MPVEND]![Last_PO_Date],Date())))

Open in new window

Comment
Watch Question

Nate FeinbergFinancial Center Operations Manager

Commented:
There is a point at which a 'compressed' code goes from elegant to confusing, and I believe you've crossed it my friend haha. I'm all for elegance, but sometimes readability has to factor into the equation. I'm not too familiar with VBA or Access VBA, I only know a bit, so I'll just kinda psuedocode it for you.

If IsDate(LastChkDt)
    If IsDate(Last_PO_Date)
        If LastChkDt > Last_PO_Date
            'Use LastChkDt
        Else
            'Use Last_PO_Date
        End If
    Else
        'Use today's date
    End If
Else If IsDate(Last_PO_Date)
    'Use Last_PO_Date
Else
    'Use today's date
End If
' There are plenty of ways to shorten (and potentially optomize) this code, but this is the best way I can devise without knowing more about VBA, using redudant if conditions, or spending more time than I have at the moment. The code isn't pretty, so definitely take the time to beautify it if possible :)

Open in new window

Commented:
Are both date fields in the same format?
CERTIFIED EXPERT
Top Expert 2010

Author

Commented:
Access doesn't allow "else if".  the only option is: IIF(comparison, true, false)

which means i have to use nested IIF.

and i think i'm missing a parenthesis or something.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2010

Author

Commented:
yes, both date fields are the same format.

if i do:
IIf(IsDate([LastChkDt])=0,IIf(IsDate([Last_PO_Date])=0,0,1),1)

i get a 0 when both of the fields are empty.  i get a 1 when at least one of them is a date.  i've also separated the test out into two separate columns to verify isdate() is correctly detecting when each of the two fields have a date, or when they are empty.
CERTIFIED EXPERT
Top Expert 2010

Author

Commented:
rockiroads - this solution is working...
except when both fields are null, i'm getting a date/time result for today's date.

i tried changing now() to date(), but i'm still getting date/time.

Commented:
Try using the format command to show just the mm/dd/yyyy.
CERTIFIED EXPERT
Top Expert 2010

Author

Commented:
how?

i tried:
format(IIf(IsNull([dbo_qwcs_MPVEND]![LastChkDt]) And IsNull([dbo_qwcs_MPVEND]![Last_PO_Date]),date(),IIf(NZ([dbo_qwcs_MPVEND]![LastChkDt],CDate("01/01/1900"))>NZ([dbo_qwcs_MPVEND]![Last_PO_Date],CDate("01/01/1900")),[dbo_qwcs_MPVEND]![LastChkDt],[dbo_qwcs_MPVEND]![Last_PO_Date]),"mm/dd/yyyy")

and i'm still getting date/time
CERTIFIED EXPERT
Top Expert 2010

Author

Commented:
WTF?
i didn't change anything, and now it's not showing the time.  just the date.

IIf(IsNull([dbo_qwcs_MPVEND]![LastChkDt]) And IsNull([dbo_qwcs_MPVEND]![Last_PO_Date]),Date(),IIf(NZ([dbo_qwcs_MPVEND]![LastChkDt],CDate("01/01/1900"))>NZ([dbo_qwcs_MPVEND]![Last_PO_Date],CDate("01/01/1900")),[dbo_qwcs_MPVEND]![LastChkDt],[dbo_qwcs_MPVEND]![Last_PO_Date]))

Commented:
That works for me in access 2010, once I added the missing parenthesis before the last comma.

format(IIf(IsNull([dbo_qwcs_MPVEND]![LastChkDt]) And IsNull([dbo_qwcs_MPVEND]![Last_PO_Date]),date(),IIf(NZ([dbo_qwcs_MPVEND]![LastChkDt],CDate("01/01/1900"))>NZ([dbo_qwcs_MPVEND]![Last_PO_Date],CDate("01/01/1900")),[dbo_qwcs_MPVEND]![LastChkDt],[dbo_qwcs_MPVEND]![Last_PO_Date])),"mm/dd/yyyy")
CERTIFIED EXPERT
Top Expert 2006

Commented:
Sorry, I had to pop out so couldn't answer your query. I always tend to use Now() as Date() sometimes gives reference issues. Now() does give the time as you have noticed.

You have it working now anyways but an alternative to date() is DateValue(Now())
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.