Why does Access ask for parameter value when I enter criteria for a calculated field?

Posted on 2011-04-20
Last Modified: 2012-08-14
Hi All,

In Access 2007, I have a column that calculates an eligibility date from a previous field and it works like a dream.  Here is the syntax for that code:

ELIG: DateAdd("m",12,[HIRE])

When I enter criteria in this column,  <[DATE]  

I get asked for a Parameter Value for the the HIRE parameter.  The Hire parameter is it's own calculation and works when I don't put in the criteria.

Here is a succession of the columns:

HIRE: CDate(IIf(Len([MBHIRE])=7,Format(Mid([MBHIRE],4,2) & "-" & Mid([MBHIRE],6,2) & "-" & Mid([MBHIRE],2,2),"mm\/dd\/yyyy"),Format(Mid([MBHIRE],3,2) & "-" & Mid([MBHIRE],5,2) & "-" & Left([MBHIRE],2),"mm\/dd\/yyyy")))   *** Represents one column

ELIG: DateAdd("m",12,[HIRE])   *** Represents the column next to it.

Any help would be greatly appreciated.

Question by:Anthony6890
    LVL 39

    Accepted Solution

    You are trying ro compare field, which is calculated in another field (and not ready yet).
    Try to change it to:
    ELIG: DateAdd("m",12,CDate(IIf(Len([MBHIRE])=7,Format(Mid([MBHIRE],4,2) & "-" & Mid([MBHIRE],6,2) & "-" & Mid([MBHIRE],2,2),"mm\/dd\/yyyy"),Format(Mid([MBHIRE],3,2) & "-" & Mid([MBHIRE],5,2) & "-" & Left([MBHIRE],2),"mm\/dd\/yyyy"))))
    And add criteria to this field
    LVL 1

    Author Comment

    That's what I was figuring, but I couldn't figure out how to logically get it correct.

    The formula works, except when I put:  <[DATE] in the criteria I get no results.  When I put in >[DATE] I get mixed results.  The parameter [DATE] I put in when I'm prompted to do so and the date I'm currently using in 04/30/2011.   I need to show everyone that has a date before 04/30/2011.

    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    I assume that [MBHire] is a string, and is not in standard date format.  Do you have valid entries in [MBHire] in your table for every record.  If not, then LEN() function will fail.  You might want to write a function and pass it [MBHire].  This way you could do some error checking and return a default value if [MBHIRE] is NULL.

    So, which field are you using the criteria < [Date] in?  Is [Date] a field (if so it shouldn't be) in your table, or did you really mean to use < Date()  

    Date is a reserved word, so you should not use it as a field name in any of your tables.

    Generally, I've found that Access will allow referencing other computed columns, as long as the definition for that column precedes the reference to it.
    LVL 1

    Author Comment

    Thanks for the response back, but I was able to figure out the issue.  I didn't set the parameter in the Parameters area of Access.  After explicitly stating it and that it was a date/time field it worked like a dream.

    To answer you question about MBHIRE, it isn't a date field field.  It's actually a numeric field that is pulled in from an AS400 so it's in format,  "cyymmdd".  I then do a conversion which lists that column as HIRE.  "HIRE" reflects the converted AS400 date into a recognized date format in Access "mm/dd/yyyy"

    And thanks for the tip on using the term DATE.  I will change it to something more specific.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    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…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    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

    13 Experts available now in Live!

    Get 1:1 Help Now