Results 1 to 6 of 6
  1. #1
    mercapto is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Spain
    Posts
    91

    Converting date field in numbers

    Hello

    I need to convert a date to a number that could be reconverted again in the original date, and -here's the point- I need to convert it to 0 if it's blank.



    Can anybody help me??

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Date value is saved to a date/time datatype field? Review http://support.microsoft.com/default...%2Dus%3B130514

    CDbl(Nz(fieldname,0))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mercapto is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Spain
    Posts
    91
    Quote Originally Posted by June7 View Post
    Date value is saved to a date/time datatype field? Review http://support.microsoft.com/default...%2Dus%3B130514

    CDbl(Nz(fieldname,0))
    I read the whole article but I found nothing useful for my query. :-(

    Answering to your question: Yes, it is a date only datatype field. No time in it.

    What I want to get is something like this:

    DateA*1+DateB*0 = DateA (in a new field)
    DateB*0+DateB*1=DateB (in a new field)
    0 and 1 values come from other fields.

    The problem comes when the date field is blank: it does not operate, giving a new blank field always.

    thx, June7

  4. #4
    RagJose is offline Seasoned user
    Windows XP Access 2002
    Join Date
    Apr 2012
    Location
    Brazil
    Posts
    42
    The Nz function suggested by June7 does the trick you need. The second argument is the value you want when the first argument is null. If you omit the second argument, it defaults to 0. Check http://www.fmsinc.com/free/newtips/a...ccesstip34.asp for background.

    Therefore, replace all references to dates in the applicable expressions with Nz({Date}) and you should be all set.

  5. #5
    mercapto is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Spain
    Posts
    91
    Quote Originally Posted by RagJose View Post
    The Nz function suggested by June7 does the trick you need. The second argument is the value you want when the first argument is null. If you omit the second argument, it defaults to 0. Check http://www.fmsinc.com/free/newtips/a...ccesstip34.asp for background.

    Therefore, replace all references to dates in the applicable expressions with Nz({Date}) and you should be all set.
    Yes!! it worked. It was the CDBl thing what confused me, I didn't know how to fit it in my query.

    Thanks to both.

    Solved

  6. #6
    RagJose is offline Seasoned user
    Windows XP Access 2002
    Join Date
    Apr 2012
    Location
    Brazil
    Posts
    42
    CDbl is a "convert-to-double" function that would make your code more efficient, if that were an issue. You would just write CDbl(Nz({Date})) instead of Nz({Date}). The net result would be the same. Glad we could help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-06-2011, 05:24 AM
  2. querying and converting text to numbers
    By hollyh in forum Queries
    Replies: 2
    Last Post: 09-01-2011, 12:39 PM
  3. Converting a date to length of time
    By Duncan in forum Access
    Replies: 2
    Last Post: 11-10-2010, 05:53 AM
  4. Converting dates from a field with blank records
    By NOTLguy in forum Programming
    Replies: 3
    Last Post: 10-14-2010, 06:38 AM
  5. Replies: 2
    Last Post: 07-03-2010, 08:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums