Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Location
    LA, USA
    Posts
    3

    Question Unanswered: What is the value of #Error on a report?

    We are running an ancient program based on DBase that management wants some useful, readable reports from. The date and time fields in the database were created as text fields. I need to be able to determine the amount of time that passed between the ([start_date] and [start_time]) and ([end_date] and [end_time]), and an average for all records. I'm using DateValue() and TimeValue() to get the strings converted, but sometimes there is a data error in the table (like a time of "/9:38" for some reason) and the function returns #Error. If I try to sum the fields and encounter an #Error the value for the sum is always #Error.

    Is there any way I can trap for this on the report or in the query? I've tried iif(DateValue([start_date]),DateValue([start_date]),NULL) but that ends up as #Error, too. Same result for iif(DateValue([start_date])>0,DateValue([start_date]),NULL). Obviously #Error won't fail an iif() and is greater than 0, but I have no idea what value to look for.

    Any help would be appreciated.

    Thanks,
    Bob

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    #Error has no value and cannot be detected. You need to start with the most important thing in any database, i.e. making sure that the data in the table is accurate. There should never be any excuse for a table ending up with data that cannot be processed. Before running your report, parse the data to get rid of any errors. You may need to write some code to do this, but it will be invaluable.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    #Error has no value and cannot be detected. You need to start with the most important thing in any database, i.e. making sure that the data in the table is accurate. There should never be any excuse for a table ending up with data that cannot be processed correctly in a query or report. Before running your report, parse the data to get rid of any errors. You may need to write some code to do this, but it will be invaluable.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  4. #4
    Join Date
    Apr 2004
    Location
    LA, USA
    Posts
    3
    Well, it's not exactly what I was hoping for, but at least I have an answer now. Thanks.

    The excuse for bad data in the table is a 17-year old program that doesn't check for valid data before it posts it, it just posts it. That and hiring their first full-time IT person only 5 months ago (me). I've been looking for something to back up my assertion that they need to dedicate some hours to fixing broken records, and this may be what I need to kick-start that.

  5. #5
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Yes, they do need to dedicate those hours, and this is a prime example of what can happen when there are broken records. Data integrity is God.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by rhaje
    Well, it's not exactly what I was hoping for, but at least I have an answer now. Thanks.

    The excuse for bad data in the table is a 17-year old program that doesn't check for valid data before it posts it, it just posts it. That and hiring their first full-time IT person only 5 months ago (me). I've been looking for something to back up my assertion that they need to dedicate some hours to fixing broken records, and this may be what I need to kick-start that.
    One thing you can do tho is to test for a valid date/time by using the IsDate function in your queries ... That will help filter out the crud ...

  7. #7
    Join Date
    Apr 2004
    Location
    LA, USA
    Posts
    3
    Ah, I knew I had to be overlooking something simple. Thank you for the advice. That will help considerably.

Posting Permissions

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