I am doing a calculted value in a report (wait time = starttime - endtime). Ocassionally, data is not entered for one or both times, yet I still need to calculate the time lapse for complete records. Currently, the calculated value is in the report. I need to determine a method for assigning a default value to the null fields and would using 0 be appropriate if it is date/time field? Any suggestions would be appreciated.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Null Value in Date/Time Field
- Thread starter geekyRN
- Start date
Try the Nz([EndTime],#1/1/1900#)
The value you enter as the second argument depends only on your business rules/logic. No one can tell you if this should be 0 or today or a future date. Keep in mind that 0 = Dec. 30, 1899.
Duane
MS Access MVP
The value you enter as the second argument depends only on your business rules/logic. No one can tell you if this should be 0 or today or a future date. Keep in mind that 0 = Dec. 30, 1899.
Duane
MS Access MVP
Screening out null values is a problems that occurs over and over in IT Here are three ways to handle it:
1) Try to stop bad input. Use an input mask and/or a pulldown for input. The argument for this is that it supposedly stops the problem before it starts. The argument against it is that users chafe at restrictions, and usually find ways to enter/import bad data.
2) Behind the scenes, run an update query periodically. The query finds bad values and replaces them with a default value. The argument against this is that you are altering data and egregiously violating lots of database design rules. It could also ERASE good data if you are not careful/lucky. The argument for it is that it often fixes the problem.
3) Use conditional logic when you extract the values for processing and output. Say something like "If you understand X, return X, otherwise return Y" (Y is a default value). You can implement this solution in an MS Access query using an IIF() function. This solution has no real downside if you write good code.
I'm sure that the people on this board could help you to implement any of these approaches. I would lean towards options 1 or 3, but I have seen people use 2 and get away with it.
As the previous poster said, we cannot choose a default value for you.
1) Try to stop bad input. Use an input mask and/or a pulldown for input. The argument for this is that it supposedly stops the problem before it starts. The argument against it is that users chafe at restrictions, and usually find ways to enter/import bad data.
2) Behind the scenes, run an update query periodically. The query finds bad values and replaces them with a default value. The argument against this is that you are altering data and egregiously violating lots of database design rules. It could also ERASE good data if you are not careful/lucky. The argument for it is that it often fixes the problem.
3) Use conditional logic when you extract the values for processing and output. Say something like "If you understand X, return X, otherwise return Y" (Y is a default value). You can implement this solution in an MS Access query using an IIF() function. This solution has no real downside if you write good code.
I'm sure that the people on this board could help you to implement any of these approaches. I would lean towards options 1 or 3, but I have seen people use 2 and get away with it.
As the previous poster said, we cannot choose a default value for you.
- Status
Similar threads
- Locked
- Question
- Locked
- Question