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

Posted on 2011-04-20
Medium Priority
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
  • 2
LVL 40

Accepted Solution

als315 earned 2000 total points
ID: 35432786
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

Author Comment

ID: 35432861
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 49

Expert Comment

by:Dale Fye
ID: 35432941
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.

Author Comment

ID: 35432978
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.


Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

807 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