datevalue returning #Error

Discussion in 'Microsoft Access Queries' started by Guest, Dec 9, 2005.

  1. Guest

    Guest Guest

    I have a date/time field. I need to extract just the date in order to do
    further date calculations. I created a calculated field using DateValue - but
    if the field does not have a date, it returns #Error. So, I changed it to the
    following: IIF([field] is null,"",DateValue([field])) - this leaves out the
    error but now changes all the dates to text. A workaround is to have it enter
    a 0 rather than leaving it blank, but then that enters the value of 12:00:00
    am. Any other suggestions of how not to have the error display but prevent
    the dates from changing to text?
    Guest, Dec 9, 2005
    1. Advertisements

  2. Guest

    Allen Browne Guest

    The zero-length string is the problem: that forces Access to treat the value
    as text.

    Instead, use Null. And wrap the expression in CVDate() to ensure it is
    understood as a date:
    CVDate(IIF([field] is Null, Null, DateValue([field])))

    You could also use Fix() to lose the time component:
    Allen Browne, Dec 9, 2005
    1. Advertisements

  3. Guest

    Guest Guest

    A huge thank you!! That should do it!

    Guest, Dec 9, 2005
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.