joygomez
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 )
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
Try adding a WHERE clause to filter out Null values of [Station2]
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.
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
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)
Maybe:
PE: NZ(TimeValue(Nz([Station2],0)),0)
ASKER
That did not work either.
try this
PE: NZ(TimeValue(Nz([Station2] ,"12:00")) ,0)
PE: NZ(TimeValue(Nz([Station2]
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Joe,
I am able to now get rid of the #Error, but it has it replaced by 12:00:00 PM
I am able to now get rid of the #Error, but it has it replaced by 12:00:00 PM
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
IIF(TimeValue(Nz(Null,"12: 00")),0)=" 12:00:00", Null, [Station2])
that is giving error, enclose your text is quotes...
that is giving error, enclose your text is quotes...
ASKER
Dale.. that worked!!!
Can you take this one step further with a related question?
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.
ASKER
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([S tation1]," Medium Time"),Format([Station2]," Medium Time")))
TimeInRegistration: Int(DateDiff("n",Format([S
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
A solution was provide be Dale and Gustav, and I assisted in pointing out the original issue.
So ... please close the question appropriately.
So ... please close the question appropriately.
hi Joe,
I am new to this... what do you mean by closing the question appropriately?
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 :-)
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: 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:. If you then click "Next", you should see a box that looks similar to:, 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
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
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.
I've submitted a request to the administrators that they reopen the question.
thanks Dale... i will look out for it...
ASKER
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.
I would certainly agree with that.
Because DateValue() and TimeValue themselves do not handle Null - you will get a error.