Solved

help with MS Access query.

Posted on 2010-11-08
12
398 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

0
Comment
Question by:zephyr_hex (Megan)
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 8

Expert Comment

by:InternalStatic
ID: 34086061
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

0
 
LVL 7

Expert Comment

by:rogerard
ID: 34086068
Are both date fields in the same format?
0
 
LVL 42

Author Comment

by:zephyr_hex (Megan)
ID: 34086088
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.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 7

Assisted Solution

by:rogerard
rogerard earned 100 total points
ID: 34086109
i don't see that you are.  A free tool you can use to help with that is notepad++.  It has parenthetical highlighting to make it easier to match up your parenthesis pairs.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 400 total points
ID: 34086127
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.

what about checking if both are null first, then check one is greater than the other. In case one is null, default them to a old date via the use of NZ

IIF (IsNull(LastChlDt) AND IsNull(Last_PO_Date),Now(),  
            IIF(NZ(LastChkDate,cdate("01/01/1900")) > NZ(Last_PO_Date,cdate("01/01/1900")) , LastChkDate, Last_PO_Date))

0
 
LVL 42

Author Comment

by:zephyr_hex (Megan)
ID: 34086136
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.
0
 
LVL 42

Author Comment

by:zephyr_hex (Megan)
ID: 34086237
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.
0
 
LVL 7

Expert Comment

by:rogerard
ID: 34086277
Try using the format command to show just the mm/dd/yyyy.
0
 
LVL 42

Author Comment

by:zephyr_hex (Megan)
ID: 34086297
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
0
 
LVL 42

Author Comment

by:zephyr_hex (Megan)
ID: 34086481
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]))
0
 
LVL 7

Expert Comment

by:rogerard
ID: 34086495
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")
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34086567
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())
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now