Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2003
    Location
    Seattle, Washington, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    When Is A Date Not A Date? (2002 SP3)

    I'm trying to do some conditional printing on a report and having some problems. The report is a timesheet and in certain situations I want to print the word "Holiday" rather than the person's duty hours in the detail of the report. Here's the Control Source for txtDutyHours in the report's detail section:

    =IIf((Not (IsNull(DLookUp("[HolidayDate]","[Holidays]","[HolidayDate] =" & [Reports]![TimesheetRpt]![OTHrlyDate]))) And Not (IsNull([CU]))) Or Not (IsNull([H])) Or Not (IsNull([HCA])),"Holiday",IIf([Total of Hours]=0,"Off",[TimeOn] & " - " & [TimeOff]))

    I've tested the whole expression and have narrowed my problem down to the reference to [OTHrlyDate] in conditional portion of the DLookUp function. What this is supposed to be doing is checking if the date of the record being printed is a holiday . If it is then the result of the DLookUp is not null, if not then the DLookUp returns a null.

    When I noticed that I wasn't getting the results I wanted in the cases where the DLookUp was a factor I pulled the code into the Detail_Format event to check the value of [OTHrlyDate] reference using a program stop. When I run the report and processing stops the value of [OTHrlyDate] looks fine, hovering the cursor over the reference returns "[Reports]![TimesheetRpt]![OTHrlyDate] = 11/11/2004". And yet, if I set anything equal to the reference the result is null. Thanks in advance for any insights!

    John

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: When Is A Date Not A Date? (2002 SP3)

    Try thisL

    =IIf((Not (IsNull(DLookUp("[HolidayDate]","[Holidays]","[HolidayDate] =" & <font color=blue>CDate([Reports]![TimesheetRpt]![OTHrlyDate])</font color=blue>))) And Not (IsNull([CU]))) Or Not (IsNull([H])) Or Not (IsNull([HCA])),"Holiday",IIf([Total of Hours]=0,"Off",[TimeOn] & " - " & [TimeOff]))
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: When Is A Date Not A Date? (2002 SP3)

    When you are just friends?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    May 2003
    Location
    Seattle, Washington, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When Is A Date Not A Date? (2002 SP3)

    Thanks for the suggestion Charlotte. Unfortunately, I've tried CDate, FormatDateTime, Format$, all without success. What's really perplexing is that while testing in the Detail_Format event I ran an IsDate on [OTHrlyDate]. When processing stopped the result of the IsDate was False even though the value of [OTHrlyDate] is shown as 11/11/2004.

    I'm bumping up against a deadline and have decided to move on to Plan B which is to simply insert a Y/N Holiday flag into a convenient spot in the query stream feeding this report. Plan A (above) was what I thought would be a way to get the job done without altering the queries but it has definitely passed it's expediency tipping point.

    Any additional insights, for purely academic purposes, are greatly appreciated.

    Thanks Again,
    John

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: When Is A Date Not A Date? (2002 SP3)

    All I can suggest is that you may have some other character in that field that is causing it to fail as a date. Any punctuation characters, a non-printing character like a tab, etc., will cause the IsDate to fail.
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: When Is A Date Not A Date? (2002 SP3)

    <img src=/S/razz.gif border=0 alt=razz width=25 height=17> <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Charlotte

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: When Is A Date Not A Date? (2002 SP3)

    Do you want to send a compacted/zipped database so we can have a go at this as well.

  8. #8
    New Lounger
    Join Date
    May 2003
    Location
    Seattle, Washington, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When Is A Date Not A Date? (2002 SP3)

    Thanks for the offer Pat. Plan B described above worked and I'm ready to chalk this one up to a quirk of Access that I hadn't encountered before now. It'd take too much time to strip all of the sensitive data out of the db to make this academic pursuit worth the effort.

    Thanks Again,
    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •