Solved

Syntax on calculated query field

Posted on 2012-03-20
11
464 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

863 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

24 Experts available now in Live!

Get 1:1 Help Now