Link to home
Start Free TrialLog in
Avatar of joygomez
joygomezFlag for United States of America

asked on

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)
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Are then any cases where [Station2] is Null?
Because DateValue() and TimeValue themselves do not handle Null - you will get a error.
Try adding a WHERE clause to filter out Null values of [Station2]
Avatar of joygomez

ASKER

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.
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
Well, you cannot use DateValue() or TimeValue() on Null values.

Maybe:

PE: NZ(TimeValue(Nz([Station2],0)),0)
That did not work either.
try this
PE: NZ(TimeValue(Nz([Station2],"12:00")),0)
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.
SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Joe,

I am able to now get rid of the #Error, but it has it replaced by 12:00:00 PM
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
IIF(TimeValue(Nz(Null,"12:00")),0)="12:00:00", Null, [Station2])

that is giving error, enclose your text is quotes...
Dale.. that worked!!!

Can you take this one step further with a related question?
if it is related to this one, ask it here.  Otherwise, just ask a new question.
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")))
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
A solution was provide be Dale and Gustav, and I assisted in pointing out the original issue.
So ... please close the question appropriately.
Avatar of Joy Gomez
Joy Gomez

hi Joe,

I am new to this... what do you mean by closing the question appropriately?
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 :-)
ok got it... any ideas on how to reopen a question so i can correct this?
I don't think it's really closed yet?
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.
I have looked all over, and i am not seeing an option to close this...
Joy, when you look at the responses to your question you should see text in the bottom of each response that looks similar to:User generated image  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:User generated image.  If you then click "Next", you should see a box that looks similar to:User generated image, 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
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
Yes, you did apparently close it.

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

I would certainly agree with that.