Solved

Syntax on calculated query field

Posted on 2012-03-20
11
451 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 100 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 50 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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 100 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now