Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

help with MS Access query.

Posted on 2010-11-08
12
Medium Priority
?
407 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: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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

876 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