Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Syntax on calculated query field

Posted on 2012-03-20
11
Medium Priority
?
514 Views
Last Modified: 2012-03-20
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
0
Comment
Question by:valmatic
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 400 total points
ID: 37743331
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 200 total points
ID: 37743337
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
 
LVL 49

Accepted Solution

by:
Dale Fye earned 400 total points
ID: 37743364
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:valmatic
ID: 37743417
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37743460
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
 
LVL 7

Author Comment

by:valmatic
ID: 37743472
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37743485
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
 
LVL 75
ID: 37743544
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37743996
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
 
LVL 7

Author Comment

by:valmatic
ID: 37744062
Will research more.  Thanks for the extras.. :)
0
 
LVL 75
ID: 37744326
" 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question