Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

help with MS Access query.

Posted on 2010-11-08
12
Medium Priority
?
405 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)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 8

Expert Comment

by:Nate Feinberg
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 44

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 7

Assisted Solution

by:rogerard
rogerard earned 400 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 1600 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 44

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 44

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 44

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 44

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

722 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