Syntax on calculated query field

Hi.  I created a calculated field in a query and keep getting this message when I run the query:

        "You may have entered a comma without a preceding value or identifier."

I cannot figure out where my syntax is wrong...  Here is the code.

        Days: IIf(is null([Section IV Date Completed]), DateDiff("d",[Date_Initiated],[Current Date]), DateDiff("d",[Date_Initiated],[Section IV Date Completed]))

I'm trying to run a day count based on whether the date complete field is filled or not.

thanks
LVL 7
valmaticAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
I concur that the mistake was your use of "is null" rather than "isnull".  However, a more succinct method would be:

Days: DateDiff("d",[Date_Initiated],NZ([Section IV Date Completed],[Current Date]))
0
 
mbizupConnect With a Mentor Commented:
Try this:

Days: IIf(isnull([Section IV Date Completed]), DateDiff("d",[Date_Initiated],[Current Date]), DateDiff("d",[Date_Initiated],[Section IV Date Completed]))


Using IsNull instead of Is Null.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
Try this:

Days: IIf(IsNull([Section IV Date Completed]), DateDiff("d",[Date_Initiated],[Current Date]), DateDiff("d",[Date_Initiated],[Section IV Date Completed]))

mx
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
valmaticAuthor Commented:
Thanks to both of you.  I haven't used the NZ function and not sure I understand it, at least not in this example.    Nz ( variant, [ value_if_null ] )

I'll have to pick it apart here.  I do get the same results though..
0
 
mbizupCommented:
Can you clarify this?

<<   I do get the same results though.. >>

Do you mean you get the same results (values) with the comments posted?

Or the same error message despite the suggestions?
0
 
valmaticAuthor Commented:
I'm sorry.  I get the same correct results using both your solution and Fyed's solution.  thanks for the 2nd set of eyes.
0
 
mbizupCommented:
Glad to help out.

<<  I haven't used the NZ function and not sure I understand it>>

NZ basically looks at a variable, field, expression, etc and determines whether it is null.  If it is null, NZ will substitute whatever value you specify.   Otherwise it will return the original value.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
valmatic:
I suggest that you lookup Nz() in the VBA Help file, because there is quite a bit more to it than mentioned here ... little nuances that are important.  This is a very commonly used function ... good to know all the details - *especially* when used in a query.

mx
0
 
Dale FyeCommented:
I agree with mx about the nuances.  In particular, if you use it in a query, the results will always be a string, so you will need to explicitly determine the type of the result with one of the data type conversion functions.
0
 
valmaticAuthor Commented:
Will research more.  Thanks for the extras.. :)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
" In particular, if you use it in a query, the results will always be a string,"
Worse ... an Empty String !! ... very problematic in itself !
0
All Courses

From novice to tech pro — start learning today.