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
    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:
    CVDate(Fix([field]))
     
    Allen Browne, Dec 9, 2005
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    A huge thank you!! That should do it!
    --
    maryj


     
    Guest, Dec 9, 2005
    #3
    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.