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??
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??
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.
I read the whole article but I found nothing useful for my query. :-(Date value is saved to a date/time datatype field? Review http://support.microsoft.com/default...%2Dus%3B130514
CDbl(Nz(fieldname,0))
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
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.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.
Thanks to both.
Solved
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!