Solved

Syntax on calculated query field

Posted on 2012-03-20
11
472 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
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…

786 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