NZ() does not work in MS Access

Hello,

I am trying to use the NZ() function in a query and it is not working... The field is a datetime field in my table. I am confused... I am not sue why it is still giving #Error.  

This is what I have,
PE: NZ(TimeValue([Station2]),0)
joygomezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Are then any cases where [Station2] is Null?
Because DateValue() and TimeValue themselves do not handle Null - you will get a error.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try adding a WHERE clause to filter out Null values of [Station2]
0
joygomezAuthor Commented:
Hi Joe,

Yes Station2 could have null... i need to show that record... but i do not want to show it as error...  I want it be be blank or null, so the user can see that it is blank or null.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
PE: TimeValue(NZ([Station2]),0)

This should show 12:00 if Station2 is null.  Is that what you want?

Date data types are double precision NUMBERS.  The integer part is the number of days since Dec 30, 1899 and the decimal is the fraction of a day since midnight.

So if you set Station2 to 0, the displayed value would be Dec 30, 1899 12:00 AM
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, you cannot use DateValue() or TimeValue() on Null values.

Maybe:

PE: NZ(TimeValue(Nz([Station2],0)),0)
0
joygomezAuthor Commented:
That did not work either.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Null
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
try this
PE: NZ(TimeValue(Nz([Station2],"12:00")),0)
0
joygomezAuthor Commented:
Hi Pat,

No, I just want it to show blank or empty... i am using this in a form to show to the user that there was no data for Staton 2.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
That seems to work

Nz
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Or this:

 IIF(TimeValue(Nz(Null,"12:00")),0)="12:00:00", Null, [Station2])
0
joygomezAuthor Commented:
Joe,

I am able to now get rid of the #Error, but it has it replaced by 12:00:00 PM
0
Dale FyeCommented:
Since all you are trying to do is display the time value of the field, and a blank if it is NULL, then use something like:

PE: Format([Station2], "hh:nn")

Open in new window


Keep in mind that the NZ( ) function, when used in a query, will always return a string.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joygomezAuthor Commented:
IIF(TimeValue(Nz(Null,"12:00")),0)="12:00:00", Null, [Station2])

that is giving error, enclose your text is quotes...
0
joygomezAuthor Commented:
Dale.. that worked!!!

Can you take this one step further with a related question?
0
Dale FyeCommented:
if it is related to this one, ask it here.  Otherwise, just ask a new question.
0
joygomezAuthor Commented:
Great... as you can see... i am now getting an error with i try to take the timediff... from Station1 and Station2.  

TimeInRegistration: Int(DateDiff("n",Format([Station1],"Medium Time"),Format([Station2],"Medium Time")))
0
Gustav BrockCIOCommented:
To have either the time or Null:

TimeToDisplay: IIf(IsNull([Station2]), Null, TimeValue(Nz([Station2], #00:00#)))

Open in new window


To get the difference in minutes, accepting Null as Midnight:

TimeInRegistration: DateDiff("n", Nz([Station1], #00:00#), Nz([Station2], #00:00#))

Open in new window

/gustav
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
A solution was provide be Dale and Gustav, and I assisted in pointing out the original issue.
So ... please close the question appropriately.
0
Joy GomezCommented:
hi Joe,

I am new to this... what do you mean by closing the question appropriately?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, you closed the Q with one of your answers as 'Accepted'.
But others (as noted  above) provided a solution (s).  So those s/b the Accepted and Assisted answers :-)
0
Joy GomezCommented:
ok got it... any ideas on how to reopen a question so i can correct this?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I don't think it's really closed yet?
0
Dale FyeCommented:
It doesn't appear that you have actually selected any of the responses.  There should be text in the bottom right corner of each response to the initial thread that reads something like "best answer" or "assist".  Click on those in the posts that helped you resolve your question.  Then there should be a box at the bottom of your screen which allows you to close the question and redistribute points (by default they are split evenly between the responses you have selected as "best" and "assist" ).  This section also enables you to make a comment.
0
Joy GomezCommented:
I have looked all over, and i am not seeing an option to close this...
0
Dale FyeCommented:
Joy, when you look at the responses to your question you should see text in the bottom of each response that looks similar to:select "best" and "assist" responses  If you select one of the responses as "best" you may then select as many of the other responses as you want (0 to all, although usually you don't need to select more than one or two "assists").  Once you have done that, you should see a section at the bottom of the screen that looks like:"selected solutions" and Next.  If you then click "Next", you should see a box that looks similar to:select solutions, although the box you see would normally also include an option to redistribute points (not visible in this example since I created a question and answered my own question for this demo).

Redistribute points as you think is appropriate, the default is that the "best" and all of the "assists" share points equally.

Type any comments you want to make, generally used to thank the contributors, and sometimes to explain why an "asker" accepted one response over another.

Then click the "Close Question" button
1
Joy GomezCommented:
Hi Guys,

Please look at this image... this question is definitely closed and I am not able to close this again.  So how about I create a new question and then you both reply it again and hopefully i can close it properly this time... your thoughts...
Picture2.png
0
Dale FyeCommented:
Yes, you did apparently close it.

I've submitted a request to the administrators that they reopen the question.
0
Joy GomezCommented:
thanks Dale... i will look out for it...
0
joygomezAuthor Commented:
Thanks all for your patience in getting this corrected.
0
PatHartmanCommented:
It may be closed now but you selected some strange posts as "answers".
1
Dale FyeCommented:
Pat,

I would certainly agree with that.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.